同一張表上的兩個簡單更新可能會死鎖嗎？

表 A 是：

id integer
version varchar
data jsonb (large data 1mb)
fkToBid integer (references B.id constraint)

表 B 是：

id integer
other... 

程序正在積極地執行下面的兩個更新，以任何順序和任何事務之外。

表 A 中的更新記錄有時會引用表 B 中的相同記錄。此外，有時會更新相同的 A 記錄。

UPDATE A.version WHERE A.id=:id
and
UPDATE A.data WHERE A.id=:id

為什麼或可以出現這種僵局？是因為表 A 中的更新記錄引用了表 B 中的同一行嗎？這種僵局會不會有別的原因？

為什麼我會在 B pk 索引上看到這些更新請求的AccessShareLock ？

為什麼我會在 B pk 索引上看到這些更新請求的AccessShareLock ？

Postgres 在檢查父表中是否存在外鍵以進行插入或更新時會使用該鎖。來自：RI_FKey_check_src/backend/utils/adt/ri_triggers.c

   /*
    * Get the relation descriptors of the FK and PK tables.
    *
    * pk_rel is opened in RowShareLock mode since that's what our eventual
    * SELECT FOR KEY SHARE will get on it.
    */
   fk_rel = trigdata->tg_relation;
   pk_rel = table_open(riinfo->pk_relid, RowShareLock);

問題是為什麼要檢查外鍵關係，因為您的更新都不會影響外鍵。

最可能的答案是 Postgres 無法檢測到更新不會影響關係。從RI_FKey_fk_upd_check_required同一源文件中：

   /*
    * If the original row was inserted by our own transaction, we must fire
    * the trigger whether or not the keys are equal.  This is because our
    * UPDATE will invalidate the INSERT so that the INSERT RI trigger will
    * not do anything; so we had better do the UPDATE check.  (We could skip
    * this if we knew the INSERT trigger already fired, but there is no easy
    * way to know that.)
    */
   xminDatum = slot_getsysattr(oldslot, MinTransactionIdAttributeNumber, &isnull);
   Assert(!isnull);
   xmin = DatumGetTransactionId(xminDatum);
   if (TransactionIdIsCurrentTransactionId(xmin))
       return true;

儘管程式碼註釋說了什麼，但此限制適用於更新兩次的行，以及插入行然後更新的規定情況。

揚聲器：

 CREATE TABLE b
 (
   id   BIGINT PRIMARY KEY
 );

 INSERT INTO b (id)
 VALUES(1);

 CREATE TABLE a
 (
   id BIGINT PRIMARY KEY,
   version INTEGER NOT NULL,
   data INTEGER NOT NULL,
   b_id BIGINT NOT NULL CONSTRAINT fk_a_b_id REFERENCES b (id)
 );

 INSERT INTO a (id, version, data, b_id)
 VALUES (1, 1, 1, 1);

explain (analyze, costs off)
UPDATE a
SET version = 2
WHERE id = 1;

explain (analyze, costs off)
UPDATE a
SET data = 3
WHERE id = 1;

db<>小提琴展示

請注意，僅第二次更新說明包括：

Trigger for constraint fk_a_b_id: time=0.386 calls=1

（是的，Postgres 使用每行內部觸發器來檢查和執行 RI。）

在 Postgres 9.3 及更高版本上，此 RI 檢查FOR KEY SHARE用於更好的並發性，但這並不意味著它是防死鎖的。

無論如何，儘管問題說明了什麼，但似乎在一個命令中發送了多個更新。這似乎是讓 Postgres不必要地執行產生父表鎖的 FK 檢查所必需的。

如果您有其他程序使用 訪問父表SELECT FOR UPDATE，則可以嘗試將其更改為“提高外鍵鎖定的並發性”Postgres 9.3 更新檔的主要作者在此 Stack Overflow 答案SELECT FOR NO KEY UPDATE中的建議。

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