Common misconception about how Oracle handles deadlocks

Introduction

In today's blog post, I'd like to uncover a common misconception about how Oracle handles deadlocks, but first let's talk about what deadlock is. Deadlock is a locking scenario that occurs when two or more sessions are blocked as they wait because of a lock that is being held by the other session. Oracle Database automatically detects this scenario and handles this because otherwise, they will wait forever as both of them are blocked and waiting to each other’s locked resources. But the question is how does Oracle handle this? some would say that Oracle handles these scenarios by terminating one of the sessions which is known to be as the "deadlock victim" - well, that's wrong. Others would say that by rolling-back the entire transaction of one of the sessions - well, that's also wrong. So how Oracle really handles deadlock scenarios?

Deadlock Illustration

Let's see a quick example of how deadlock occurs

Step 1

Session #1 performs an update of a row (employee #151) and acquires a lock on that row

SQL> UPDATE employee
          SET first_name = 'David'
          WHERE employee_id = 151;

1 row updated.

Step 2

Session #2 performs an update of a row (employee #39) and acquires a lock on that row

SQL> UPDATE employee
          SET first_name = 'Greg'
          WHERE employee_id = 39;

1 row updated. 

Step 3

Session #1 performs an update of a row (employee #39) and is now waiting since a lock has been acquired on the same row which has not been released yet as session #2 transaction is still active

UPDATE employee
          SET first_name = 'Mark'
          WHERE employee_id = 39;

Step 4

Session #2 performs an update of a row (employee #151) and is now waiting since a lock has been acquired on the same row which has not been released yet as session #1 transaction is still active.  

SQL> UPDATE employee
          SET first_name = 'John'
          WHERE employee_id = 151;

At this stage both sessions (session #1 and session #2) are blocked  and waiting to each other’s locked resources - that's exactly what deadlock is.

So how Oracle handles deadlock scenarios?

The way Oracle handles deadlock scenarios is not by terminating one of the sessions or performing a transaction-level rollback, it's actually just by performing a statement-level rollback to one of the sessions.  The session that its statement is being rolled back, will encounter an “ORA-00060: Deadlock detected while waiting for resource.” error message (that will also be recorded in the alert log file).