oracle lock 04 - Locks and Deadlocks

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 signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.

Table 9-5 illustrates two transactions in a deadlock.

Table 9-5 Deadlock Example

 

TimeSession 1Session 2Explanation

t0

SQL> 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.

t1

SQL> UPDATE employees   SET salary = salary*1.1  WHERE employee_id = 200;-- prompt does not return

SQL> UPDATE employees   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.

t2

UPDATE employees       *ERROR at line 1:ORA-00060: deadlock detectedwhile waiting for resourceSQL>

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.

t3

SQL> COMMIT;Commit complete.

Session 1 commits the update made at t0, ending transaction 1. The update unsuccessfully attempted at t1 is not committed.

t4

1 row updated.SQL>

The update at t1 in transaction 2, which was being blocked by transaction 1, is executed. The prompt is returned.

t5

SQL> 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.