PostgreSQL 鎖等待監控 珍藏級SQL - 誰堵塞了誰

postgresql , pg_locks , pg_stat_activity , 鎖監控 , 誰堵塞了誰

在資料庫中，通過鎖以及多版本并發控制可以保護資料的一緻性，例如a正在查詢資料，b就無法對a通路的對象執行ddl。a正在更新某條記錄，b就不能删除或更新這條記錄。

鎖是資料庫自動管理的，同時資料庫還提供了ad lock或者lock文法，允許使用者自己控制鎖。

例如ad lock的應用可以參考如下：

<a href="https://github.com/digoal/blog/blob/master/201705/20170507_02.md">《postgresql 使用advisory lock實作行級讀寫堵塞》</a>

<a href="https://github.com/digoal/blog/blob/master/201610/20161020_02.md">《postgresql 無縫自增id的實作 - by advisory lock》</a>

<a href="https://github.com/digoal/blog/blob/master/201610/20161018_01.md">《postgresql 使用advisory lock或skip locked消除行鎖沖突, 提高幾十倍并發更新效率》</a>

<a href="https://github.com/digoal/blog/blob/master/201611/20161117_01.md">《聊一聊雙十一背後的技術 - 不一樣的秒殺技術, 裸秒》</a>

當然，如果應用程式邏輯設計不慎，就可能導緻嚴重的鎖等待，或者死鎖的産生。

如果你發現sql請求大多數時候處于等待鎖的狀态，那麼可能出現了業務邏輯的問題。

如何檢查或監控鎖等待呢？

postgresql提供了兩個視圖

1. pg_locks展示鎖資訊，每一個被鎖或者等待鎖的對象一條記錄。

2. pg_stat_activity，每個會話一條記錄，顯示會話狀态資訊。

我們通過這兩個視圖可以檢視鎖，鎖等待情況。同時可以了解發生鎖沖突的情況。

pg_stat_activity.query反映的是目前正在執行或請求的sql，而同一個事務中以前已經執行的sql不能在pg_stat_activity中顯示出來。

是以如果你發現兩個會話發生了沖突，但是他們的pg_stat_activity.query沒有沖突的話，那就有可能是他們之間的某個事務之前的sql擷取的鎖與另一個事務目前請求的query發生了鎖沖突。

如果追蹤詳細的鎖沖突資訊：

1. 可以通過lock trace跟蹤鎖等待的詳細資訊，

<a href="https://github.com/digoal/blog/blob/master/201301/20130128_01.md">《postgresql developer options (debug, trace, system table mod and so on...) 詳解》</a>

2. 通過資料庫日志（開啟lock_timeout, log_lockwait參數）(csvlog)跟蹤鎖等待資訊，

3. 或者通過資料庫日志（開啟log_statements='all'，sql審計）追蹤事務中所有的sql (csvlog)，分析事務之間的鎖沖突。

4. 通過sql檢視持鎖，等鎖的事務狀态。

鎖的釋放時機：

大多數鎖要等待事務結束後釋放，某些輕量級鎖（資料庫自動控制）是随用随釋放的。

這條sql非常有用，建議dba珍藏。

如果覺得寫sql麻煩，可以将它建立為視圖

1. 前面的鎖查詢sql，已經清晰的顯示了每一個發生了鎖等待的對象，按鎖的大小排序，要快速解出這種狀态，terminate最大的鎖對應的pid即可。

會話d

幹掉23043後，大家都清淨了

如果是greenplum，由于版本問題，sql語句略微不一樣，如下：

測試

關注gp_segment_id=-1的，長時間等待，殺掉procpid即可。

<a href="https://www.postgresql.org/docs/9.6/static/view-pg-locks.html">https://www.postgresql.org/docs/9.6/static/view-pg-locks.html</a>

<a href="https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#pg-stat-activity-view">https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#pg-stat-activity-view</a>

<a href="https://www.postgresql.org/docs/9.6/static/mvcc.html">https://www.postgresql.org/docs/9.6/static/mvcc.html</a>

<a href="https://github.com/digoal/blog/blob/master/201104/20110408_01.md">《compare postgresql and oracle dead lock detect and transaction》</a>

<a href="https://github.com/digoal/blog/blob/master/201306/20130620_01.md">《postgresql lock waiting order》</a>

<a href="https://github.com/digoal/blog/blob/master/201211/20121114_01.md">《postgresql row lock and htup.t_infomask thinking》</a>