Question

What is dead lock, when it occurs and how to fix it?

Answer 1

Dead lock occurs when two or more users waiting for the resources locked by each other. The users are stuck and they can not proceed as they may end up waiting indefinitely for the resources form each other. So when this condition occurs these users are stuck (deadly embraced) and cannot continue processing.

Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions/statements involved in the deadlock, thus releasing one set of resources/data locked by that transaction. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also produce detailed information in a trace file under database's UDUMP directory.

Most commonly these deadlocks are caused by the applications that involve multi table updates in the same transaction and multiple applications/transactions are acting on the same table at the same time. These multi-table deadlocks can be avoided by locking tables in same order in all applications/transactions, thus preventing a deadlock condition.

In the following example I am demonstrating a dead lock scenario . In the first session ie. session1 lock table: CUSTOMER then CONTACT; and in session2: CONATCT then CUSTOMER.

 

Here is an example of how to simulate a deadlock error:

Session 1 lock table CUSTOMER:
SQL> update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486';

1 row updated.

Session 2 lock table CONTACT:

SQL> update contact set language_id = 8 where customer_ref = '10000000000000000486';

1 row updated.

Session 1 now update CONTACT. The session will hang waiting for a lock (not a deadlock yet!):

SQL> update contact set language_id = 8 where customer_ref = '10000000000000000486';

Session 2 now update CUSTOMER, causing the deadlock:

SQL> update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486';
update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486'
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

SQL>

Comments

Resolution/Fix for deadly Embrace Dead Lock:

The session on which ORA-00060 occurred encountered the dead lock and Oracle automatically rolled back the statement that caused the dead lock. No other session got affected, so other sessions functions as usual.

In the affected session, the rolled back statement needs to be re-executed once the resources are available ie. When no other session is competing for the same resource.

Probably it will be a good idea to analyze oracle trace file and then modify the application to avoid this situation.

To avoid deadly embrace dead locks, review the application transaction logic thoroughly in the design phase it-self and ensure tables are ordered with in the transactions/applications in such a way that resource contention not occurs among transactions/applications.