2023年5月22日 星期一

PostgreSQL 找出造成卡住 Blocked 狀況的 Leading SQL

PG 在發生 Locks 時，在一些較複雜的系統常常會看到 Blocked Chain 的狀況，B 使用者被 A 使用者查詢卡住，造成 C 使用者又被 B 使用者卡住，這種時候，雖然會查到很多 Locked 的狀況，但不需要全部砍掉，通常只要將源頭砍掉就會解開了，因此基於原本的 Blocked SQL 做加工。

Blocking 代表卡住造成別人無法執行查詢的 SQL，但這個 SQL 也有可能被前面的 SQL 卡住，而 Bloced 代表被卡住的 SQL，主要透過該 Session 的 Wait Event 來做判斷，如果為 Lock 就代表他有被別人卡住，如果是其他 Client Read 等等就代表正在順利執行或是 idle in transaction (通常是這個)，則可以判斷是卡住的源頭。


查詢 SQL

SELECT
  COALESCE(blockingl.relation::regclASs::text,blockingl.locktype) AS locked_item,
  blockinga.pid AS blocking_pid,
  blockinga.query AS blocking_query, 
  blockingl.mode AS blocking_mode,
  CASE 
    WHEN blockinga.wait_event_type = 'Lock' THEN 'False'
    ELSE 'True'
  END  AS leading_lock,
  blockeda.pid AS blocked_pid, 
  blockeda.query AS blocked_query,
  blockedl.mode AS blocked_mode,
  now() - blockeda.query_start AS blocked_time
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
    (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted
order by leading_lock desc;
查詢結果
   locked_item    | blocking_pid |       blocking_query        |    blocking_mode    | leading_lock | blocked_pid |       blocked_query        |    blocked_mode     |  blocked_time
------------------+--------------+-----------------------------+---------------------+--------------+-------------+----------------------------+---------------------+-----------------
 pgbench_accounts |       548294 | truncate pgbench_accounts ; | ShareLock           | True         |      548398 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:29.498179
 pgbench_accounts |       548294 | truncate pgbench_accounts ; | ShareLock           | True         |      548484 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:23.333006
 pgbench_accounts |       548294 | truncate pgbench_accounts ; | AccessExclusiveLock | True         |      548398 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:29.498179
 pgbench_accounts |       548294 | truncate pgbench_accounts ; | AccessExclusiveLock | True         |      548484 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:23.333006
 pgbench_accounts |       548398 | truncate pgbench_accounts;  | AccessExclusiveLock | False        |      548484 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:23.333006
 pgbench_accounts |       548484 | truncate pgbench_accounts;  | AccessExclusiveLock | False        |      548398 | truncate pgbench_accounts; | AccessExclusiveLock | 00:04:29.498179

參考資料：

https://ravenonhill.blogspot.com/2019/08/pgsql-who-blocks-my-query.html


on