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!

• Create a Deadlock

• Resolving 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.