This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The DeadLock in a database system is a very common thing and all Database Administrator responsible to detect and manage a DeadLock situation.
In this post, I am sharing one small demonstration t to generate a DeadLock situation in a PostgreSQL.
Most of the time, We are facing DeadLock problem in our Production Database Server.
But What happen, when Database Administrator wants to generate DeadLock situation and wants to create a script for detecting a DeadLock.
First create two sample tables:
|
1 2 |
CREATE TABLE ABC (ID INT PRIMARY KEY); CREATE TABLE XYZ (ID INT PRIMARY KEY); |
Open Session – 1 and execute below code:
|
1 2 |
BEGIN; INSERT INTO ABC VALUES (1); |
Open Session – 2 and execute below code:
|
1 2 3 |
BEGIN; INSERT INTO XYZ VALUES (1); INSERT INTO ABC VALUES (1); |
Again open Session – 1 and execute below Insert Statement:
|
1 |
INSERT INTO XYZ VALUES (1); |
You will get a below deadlock message:
|
1 2 3 4 5 |
ERROR: deadlock detected DETAIL: Process 5944 waits for ShareLock on transaction 892; blocked by process 4884. Process 4884 waits for ShareLock on transaction 891; blocked by process 5944. HINT: See server log for query details. CONTEXT: while inserting index tuple (0,2) in relation "xyz_pkey" |
