.::: How to Sample Simulation Blocking in PostgreSQL EDB :::.

1. Create a Test Table:

-bash-4.2$ psql
psql (15.4)
Type "help" for help.

postgres=# \c teguhth
You are now connected to database "teguhth" as user "postgres".
teguhth=#


First, create a test table in your PostgreSQL database:
\c teguhth

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    data text
);
 
2. Open two separate database connections, either in different terminal sessions or by using a PostgreSQL client tool like psql.

3. Simulate Blocking:

In Connection 1:


-- Start a transaction and lock a row
BEGIN;
SELECT '1' as Terminal,* FROM test_table WHERE id = 1 FOR UPDATE;
SELECT pg_sleep(30);

-- This locks the row with id = 1

-- Sleep for a while (simulating some processing)
-- In PostgreSQL, you can use pg_sleep for this purpose


In Connection 2 (while Connection 1 is still in the transaction):


-- Try to access the same locked row
BEGIN;
SELECT '2' as Terminal,* FROM test_table WHERE id = 1 FOR UPDATE;
-- This will block until the lock in Connection 1 is released


In Connection 3 (while Connection 1, 2 is still in the transaction):


-- Try to access the same locked row
BEGIN;
SELECT '3' as Terminal,* FROM test_table WHERE id = 1 FOR UPDATE;
-- This will block until the lock in Connection 1 is released
 

4. Check for Blocking:

To check for blocking, you can use various methods:

Run the following query to view the current database sessions and their statuses:

SELECT * FROM pg_stat_activity;

Look for sessions in a "waiting" state. The state column will show you if a session is waiting for a lock.
 

5. check using pg_locks:
Run the following query to see which locks are currently held by which sessions:

SELECT * FROM pg_locks;
This will provide detailed information about locks, including the lock type, mode, and which session holds the lock.
 

6. pg_blocking_pids:
You can use the pg_blocking_pids function to find the process IDs (PIDs) of sessions that are blocking other sessions. For example:


This will return the PIDs of sessions that are blocking the current session.


SELECT pg_blocking_pids(pg_backend_pid());