First page Back Continue Last page Graphics
Locking Issues: Deadlocks
Two sessions are blocked
Each waiting for resource the other holds
Oracle chooses one session for rollback
- Only the statement causing deadlock is rolled back
Notes:
A deadlock is a situation where two transactions are blocking each other, and they are both waiting a resource that the other holds (i.e. has locked).
Deadlocks are automatically detected (Oracle queries v$lock view every 3 seconds). When detected, Oracle will write an entry to the server trace file and automatically roll back one of the two statements involved in the deadlock. The “victim” will receive the following error message:
ORA-00060: deadlock detected while waiting for resource
In the example above, after Oracle automatically rolled back one of the blocking statements (see Time 5), note that Session A was still blocked until Time 7, when Session B issued a ROLLBACK.