top of page
DBA Genesis Docs logo

Removing Deadlocks in Oracle

Identify and resolve deadlocks in Oracle databases effectively.

You locked row A, another session locked row B. Now, you want to lock row B but you are blocked by the other session. The other session wants to lock row A but they are blocked by you. Hence, deadlock!

Deadlock is a situation where two or more transactions are waiting for one another to give up locks.



Create a Deadlock


Let us create a deadlock situation inside test database. Create a table and insert two records in it

CREATE TABLE deadlock_test(id number,
  fname varchar2(10),
  lname varchar2(10)
);

INSERT INTO deadlock_test VALUES (1,'John','Doe');
INSERT INTO deadlock_test VALUES (2,'Mark','Taylor');

COMMIT:

In the same session (where you executed above commands), try to update last name from the id 1

-- from session 1 putty window
UPDATE deadlock_test SET lname='Max' WHERE ID=1;

Do not commit yet otherwise the lock will be released. Now ope another session (new putty terminal) and try to update the last name for the id 2

-- from session 2 putty window
UPDATE deadlock_test SET lname='Booth' WHERE ID=2;

Note

  • The first session holds lock where ID=1

  • The second session holds lock where ID=2


Let us try to update the first name from session 1 where id=2 (note, the session 2 holds lock on this record)

-- from session 1 putty window
UPDATE deadlock_test SET fname='Francis' WHERE ID=2;

-- statement waiting

The above statement goes on waiting as session 2 holds the lock. From session 2, let us try to update the first name where id=1 (note, the session 2 holds lock on this record)

-- from session 2 putty window
UPDATE deadlock_test SET FNAME='Jim' WHERE ID=1

-- deadlock

This is the perfect situation of deadlock and your session 1 will get error ORA-00060.



Resolving Deadlock


Oracle is smart and it is able to find deadlock situation within 3 seconds. Below are the options to avoid deadlocks inside the database


  • Ask the session getting deadlock error ORA-00060 to issue either COMMIT or ROLLBACK

  • Ask the waiting session to kill the SQL / transaction

  • Look inside alert log / trace file for the sessions involved in deadlock and inform application team to improve the code


As a DBA, whenever you get a Deadlock alert, immediately contact application team and inform them.

Become a top notch dba.png
bottom of page