Re: Orphaned Row Locks

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 11 Mar 1999 21:44:51 GMT
Message-ID: <36fb3770.34847528@192.86.155.100>

A copy of this was sent to Chad Edwards <cedwards_at_infinet.com> (if that email address didn't require changing) On Thu, 11 Mar 1999 18:05:08 GMT, you wrote:

>Don't know about "orphans", but I do know that if you have a deadlock..ie
>2 locks that are waiting on the other before they will release...the only
>way to fix it is to shutdown your database and restart it.
>

huh? never happens that way. send us an example.

there are 2 deadlock cases

1. in a single database. These are detected immediately and one of the transactions gets an error message right away.
2. in a distributed database. These are detected over time, not immediately and the time is controlled by the init.ora parameter distributed_lock_timeout.

Simply put: Deadlocks are always detected and resolved...

Here is an example:

Session 1                 Session 2                 Comment
-------------------       ------------------        --------------------

update emp
set ename = lower(ename)
where empno = 7369;

                  
                          update emp                At this point, each session
                          set ename = lower(ename)  has its own row locked...
                          where empno = 7499          

update emp                                          Session 1 now becomes 
set ename = lower(ename)                            blocked since the row
where empno = 7499                                  it is trying to update
                                                    is already locked.

                         update emp                 Upon issuing this update,
                         set ename = lower(ename)   one of the session
                         where empno = 7369;        IMMEDIATELY recieves:
                                                    ORA-00060: deadlock detected
                                                    while waiting for resource
                                                    Lets say session 1 gets this

                                                    message
 
rollback;                                           Session 2 now becomes
                                                    unblocked and performs the
                                                    update of its 2cnd row.


This is the way it is documented to work in the server concepts manual.
                                                     

>According to docs I've read, the best way to keep this from happening is
>to have your DBA keep a close eye on the locks that are happening.
>
>Chad
>
>Clifford <clifford_at_bang.com> wrote:
>: Oracle Version : 7.3
>: I have an app that locks rows for updating. Now when this app crashes
>: (NT) these locks are still held by oracle so that no-one can update
>: said rows until the instances are killed via v$session/Instance
>: manager.
>
>: How do i ensure that the row locks are released when the app crashes.
>
>: Thanks :-)
>
>: Cliff
>: Which is more important :
>: The Country or it's populace?
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Mar 11 1999 - 15:44:51 CST