Re: Row Locking

pberger_at_nic.wat.hookup.net (Paul Berger) wrote:
>I have a question regarding deadlock detection within Oracle7,
>version 7.1.4, (in a Netware 3.x environment).
>
>We have an experienced application developer who is concerned
>about allowing his application to accommodate "update"
>deadlock conditions. As the resident DBA I am trying to help.
>
>We have done some testing within SQL*Plus to force a lock
>state on a single row within a table accessed by two concurrent
>users. The blocked user becomes "locked" indefinitely until
>the "blocker" either commits or roles back their transaction.
>Oracle on behalf of the blocked user does nothing. Is there
>some way, at the session or system level, that we can bias
>Oracle's behaviour in this regard? Can we establish a time-out
>so that the blocked user can proceed in an informed fashion,
>rather than waiting on the "blocker" to do something?
>
>Oracle doesn't seem to do any kind of deadlock detection in
>this regard, other than wait for the "blocker". On hot
>spots within a busy table this can conceivably become
>problamatic.
>

I don't think your test simulate a deadlock situation. Instead, it is better known as "Live Locking", or blockers -- (you got that right!). The difference between Dead & Live locking is that NO transaction can proceed in the former situation; whereas in the live lock scenerio, a single thread always continue in its own way, and therefore blocking out others indefinitely.

Oracle can detect deadlock situation, and roll back those transactions in a matter of seconds. Blockers are different stories. If you think carefully, they're not necessarily "problems" or may as well a desired behaviour. If it is not a designed behaviour, blockers should be transient and exist not more than a few seconds.

Having said that, it is intutative to say "IT IS YOUR RESPONSIBILTY TO KEEP THEM OUT". Check out for tables that with high update frequency, where these kind of problems usually seen. There are developed procedures (by Oracle people) to detect blockers, and works pretty well. Once you find one, issue an "alter session kill ...", or simply kill the shadow process at system level.

Hope this helps.

Andy :) Received on Mon Sep 18 1995 - 00:00:00 CEST