• Arun Kumar

Removing Deadlocks in Oracle

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!


Read our article on finding locked objects inside database and killing sessions!

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.

2,842 views

Recent Posts

See All