This article is half-done without your Comment! *** Please share your thoughts via Comment ***
PostgreSQL 9.6 introduced a pg_blocking_pids() to get a blocking process id of your running transaction.
When multiple sessions are trying to access a table, and that table is locked. At that time, we need to find that blocking process id which blocked that table so that we can take necessary steps.
Using pg_blocking_pids(), we can get that head process id which is blocking other connections.
Below is a full demonstration of this:
First, save your current process id:
|
1 2 |
SELECT pg_backend_pid(); -- my process id is: 6720 |
Create a sample table:
|
1 |
CREATE TABLE tbl_students(rno int, name character varying(10)); |
Put your table in lock mode:
|
1 2 |
BEGIN TRANSACTION; LOCK tbl_students IN ACCESS EXCLUSIVE MODE; |
Open a new session, and save process id for that session also:
|
1 2 |
SELECT pg_backend_pid(); -- my process id is: 5936 |
Now in that new session, try to insert below record:
Your result will be in waiting mode…
|
1 |
INSERT INTO tbl_students VALUES (1,'Anvesh'); |
Open a third new session, and execute below command with process id of your second session:
|
1 |
SELECT pg_blocking_pids(5936); |
The result is:
|
1 2 3 |
pg_blocking_pids -------------------- {6720} |
