Re: How to handle deadlocks ?

In article <4piahv$stp_at_soft.softouch.bc.ca> joachim_at_softouch.bc.ca (Joachim Achtzehnter) writes:

    Date: Mon, 10 Jun 96 23:21:33 GMT
    From: joachim_at_softouch.bc.ca (Joachim Achtzehnter)

    If you follow a fixed locking order you can avoid most deadlock situations.     Whether locks are taken implicitly or explicitly doesn't make any difference,     as long as the database designer *knows* how the RDMBS locks resources.

OK, suppose a reader RDR reads a table TBL using index IND and is going to read row R. The nature of the transaction forces a lock on IND before getting to read R and locking it, right? Now, a writer WRT updates R before RDR gets to it, and that update affects IND. Does Sybase actually try to get all the appropriate index locks before getting an exclusive lock on R, or is R locked, then a lock on IND is attempted, thus leading to deadlock? (In the former case, obviously WRT must get a shared lock on R before going to find the affected index pages.) In the latter case, it is impossible for the SQL user to impose a consistent fixed locking order (unless he is willing to essentially eliminate concurrency).

This particular question arises since a trace log we were analyzing showed a reader and a writer deadlocking on a single table, and from our knowledge of the SQL involved, we're concluding that an index was involved as described above. Basically, the deadlock occured in the writer in a trigger that attempted to update an additional column in the rows affected by the original update statement (in effect, a timestamp column). No additional data page locks should have been needed than the writer must have already had. Though we don't know for certain, we suspect that the reader was selecting by using the index on our "timestamp" column.

• Pat pao_at_ascent.com