Re: How to handle deadlocks ?

From: Joachim Achtzehnter <joachim_at_softouch.bc.ca>
Date: 1996/06/10
Message-ID: <4piahv$stp_at_soft.softouch.bc.ca>#1/1


bgw_at_world.std.com (Bruce G Wilson) wrote:

> there were occasions where they had problems with deadlock: two
> processes trying to allocate two resources in opposite order.
 

> It struck me that part of the reason why this is such a problem
> is that locks are taken (and escalated) implicitly in Sybase, so
> that it's difficult from a superficial read of the code to tell
> when a lock is being taken and where the conflict might be occurring.
> I guess I'm kind of surprised to find that locks can't be taken
> explicitly; with this, it would be made clear in the code where the
> locks are being taken and might make it easier to determine where the
> problem is.

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.

Making it "easier to tell when a lock is being taken" is not enough of a reason to require explicit locking! Consider the disadvantages:

  1. The data structures must be accessed twice: first for the lock, then to access the data.
  2. One might forget to lock a resource leading to hard-to-find inconsistencies in the database.
  3. Code becomes more complicated, making it even more difficult to understand.

> I'm wondering, what kinds of techniques are used in "standard"
> practice with Sybase to detect/prevent deadlocks ? Do IS
> organizations establish rules for accessing the database, like,
> "always do an update to table A before doing an update to table B" ?

Yes, common database practice, not only with Sybase, is to establish a global locking order for all tables. If this is strictly followed deadlocks should not occur. Sometimes it is difficult, or inefficient, to follow the locking order in all cases. It is ok to violate the order in some cases as long as deadlocks don't occur too often. In this case, of course, the applications must be written to handle deadlock errors sensibly.

> I can't imagine that other databases (Oracle, Informix) don't also
> have this kind of problem. How are deadlocks handled in these ?

The principle is the same in all these RDBMS systems (also Ingres, DB/2, and others). The actual types of locks, and the exact circumstances under which locks are taken implicitly can differ from one vendor to vendor.

One interesting case is Oracle which has approached the locking issue more from a marketing angle (in my opinion). They lock much less than all the other heavy-duty RDBMSs and, therefore, benchmarks may suggest Oracle provides better concurrency. But the downside is that the default Oracle locking does not guarantee consistent multi-query transactions. With real-world applications a lot of manual attention must be directed at explicit locking (in addition to Oracle's implicit locks) to enforce consistency. Note, that even in this case these "explicit" locks don't help with your problem because the actual locks are only taken when the data is accessed, locking is explicit in the sense that you specify how locks should be taken (exclusive vs. shared, row vs. table, etc.), not when they are taken.

Joachim



joachim_at_softouch.bc.ca (work)
joachim_at_wimsey.ca (home) Received on Mon Jun 10 1996 - 00:00:00 CEST

Original text of this message