Common misconception about how Oracle handles deadlocks

    Aug 31, 2017 12:03:00 PM by Pini Dibask

    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<br/>          SET first_name = 'Mark'<br/>          WHERE employee_id = 39;<br/>

     

    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&gt; UPDATE employee<br/>          SET first_name = 'John'<br/>          WHERE employee_id = 151;<br/>

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

    Tags: Oracle

    Pini Dibask

    Written by Pini Dibask

    Pini Dibask is an Oracle Database Technologist and Architect with more than 10 years of experience. During these years Pini has worked as an Oracle DBA and Oracle DBA team leader. He is the Product Manager for Databases Solutions at Dell Software. Pini is an Oracle Database Certified Professional and also OPN Certified Specialist. See his blog at: OracleDBPro.BlogSpot.com Expertise: Performance Tuning Storage & ASM RAC & RAC One Node Failover Clusters Data Guard Golden Gate RMAN Flashback Features Installations & Upgrades Data Migrations Exadata Troubleshooting OTN Community forum page: http://community.oracle.com/people/Pini%20Dibask