Hello,

I have a multi-threaded application that has two threads for database
operations. One thread adds records to the tables of the database.
Another thread is responsible for deleting old records from the
database. There appear to be some issues that I need to understand to
make this work better.

I have sets of tables that have the following relationships:

1) Main table ->

EventTable
ID - Identity

2) Support tables ->

DataTable ... ImageTable
ID - Identity ... ID - Identity

3) Relationship from EventTable to support tables ->

EventDataTable ... EventImageTable
EventID EventID
DataID ImageID


The add operation order is add EventTable row, add support table(s)
row (i.e., DataTable), and then add relationship table row (i.e.,
EventDataTable). The delete operation is to allow the cascading
delete via delete on the support table (i.e., DataTable) row and then
delete the EventTable row.

Some issues that I've encountered:

1) The delete on the support tables always raise the lock on the Table
to exclusive. Does a delete operation always raise the lock level to
a table lock or can I change something to only have a record or page
lock on the delete operation.

2) The delete on one particular support table is much long then the
other support tables. What should I look for as to the reason for
this slow delete behavior.

I appreciate any help you can offer to get me going understanding the
issues here.

Gordon.

"Gordon" <gmnewma (AT) sandia (DOT) gov> wrote

Physical locking is, well, physical and dbms implementation-dependent. The
logical representation of your data might have no direct bearing at all on
the locks set. The dbms, on the other hand, does.

Which dbms are you using? What physical structures are you using?

In an attempt to throw the authorities off his trail, gmnewma (AT) sandia (DOT) gov (Gordon) transmitted:
No, it does not. That presumably is a problem specific to whatever
database system you are using. Other database systems may have
substantially different approaches to locking.

The only system I am aware of that _used_ to use policies like that
was Sybase SQL Server.

Absent of actual information, it is difficult to diagnose this sort of
problem.
--
"cbbrowne","@","cbbrowne.com"
http://www.ntlug.org/~cbbrowne/advocacy.html
"While preceding your entrance with a grenade is a good tactic in
Quake, it can lead to problems if attempted at work." -- C Hacking
-- http://home.xnet.com/~raven/Sysadmin/ASR.Quotes.html