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

Notice:

  • 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 deadlock error ORA-00060


Resolving deadlocks


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

  • (or) 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 an Deadlock alert, immediately contact application team and inform them.

934 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis