Remove deadlock without killing session

Question

Is there any workaround to remove deadlock without killing the session?

Solution 1

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

Discussion about removing deadlock on AskTom

Solution 2

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.