鎖問題

• 髒讀：一個事務可以讀到另一個事務中未提交的資料

• 不可重複讀：在一個事務沒有結束時，另外一個事務也訪問該同一資料集合，並做了 DML 操作。因此，在第一個事務兩次讀資料之間，讀到的資料可能是不一樣的

• 幻讀（丟失更新）：一個事務的更新操作會被另一個事務的更新操作所覆蓋，從而導致資料的不一致。

隔離級別

• READ UNCOMMITTED

• 允許事務檢視其他事務所進行的未提交更改

• 允許“髒”讀、 不可重複讀和虛讀。

• READ COMMITTED

• 允許事務檢視其他事務所進行的已提交更改

• 不允許“髒”讀。 但允許不可重複讀和虛讀。

• 只支援二進位制格式為 ROW 情況下

• REPEATABLE READ

• 確保每個事務的 SELECT 輸出一致

• SERIALIZABLE

• 將一個事務的結果與其他事務完全隔離， 即一個事務所選的行不能由其他事務更改， 直到該事務完成為止。

• 分散式事務常用。

鎖機制

1. 表級鎖模式

   鎖模式	描述
   ACCESS SHARE	SELECT 命令將在所引用的表上加此型別的鎖。通常只讀取表而不修改表的查詢都會請求這種鎖模式
   ROW SHARE	SELECT FOR UPDATE 和 SELECT FOR SHARE 命令會在目標表上加此型別的鎖
   ROW EXCLUSIVE	UPDATE、DELETE、INSERT 命令會自動在所修改的表上請求加這個鎖。通常，修改表中資料的命令都是加這種鎖
   SHARE UPDATE EXCLUSIVE	這個模式保護一個表，在模式改變和執行 VACUUM 併發、ANALYZE/CREATE INDEX CONCURR-ENTLY 命令請求這樣的鎖
   SHARE	這個模式可避免表的併發資料修改。CREATE INDEX （不帶 CONCURRENTLY 選項）語句要求這樣的鎖模式
   SHARE ROW EXCLUSIVE	任何 PostgreSQL 命令都不會自動請求這個鎖模式
   EXCLUSIVE	這個模式只允許併發 ACCESS SHARE 鎖，也就是說，只有對錶的度動作可以和持有這個鎖的事務併發執行。任何 PostgreSQL 命令都不會使用者表上自動請求這個鎖模式。不過，在執行某些操作時，會在某些系統上請求這個鎖。
   ACCESS EXCLUSIVE	這個模式只能保證只有一個人訪問此表。ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL 命令要求這樣的鎖。在 LOCK TABLE 命令中沒有明確宣告需要的鎖模式時，它是預設鎖模式

   

2. 行級鎖模式：只有兩種，即“共享鎖”和“排他鎖”，或者可以說是“讀鎖”或“寫鎖”。而在 PostgreSQL 中不稱其為“讀鎖”的原因是，由於有多版本的實現，所以實際讀取資料時，並不會在行上執行任何鎖。

表級鎖命令

LOCK [ TABLE ] [ ONLY ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ]

• name：表名

• lockmode：八種鎖模式

• NOWAIT：如果沒有 NOWAIT 這個關鍵字時，當無法獲得鎖時，會一直等待，而如果加了 NOWAIT 關鍵字，在無法立即獲取該鎖時，此命令會立即退出並且發出一個錯誤資訊。

行級鎖命令

SELECT ...... FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...]

•  [ OF table_name [, ...] ]：如果明確了表名字，那麼只有這些被指定的表會被鎖定，其他在 SELECT 中使用的表將不會被鎖定。

• 若主查詢中引用了 WITH 查詢時，WITH 查詢中的表並不會被鎖定。如果想鎖定 WITH 查詢內的錶行，需要在 WITH 查詢內指定 FOR UPDATE 或 FOR SHARE 關鍵字。

鎖的檢視

pg_locks 解釋

列名稱	列型別	引用	描述
locktype	text		被鎖定的物件型別
database	oid	pg_database.oid	鎖定物件之資料庫的 OID，如果物件是一個共享物件，不屬於任何一個資料庫，此值為 0，如果物件是“transaction ID”，此值為空
relation	oid	pg_class.oid	如果物件不是表或只是表的一部分，則此值為 null，否則此值是表的 OID
page	integer		表中的頁號，如果物件不是錶行 (tuple) 或表頁 (relation page)，則此值為 null
tuple	smallint		頁內的行號 (tuple)，如果物件不是錶行 (tuple)，則此值為空
virtualxid	text		是一個虛擬事務 ID (ID of a transaction)，如果物件不是虛擬事務，則此值為 null
transactionid	xid		事務 ID (ID of a transaction)，如果物件不是事務 ID，此值則為 null
classid	oid	pg_class.oid	包含該物件的系統目錄 (system catalog) 的 OID，如果物件不是通常的資料庫物件，則此值為空
objid	oid	any OID column	物件在系統目錄(system catalog) 中的 OID，如果物件不是通常的資料庫物件，則此值為空。對於 advisory locks，此欄位用於區別兩類 key 空間（1 表示 int8 的 key，2 表示 two int4 的 key）
objsubid	smallint		如果物件是表列 (table column)，此列的值為列號，這時“classid”和“objid”指向表，在其他的資料庫型別中，此值為 0；如果不是資料庫物件，則此值為 null
virtualtransaction	text		持有或等待這把鎖的虛擬事務的 ID (Virtual ID of the transaction)
pid	integer		持有或等待這把鎖的服務程式的 pid。如果此鎖是被一個兩階段的事務持有，則此值為 null
mode	text		鎖的模式名稱
granted	boolean		如果鎖已被持有，此之為“True”；如果等待獲得此鎖，則此值為“False”

事務 ID 欄位詳解

• transactionid-->xid：普通事務 ID

• virtualxid-->vxid：只讀事務 ID

• virtualtransaction：表示持有鎖或等待鎖 session 的虛擬事務 ID。

常用語句

SELECT locktype,
       relation::regclass as rel,
       page||','||tuple as ctid,
       virtualxid as vxid,
       transactionid as xid,
       virtualtransaction as vxid2,
       pid,
       mode,
       granted
  FROM pg_locks;