Re: How to handle deadlocks ?

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

>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
[snip]

>
>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.
>

[snip]
Not really, and only if you employ full table level locks on data. If you are locking at the row or page level, you need to specify locking orders at the row/page level to avoid deadlocks. Problem is, you can't specify locking at the row or page level in any system.

[snip]
>
>> 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.
>

But this only works if you lock at the table level and only if you lock during insert/update/delete (but not selects where you have no control over how multiple tables are accessed during the query).

On a page level locking system, the problem is even worse, deadlocks are unavoidable in a highly active environment. Since locking a row locks some completely unrelated data, you have no control over what you are locking. Escalation simply makes the problem even worse.

>> 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.

As does real world experience.

>But the downside is that the default Oracle locking does
>not guarantee consistent multi-query transactions.

Neither do the others. Only if you turn on full repeatable reads in the others do you get consistent multi-query transactions. Every row read by one transaction will not be updateable by any other transaction during this period (talk about deadlocks).

In fact, the default mode in the other dbms's dont even gaurantee SINGLE query consistency. That is, running a simple select may very well return an answer that never existed in the database at any point in time. The only way to get multi-query consistency or even single query consistency in these dbms's is to lock all data in a shared mode (others read, no-one writes) as you read it. Now my users running simple selects can and will deadlock with users running updates. Readers block writers, writers block readers.

>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.

In a read only transaction, to get multi-statement consistency, one just needs to issue "set transaction read only". All statements are now not only 'singlely' consistent but each statement is consistent with each other.

In a read/write transaction, you can also use SERIALIZABLE=TRUE in 7.2 and earlier or use 'alter session set transaction=serializable' to achieve a read/write consistent view of the database (without employing shared read locks, readers do not block writers and writers do not block readers).

>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)

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Jun 12 1996 - 00:00:00 CEST