A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Deadlocks (ORA-00060) in Oracle

2 min read
A deadlock is a situation in which two or more users are waiting for data locked by each other. 

Deadlocks prevent some transactions from continuing to work. 

Oracle Database automatically detects deadlocks and resolves them by rolling back one statement involved in the deadlock, releasing one set of the conflicting row locks.

The database returns a corresponding message to the transaction that undergoes statement-level rollback. The statement rolled back belongs to the transaction that detects the deadlock. Usually, the signaled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.
TransactionsSession 1Session 2Explanation
t0SQL> UPDATE employees
SET salary = salary*1.1
WHERE employee_id = 100;
1 row updated.
SQL> UPDATE employees
SET salary = salary*1.1
WHERE employee_id = 200;
1 row updated.
Session 1 starts transaction
1 and updates the salary for
employee 100. Session 2 starts
transaction 2 and updates the
salary for employee 200. No
problem exists because each
transaction locks only the row
that it attempts to update
t1SQL> UPDATE employees
SET salary = salary*1.1
WHERE employee_id = 200;
— prompt does not return
SQL> UPDATE employees
SET salary = salary*1.1
WHERE employee_id = 100;
— prompt does not return
Transaction 1 attempts to update
the employee 200 row, which is
currently locked by transaction 2.
Transaction 2 attempts to update
the employee 100 row, which is
currently locked by transaction 1.

A deadlock results because
neither transaction can obtain
the resource it needs to proceed
or terminate. No matter how
long each transaction waits, the
conflicting locks are held.
t2UPDATE employees
*
ERROR at line 1:
ORA-00060: deadlock
detected
while waiting for resource
SQL>
Transaction 1 signals the
deadlock and rolls back the
UPDATE statement issued at t1.
However, the update made at t0
is not rolled back. The prompt is
returned in session 1.

Note: Only one session in
the deadlock actually gets the
deadlock error, but either session
could get the error.
t3SQL> COMMIT;
Commit complete.
Session 1 commits the update
made at t0, ending transaction
The update unsuccessfully
attempted at t1 is not committed.
t41 row updated.
SQL>
The update at t1 in transaction
2, which was being blocked by
transaction 1, is executed. The
prompt is returned.
t5SQL> COMMIT;
Commit complete.
Session 2 commits the updates
made at t0 and t1, which ends
transaction 2.
Deadlocks most often occur when transactions explicitly override the default locking of Oracle Database. Because Oracle Database does not escalate locks and does not use read locks for queries, but does use row-level (rather than page-level) locking, deadlocks occur infrequently.

Hope it worked !! šŸ™‚