Home » SQL & PL/SQL » SQL & PL/SQL » How to free exclusive row lock after deadlock???
How to free exclusive row lock after deadlock??? [message #296912] Tue, 29 January 2008 08:00 Go to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

I logged in into two differnet sessions side by side to the same schema.. (say) USER01. I created a deadlock situation by following the order as below:

session1>>>
UPDATE T1 SET COL=10 WHERE COL=1;

session2>>>
UPDATE T1 SET COL=20 WHERE COL=2;

session1>>>
UPDATE T1 SET COL=200 WHERE COL=2;

session2>>>
UPDATE T1 SET COL=100 WHERE COL=1;


without any commits.



Now I destroyed both the sessions. Logged in to yet another session under the schema USER01. Fired an update again involving the previously locked row

UPDATE T1 SET COL=10 WHERE COL=1;

I find the session still hangs which means the row lock is not yet fred. How can I now free the row lock (other than by bouncing the database)???

Just to tell you, the lock on the row corresponding to COL=2 got fred when I destroyed session1.. but the row corresponding to COL=1 didn't. Why so??

Re: How to free exclusive row lock after deadlock??? [message #296919 is a reply to message #296912] Tue, 29 January 2008 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post exactly when you did, that is copy and paste each SQL*Plus session.
What you say can't happen unless you did it wrong.
First you can't have a deadlock (I mean the session can't hang forever), Oracle automatically detects it and kill one of the transactions.
Next when you kill the sessions, Oracle roll back their current transaction and release the lock within few time. You don't have to do anything(more you can't do anything).

Regards
Michel
Re: How to free exclusive row lock after deadlock??? [message #297162 is a reply to message #296912] Wed, 30 January 2008 07:16 Go to previous messageGo to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

amazing!!!

you are absolutely right.. Smile ... i cannot replicate the situation again..

BUT BELIEVE ME.. IT REALLY HAPPENED ONCE!!! .. i dont know how.. but it happened
Re: How to free exclusive row lock after deadlock??? [message #297180 is a reply to message #297162] Wed, 30 January 2008 08:35 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As you stated, Michel is right, which sadly means that the described situation is impossible.
It is very unlikely that it 'really happened once', I am afraid that you might have checked a wrong table, or misinterpreted what you saw.
Previous Topic: Can we get the Columns be bold in the EXCEL Sheet when it was opened
Next Topic: Why does oracle allow to create such ambiguous synonyms??
Goto Forum:
  


Current Time: Wed Dec 07 05:13:43 CST 2016

Total time taken to generate the page: 0.09685 seconds