儲存過程本身死鎖

我有一個奇怪的情況，從日誌中可以看出：

Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. 
Process 53807 waits for ExclusiveLock on advisory lock [16421,999999,12864395,2]; blocked by process 37278. 
Process 37278: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24) 
Process 53807: SELECT * FROM zel_api.insert_event ($1 ,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24)",
"See server log for query details.",,,"SQL statement ""SELECT pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer)""

這本身就很奇怪，因為看起來兩個程序阻塞了同一個諮詢鎖，但實際上都不能抓住它。

嘗試獲取鎖的函式如下：

CREATE OR REPLACE FUNCTION zel_event.create_new_partition(
   p_table_name text
)
RETURNS void AS
$BODY$
DECLARE
   _schema text;
BEGIN
   IF NOT EXISTS (table from catalog)
   THEN
       PERFORM pg_advisory_xact_lock(999999, format('zel_event.%I', p_table_name)::regclass::oid::integer);

       IF NOT EXISTS (table from catalog)
       THEN
           EXECUTE format('
               CREATE TABLE %1$I.%2$I (
                   LIKE zel_event.%2$I INCLUDING ALL
               ) INHERITS (zel_event.%2$I)', _schema, p_table_name);
       END IF;
   END IF;
   RETURN;

END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;

在我看來，沒有一個“經典”死鎖原因存在，因為只有一個邏輯……

其背後的想法是按需創建新表，其中需求可以從多個連接以非常高的頻率出現。當插入函式針對父表執行插入時呼叫它，並由調度程序觸發器轉移到適當的子表。

這是交易的樣子：

INSERT INTO parent (zel_api.insert_event())
       |
    trigger
       |
       +----> child exists? ---no---> CREATE TABLE child
                     |                         |
                    yes                        |
                     |                         |
                     V                         V
             INSERT INTO child (zel_event.create_new_partition())   

PostgreSQL 版本是 9.2

了解這種情況是如何發生的（當然是減輕的）將是非常有趣的。

這不是同一個諮詢鎖。您的日誌提到了第一個程序的不同表 oid（減 10）作為第二個。這看起來像一個典型的死鎖情況，兩個程序以不同的順序尋找相同的鎖。第一個中的第三個數字是 12864385，而第二個中的第三個數字是 12864395。

所以我懷疑兩個程序都試圖創建相同的分區，只是順序不同，所以它們死鎖了。

我發現在這種令人費解的情況下有用的一件事是在一系列數字中一次遍歷並比較一個數字，因為使用諮詢鎖很容易像這樣巧妙地漏掉一個數字。

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