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

Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user. There are two simple ways to achieve this: Generate SQL for each table In this method,

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback