.::: Simulation Blocking,lock, Locking in PostgreSQL EDB with Advanced process list Pg-Activity ,pg Lock like 'Waiting for table metadata lock' MariaDB MySQL :::.

 

correlation metadata mariadb https://teguhth.blogspot.com/2026/07/how-to-simulation-locking-with-waiting.html
correlation https://teguhth.blogspot.com/2023/10/how-to-sample-simulation-blocking-in.html

1. terminal 1

BEGIN;

SELECT
    pg_backend_pid() AS session_id,
    current_user AS user;

UPDATE pembelian
SET jumlah_pembelian = 10
WHERE kode_pembelian = 'BEL-E001';

-- JANGAN COMMIT
-- biarkan transaksi terbuka

BEGIN;
SELECT pg_backend_pid() AS session_id,current_user AS user;
UPDATE pembelian SET jumlah_pembelian = 10 WHERE kode_pembelian = 'BEL-E001';
  

2. terminal 2

SELECT
    pg_backend_pid() AS session_id,
    current_user AS user;
    
ALTER TABLE pembelian
ADD COLUMN keterangan VARCHAR(100);

SELECT pg_backend_pid() AS session_id,current_user AS user;    
ALTER TABLE pembelian ADD COLUMN keterangan VARCHAR(100);
 

3. terminal 3 

SELECT pg_backend_pid() AS session_id,current_user AS user;    
ALTER TABLE pembelian ADD COLUMN remaks VARCHAR(50);
 

4. check procest locking 

queri 1

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.client_addr AS blocked_client,
    blocked.application_name AS blocked_app,
    blocked.state AS blocked_state,
    blocked.wait_event_type,
    blocked.wait_event,
    now() - blocked.query_start AS blocked_duration,

    blocked.query AS blocked_query,

    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.client_addr AS blocker_client,
    blocker.application_name AS blocker_app,
    blocker.state AS blocker_state,
    now() - blocker.xact_start AS blocker_xact_duration,

    blocker.query AS blocker_query,

    blocked_l.relation::regclass AS blocked_relation,
    blocked_l.locktype AS blocked_locktype,
    blocked_l.mode AS blocked_lock_mode,
    blocked_l.granted AS blocked_granted,

    blocker_l.locktype AS blocker_locktype,
    blocker_l.mode AS blocker_lock_mode,
    blocker_l.granted AS blocker_granted

FROM pg_stat_activity blocked

JOIN LATERAL unnest(pg_blocking_pids(blocked.pid))
    AS b(blocker_pid)
    ON true

JOIN pg_stat_activity blocker
    ON blocker.pid = b.blocker_pid

LEFT JOIN pg_locks blocked_l
    ON blocked_l.pid = blocked.pid
   AND blocked_l.granted = false

LEFT JOIN pg_locks blocker_l
    ON blocker_l.pid = blocker.pid
   AND blocker_l.granted = true

WHERE blocked.datname = 'teguhth'

ORDER BY blocked.pid;
 

5. using query 2

queri 2

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.client_addr AS blocked_client,
    blocked.application_name AS blocked_app,
    blocked.state AS blocked_state,
    blocked.wait_event_type,
    blocked.wait_event,
    now() - blocked.query_start AS blocked_duration,

    blocked.query AS blocked_query,

    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.client_addr AS blocker_client,
    blocker.application_name AS blocker_app,
    blocker.state AS blocker_state,
    now() - blocker.xact_start AS blocker_xact_duration,

    blocker.query AS blocker_query,

    blocked_l.relation::regclass AS blocked_relation,
    blocked_l.locktype AS blocked_locktype,
    blocked_l.mode AS blocked_lock_mode,
    blocked_l.granted AS blocked_granted,

    blocker_l.locktype AS blocker_locktype,
    blocker_l.mode AS blocker_lock_mode,
    blocker_l.granted AS blocker_granted

FROM pg_stat_activity blocked

JOIN LATERAL unnest(pg_blocking_pids(blocked.pid))
    AS b(blocker_pid)
    ON true

JOIN pg_stat_activity blocker
    ON blocker.pid = b.blocker_pid

LEFT JOIN pg_locks blocked_l
    ON blocked_l.pid = blocked.pid
   AND blocked_l.granted = false

LEFT JOIN pg_locks blocker_l
    ON blocker_l.pid = blocker.pid
   AND blocker_l.granted = true

WHERE blocked.datname = 'teguhth'

ORDER BY blocked.pid;
 

6. using query 3 - Blocking tree

Blocking tree

queri 3
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.client_addr AS blocked_client,
    blocked.application_name AS blocked_app,
    blocked.state AS blocked_state,
    blocked.wait_event_type,
    blocked.wait_event,
    now() - blocked.query_start AS waiting_for,
    blocked.query AS blocked_query,

    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.client_addr AS blocker_client,
    blocker.application_name AS blocker_app,
    blocker.state AS blocker_state,
    now() - blocker.xact_start AS transaction_age,
    blocker.query AS blocker_query

