Is there any workaround to remove deadlock without killing the session?
Deadlock occurred when two or more session are waiting for data locked by each other, resulting in all the sessions being blocked.
P Sharma
2009-11-28 09:43:40
yes, of course, this is almost the definition of dead locks.
René Nyffenegger
2009-11-28 10:03:47
So, Question is simple-is there any way to clear deadlock without killing any session?
P Sharma
2009-11-28 10:11:51
+5
A:
Hi P Sharma,
From the Concepts Guide:
Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks.
You don't have to do anything to remove a deadlock, Oracle takes care of it automatically. The session is not killed, it is rolled back to a point just before the trigger statement. The other session is unaffected (i-e it still waits for the lock until the rolled back session either commits or rolls back its transaction).
In most situations deadlocks should be exceptionally rare. You can prevent all deadlocks by using FOR UPDATE NOWAIT statements instead of FOR UPDATE.
See also
Vincent Malgrat
2009-11-28 10:53:55
+1
A:
Deadlocks are automatically cleared in Oracle by cancelling one of the locked statements. You need not do it manually. One of the sessions will get "ORA-00060" and it should decide whether to retry or roll back.
But from you description it looks like you have a block, not deadlock.
Anyway, blocking session should somehow release its lock -- by commiting or rolling back its transaction. You can just wait for it (possibly for a long time). If you can change code of your application -- you probably can rewrite it to release lock or avoid it. Otherwise, you have to kill session to immediately unlock resources.
egorius
2009-11-28 10:54:58