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