假設您有一個具有此定義的表：

CREATE TABLE public.positions
(
  id serial,
  latitude numeric(18,12),
  longitude numeric(18,12),
  updated_at timestamp without time zone
)

您在此表中有 50,000 行。現在出於測試目的，您將執行如下更新：

update positions
set  updated_at = now()
where latitude between 234.12 and 235.00;

該語句將從 50,000 行（在此特定數據集中）中更新 1,000 行。

如果您在 30 個不同的執行緒中執行這樣的查詢*，MySQL innodb 將成功，而 PostgreSQL 將因大量死鎖而失敗。

為什麼？

---

• 我在比較 MySQL innodb 和 Postgres 的最新版本，這是一個並發更新案例。生產案例：想像有 5000 隻股票不斷更新最新價格。

歷史課

2013 年 3 月 13 日，Uber 從 MySQL 切換到 PostgrsSQL。

令人驚訝的是，這段戀情並沒有持續多久。

2016 年 6 月 26 日，Uber 從 PostgreSQL 切換到 MySQL。

為什麼要打臉？？？

您的實際問題

UPDATE在 PostgreSQL 中執行一個令人驚訝的微管理。

PostgreSQL中有兩個DDL系統標識符必須正確理解

• ctid：行版本在其表中的物理位置。請注意，儘管 ctid 可用於非常快速地定位行版本，但ctid如果更新或移動行，行的版本將發生變化VACUUM FULL。因此ctid作為長期行標識符是無用的。OID，或者更好的使用者定義的序列號，應該用於辨識邏輯行。
• oid: 行的對象標識符（對象 ID）。僅當表是使用 WITH OIDS 創建的，或者當時設置了 default_with_oids 配置變數時，才會出現此列。該列是typeoid（與列同名）；有關類型的更多資訊，請參閱關於對象標識符類型的 PostgreSQL 文件。

關於ctids，請注意執行and UPDATE ( DELETE/INSERT從物理角度)orVACUUM FULL會改變一行的ctid。這對於具有許多索引的表來說不是一個好兆頭。為什麼 ？Uber 的數據工程師最近（2016 年 7 月 26 日）發現了這一事實。

請注意下圖（由 Uber Engineering 提供）

PostgreSQL 不會將 aPRIMARY KEY與非唯一索引緊密結合。由於索引通過 引用一行ctid，因此一個簡單的UPDATE（即使在非索引列上）將更改ctid，導致需要重寫ctid表中引用該更改行的每個索引。這不是什麼新鮮事。PostgreSQL 在設計上總是這樣做的。

因此，執行UPDATE1000 行將執行 1000DELETEs和 1000 INSERTs。如前所述，附加到表的每個索引都必須將它們的新行ctid值寫入 BTREE 索引條目，替換舊行ctid值。由於表上沒有索引，因此您只是遇到DELETEs並INSERTs生成了您看到的死鎖。

補充資訊

有人曾經發布過關於這個問題以及如何解決它的討論。其他人利用其 WHERE 子句可用的系統標識符的公開：

• Jun 04, 2002: ctid & 更新（或快速更新/刪除）
• Jul 05, 2011:刪除 PostgreSQL 中完全重複的行（只保留 1 行）
• Nov 26, 2012:使用 ctid 列檢測表重寫
• May 27, 2014:如何將 ctid 分解為頁碼和行號？

預設事務隔離級別是READ COMMITTED. 移動索引將在此隔離級別下提供更好的數據一致性，但其代價是不斷重寫ctid索引中的值以及類似 MongoDB 的刪除和插入全新行的行為，只是為了一個簡單的UPDATE. 雖然其他事務隔離級別是可能的（REPEATABLE READ, READ UNCOMMITTED, SERIALIZABLE），但它可能需要一些針對 ctid 和 oid 值的專門 SQL 來維護任何給定事務中所需的數據視圖。這種專門的查詢可能有幫助，SELECTs但相當不可信UPDATEs，DELETEs因為還有一個VACUUM守護程序正在執行，並且可能ctid過時將是巨大的。在這種情況下，明智的做法是通過oid而不是引用查詢中的行citd。

關於INNODB ???

InnoDB 不會像 PostgreSQL 那樣跳過循環來執行 UPDATE。InnoDB 不會強迫您編寫專門的 SQL 命令來利用 rowid 類型資訊來充實 UPDATE。

InnoDB 的預設隔離級別是REPEATABLE READ. 這比READ COMMITTEDInnoDB rowids 永遠不會改變的事實更容易維護。

為了讓 InnoDB 具有同樣的不良行為，您必須執行REPLACE 命令（這是一個機械的 DELETE 和 INSERT）。PostgreSQL 自動執行此操作，您對此無能為力（除非您有足夠的勇氣獲取原始碼並修復或改進該UPDATE過程）。

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