DeadLock in Oracle Database

A deadlock happens when two or more Transactions are waiting for resources locked by each other, resulting in all the Transactions being blocked.

Pictorial Representation of DeadLock

Let’s see , How to create deadlock in Oracle Database. Create Two Tables in same Schema. Open two SQL Developer IDE.

Creating Two Tables in same schema

CREATE TABLE Adam1
(
ID NUMBER(2) CONSTRAINT adam_col1_pk23 PRIMARY KEY,
Name VARCHAR2(20),
Salary NUMBER(5)
);

INSERT INTO Adam1 VALUES(12,’Jason’,2000);
INSERT INTO Adam1 VALUES(13,’Martin’,2500);
INSERT INTO Adam1 VALUES(14,’Cavin’,1900);
INSERT INTO Adam1 VALUES(15,’Harry’,1800);
select * from Adam1;

–***
CREATE TABLE Adam2
(
ID NUMBER(2) CONSTRAINT adam2_col1_pk23 PRIMARY KEY,
Name VARCHAR2(20),
Salary NUMBER(5)
);

INSERT INTO Adam2 VALUES(12,’Jason’,2000);
INSERT INTO Adam2 VALUES(13,’Martin’,2500);
INSERT INTO Adam2 VALUES(14,’Cavin’,1900);
INSERT INTO Adam2 VALUES(15,’Harry’,1800);

select * from Adam2;

DeadLock created on 3rd Query execution.

on Transaction 1 Execute Following Queries

–Transaction 1
update Adam1 set Name = ‘Marry’
where ID=12;

update Adam2 set Name = ‘Marry’
where ID=12;

on Transaction 2 Execute Following Queries like Above Picture

— Transaction 2
update Adam2 set Name = ‘Martin’
where ID=12;

update Adam1 set Name = ‘Martin’
where ID=12;

After Following All above steps , you are able to create deadlock situation. if Any doubts on steps , follow the below video link.

Happy Learning, Thanks.

Post Author: adama

Leave a Reply

Your e-mail address will not be published. Required fields are marked *