Re: Orphaned Row Locks

No I'm not talking about deadlocks. When i mean 'orphaned locks' i mean that the parent - the application - is dead and the locks are still alive...thus are orphans. :-) (I should make that copyright but in the interest of open source....hehehe

On Thu, 11 Mar 1999 21:44:51 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:

>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

Which is more important :
The Country or it's populace? Received on Fri Mar 12 1999 - 03:39:52 CST