Re: How to release acquired locks

pberger_at_nic.wat.hookup.net (Paul Berger) wrote:

>In article <43spc3$pk2_at_hpcc48.corp.hp.com>,
>Yuk Hon Johnny Chan <jychan_at_corp.hp.com> wrote:
>>Paul Berger (pberger_at_nic.wat.hookup.net) wrote:

[snip]

>>
>>one last note, this is not a deadlock situation...there's another thread
>>that talks extensively about blocking locks vs dead locks, so i won't
>>rehash it here.
>>
>>jc
 

>Actually I am talking about deadlocks. When two sessions try to
>update the same row in a table one session is blocked indefinitely.
>This is a deadlock condition not a blocked condition as you allude to!
 

>PB

No, a deadlock is when session A locks row 1

                       session B locks row 2
                       session A ATTEMPTS TO LOCK row 2
                       session B ATTEMTPS TO LOCK row 1

It is no until step four that you have a deadlock. Up to that point you are simply waiting for data, step four makes it so that the wait would be infinite. That is a deadlock.

In general, if two sessions try to update the same row (and only update for example 1 row), then one of them will continue processing and the other will block. Since one of them is in fact running, you do not have a deadlock. Oracle does detect deadlocks. To see this in work, use the scott/tiger account with two windows and try the above example on the EMP table or something.

A couple of things you can do however:

Look at SELECT FOR UPDATE NOWAIT. If you do not want your update to block, perform a select for update NOWAIT on the data first. You will get an error message if the data you are going after is already locked.

Look at profiles. Profiles in the database can be used to log people off after a specified amount of idle time. In that fashion, when a client application terminates and for whatever reason the server does not detect it, the database will log them off (and roll them back releasing locks) after a specified duration.

Look at alter system kill session 'sid,serial#'. You can also use sqldba/server manager to identify and kill sessions. Look at the utllockt.sql script in $ORACLE_HOME/rdbms/admin to see how to identify sessions that are blocking other sessions to find out the correct sid/serial# combo to kill. Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Tue Sep 26 1995 - 00:00:00 CET