共享鎖上的 Postgres 死鎖

我遇到了一個問題，即幾個 UPDATE sql 語句鎖定在數據庫中的特定表上。由於某種原因，這些語句似乎需要訪問“ShareLock”並且沒有被授予。通過查看 Postgres 文件，只能在執行 CREATE INDEX 時使用 Sharelock。但是，我的應用程序中的所有語句/查詢都不是 CREATE INDEX。它可以以某種方式隱式觸發嗎？

有問題的更新如下：

<update id="updateHealth">
       UPDATE table d
       SET fk_table_health_status_id = 
           (SELECT pk_table_health_status_id 
           FROM stat_table_health_status sdhs
           WHERE sdhs.table_health_status = #{health})
           <if test="health == 'Failed' || health == 'Other Failed'">
           ,last_failure = NOW(),
           failure_count = failure_count + 1</if>
       WHERE d.name = #{name}
   </update>

據我所知，僅此聲明不應該引起嚴重的問題。我已經同步了程式碼，所以沒有兩個執行緒可以同時寫入這個表。所以我有點不知道這是怎麼發生的……

在我嘗試調試這個時，我一直在執行這個查詢：

SELECT a.datname,
        l.relation::regclass,
        l.transactionid,
        l.mode,
        l.GRANTED,
        a.usename,
        LEFT(a.query, 20),
        a.query_start,
        age(now(), a.query_start) AS "age",
        a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

這講述了一個關於正在發生的事情的更好故事，但如果我完全誠實，我並不完全理解它（不是 dba，只是一個自願解決問題的開發人員）。從下表可以看出，有一個事務試圖訪問 dyn_device 表上的 ShareLock，但沒有被授予。然而，該事務似乎與正常的 UPDATE 語句是分開的（但它仍然是同一個查詢……）。我還注意到該交易的關係為空。也不完全確定為什麼會這樣。

那麼有人知道為什麼會發生這種情況嗎？或者也許更了解這個 pg_stat_activity 查詢？任何幫助，將不勝感激。

如果您想更好地了解進行呼叫的軟體，我寫了一個 stackoverflow 問題，重點更關注 java 。

https://stackoverflow.com/questions/69746549/idle-transactions-mybatis-jboss-6-4-postgres-9-6?noredirect=1#comment123288064_69746549

所以我發現了問題所在。問題真的不是數據庫的錯，甚至不是正在使用的查詢。事實證明，我們的系統對我們的數據源（Postgres 數據庫）和我們的 JMS 消息傳遞系統都使用了相同的事務子系統。發送 JMS 消息時，它會創建一個事務，並且在該執行緒/事務的生命週期中遵循的每個基於事務的操作都將被視為該事務的一部分。其中包括我們所有的數據庫呼叫……

這就解釋了為什麼像插入消息日誌這樣簡單的查詢會觸及我們在數據庫中的所有關係。調試查詢只向我顯示了第一個查詢，而不是在 JMS 消息的生命週期中使用的所有其他查詢。有幾種方法可以解決這個問題，但我的團隊選擇了最簡單的方法，即阻止數據源使用 JBoss 提供的事務管理器。

<datasource jndi-name="java:jboss/datasources/PostgresDS" jta="false" pool-name="PostgresDS" enabled="true" use-java-context="true">

您提到的文件是針對 table 上的 ShareLock的。但這不是表鎖，而是事務鎖。也就是說，它正在等待另一個事務結束。（每個session在自己的transactionid上擁有一個ExclusiveLock，不同的session通過請求對應的ShareLock來等待）

這是行鎖。一個會話可以同時鎖定數百萬行。不是將每個事務都輸入到 RAM 中的鎖表中，而是將衝突記錄為等待另一個事務結束，而不是等待特定的行鎖結束。

您在此處複製的表格相當亂碼。列向下移動了一部分，我懷疑行也失去了。應該有另一行的事務 ID 為“12849”。

引用自：https://dba.stackexchange.com/questions/302079