Re: How to handle deadlocks ?

From: Joachim Achtzehnter <joachim_at_softouch.bc.ca>
Date: 1996/06/12
Message-ID: <4pncin$8lo_at_soft.softouch.bc.ca>#1/1


>>> Thomas J. Kyte <tkyte_at_us.oracle.com> wrote in response to
 

>>>>> <joachim_at_softouch.bc.ca> Joachim Achtzehnter's ramblings:

Joachim> If you follow a fixed locking order you can avoid most deadlock Joachim> situations.

Thomas> Not really, and only if you employ full table level locks on data.

Note, that I said *most* deadlocks can be avoided. Following a locking order is a standard design approach that can reduce the number of deadlocks considerably. Are you suggesting one should ignore this? If so, then I disagree with you on this point. Deadlocks are undesirable even in applications that are written to properly deal with deadlocks, when deadlocks occur you are wasting time and resources!

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

It makes it somewhat worse, I agree. But it has also advantages over row locking, its just a different tradeoff. For example, with page locking, or more precisely with "access path locking" one can prevent the problem of ghost rows. With row locks you can't.

Joachim> benchmarks may suggest Oracle provides better concurrency.  

Thomas> As does real world experience.

Well, people with an email address of <tkyte_at_us.oracle.com> must say this, don't they? ;-)

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

Thomas> Neither do the others. Only if you turn on full repeatable reads Thomas> in the others do you get consistent multi-query transactions.

Ok, I should not have generalized too much, I haven't used every RDBMS in the market. Let's say I have worked with some of Oracle's competitors in the past, and these products (Ingres in particular) provided full repeatable reads by *default*. The tradeoff is between correctness and performance. Oracle decided in favour of performance (benchmarks!), Ingres in favour of correctness (academic origins?). And in both cases I am talking about the default behaviour you get without manual intervention or changes to the configuration. You can change the behaviour in both Oracle and Ingres when needed.

Thomas> In fact, the default mode in the other dbms's dont even gaurantee Thomas> SINGLE query consistency.

Interesting. Which other dbms's do you mean? I would like to know because I would certainly want to stay away from those! But be careful with general statements like this, not all your competitors behave as you described.

Joachim



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

Original text of this message