FROM pg_stat_activity blocked

CROSS JOIN LATERAL unnest(
    pg_blocking_pids(blocked.pid)
) AS bp(blocker_pid)

JOIN pg_stat_activity blocker
    ON blocker.pid = bp.blocker_pid

ORDER BY blocked.query_start;

Ini menurut saya query utama untuk monitoring blocking.
 

7. Lock yang sedang ditunggu
queri 4

SELECT
    a.pid,
    a.usename,
    a.client_addr,
    a.application_name,
    a.state,
    a.wait_event_type,
    a.wait_event,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS relation_name,
    now() - a.query_start AS query_duration,
    a.query
FROM pg_stat_activity a
JOIN pg_locks l
    ON l.pid = a.pid
WHERE l.granted = false
ORDER BY a.query_start;

Dalam kasus Anda, kemungkinan terlihat:

PID     3253
USER    adminaes
MODE    AccessExclusiveLock
GRANTED false
TABLE   pembelian

granted = false adalah session yang sedang menunggu lock.
 

8. Lock yang sedang dipegang blocker

queri 5

SELECT
    a.pid,
    a.usename,
    a.client_addr,
    a.application_name,
    a.state,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS relation_name,
    now() - a.xact_start AS transaction_age,
    a.query
FROM pg_stat_activity a
JOIN pg_locks l
    ON l.pid = a.pid
WHERE l.granted = true
  ----AND a.pid = 3226 

  AND a.pid = 5207
ORDER BY a.pid;


SELECT
    a.pid,
    a.usename,
    a.client_addr,
    a.application_name,
    a.state,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS relation_name,
    now() - a.xact_start AS transaction_age,
    a.query
FROM pg_stat_activity a
JOIN pg_locks l
    ON l.pid = a.pid
WHERE l.granted = true
ORDER BY a.pid;
 

9. report dba 

queri 6
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.client_addr AS blocked_client,
    blocked.application_name AS blocked_app,
    blocked.state AS blocked_state,

    now() - blocked.query_start AS blocked_duration,

    blocked.wait_event_type,
    blocked.wait_event,

    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.client_addr AS blocker_client,
    blocker.application_name AS blocker_app,
    blocker.state AS blocker_state,

    now() - blocker.xact_start AS blocker_xact_age,

    blocked.query AS blocked_query,
    blocker.query AS blocker_query,

    pg_blocking_pids(blocked.pid) AS blocking_pids

FROM pg_stat_activity blocked

JOIN LATERAL unnest(
    pg_blocking_pids(blocked.pid)
) b(pid)
ON true

JOIN pg_stat_activity blocker
ON blocker.pid = b.pid

WHERE blocked.datname = 'teguhth'

ORDER BY blocked.query_start;
 

10. report dba more complete


queri 7 

SELECT
    -- =========================
    -- BLOCKED SESSION
    -- =========================
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.client_addr AS blocked_client,
    blocked.application_name AS blocked_app,
    blocked.state AS blocked_state,

    now() - blocked.query_start AS blocked_duration,

    blocked.wait_event_type,
    blocked.wait_event,

    blocked.query AS blocked_query,

    -- =========================
    -- BLOCKER SESSION
    -- =========================
    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.client_addr AS blocker_client,
    blocker.application_name AS blocker_app,
    blocker.state AS blocker_state,

    now() - blocker.xact_start AS blocker_xact_age,

    blocker.query AS blocker_query,

    -- =========================
    -- LOCK
    -- =========================
    blocked_l.locktype,
    blocked_l.relation::regclass AS relation_name,

    blocked_l.mode AS blocked_lock_mode,
    blocked_l.granted AS blocked_granted,

    blocker_l.mode AS blocker_lock_mode,
    blocker_l.granted AS blocker_granted,

    pg_blocking_pids(blocked.pid) AS blocking_pids

FROM pg_stat_activity blocked

JOIN LATERAL unnest(
    pg_blocking_pids(blocked.pid)
) AS bp(blocker_pid)
    ON true

JOIN pg_stat_activity blocker
    ON blocker.pid = bp.blocker_pid

LEFT JOIN pg_locks blocked_l
    ON blocked_l.pid = blocked.pid
   AND blocked_l.granted = false

LEFT JOIN pg_locks blocker_l
    ON blocker_l.pid = blocker.pid
   AND blocker_l.granted = true
   AND blocker_l.locktype = blocked_l.locktype
   AND blocker_l.database IS NOT DISTINCT FROM blocked_l.database
   AND blocker_l.relation IS NOT DISTINCT FROM blocked_l.relation

WHERE blocked.datname = 'teguhth'

ORDER BY blocked.query_start;