Re: How to handle deadlocks ?
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