Postgresql鎖機制詳解(表鎖和行鎖)

表鎖

LOCK [ TABLE ] [ ONLY ] name [ * ] [, …] [ IN lockmode MODE ] [ NOWAIT ]

lockmode包括以下幾種：

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

LOCK TABLE命令用於獲取一個表鎖，獲取過程將阻塞一直到等待的鎖被其他事務釋放。如果使用NOWAIT關鍵字則如果獲取不到鎖，將不會等待而是直接返回，放棄執行當前指令並拋出一個錯誤(error)。一旦獲取到鎖，將一直持有鎖直到事務結束。(沒有主動釋放鎖的命令，鎖總是會在事務結束的時候被釋放)。

當使用自動獲取鎖的模式的時候，PostgreSQL總是盡可能地使用限制最小的模式。LOCK TABLE命令使我們可以自己定義鎖的限制大小。比如一個應用程序使用事務在讀提交(Read Committed isolation level)模式下需要保證數據庫的數據在事務期間保持穩定，於是可以使用SHARE鎖模式在讀取前對表進行加鎖。這可以防止並發的數據改變並且可以保證後續的事務對這個表的讀取不會讀到沒有提交的數據，因為SHARE鎖和由寫入事務持有的ROW EXCLUSIVE鎖是沖突的，所以對於想要使用SHARE鎖對表進行加鎖的事務，將會等到它之前所有持有該表的ROW EXCLUSIVE鎖的事務commit或者是roll back。因此，一旦獲取瞭表的SHARE鎖，將不會有沒有提交的數據，同樣也不會有其他事務能夠對表數據進行改變，直到當前事務釋放SHARE鎖。

為瞭在REPEATABLE READ(重復讀)模式和SERIALIZABLE(序列化)模式下實現同樣的效果，必須在任何查詢和修改語句之前加上LOCK TABLE。在執行第一句SELECT語句或者修改數據語句前，重復讀和序列化模式中一個事務的的數據視圖將會被存儲為快照。在這種情況下，事務申明的表鎖同樣可以避免並發的修改，但是並不能保證該事務能夠讀取到最新提交的數據。

如果一個事務想要修改表中的數據，應該使用SHARE ROW EXCLUSIVE(共享行排他)鎖而不是SHARE鎖。共享行排他鎖將能夠保證在同一時間隻有當前事務能夠運行。不加這個鎖的話可能會造成死鎖：兩個事務同時想要獲取SHARE鎖，並且接下來又想要同時獲取ROW EXCLUSIVE鎖去進行數據更新(註意：同一個事務獲取的兩種不同的鎖不會造成沖突，所以對於同一個事務，它可以在獲取SHARE鎖之後再次獲取ROW EXCLUSIVE，當然是在沒有其他事務獲取SHARE鎖的情況下)。為瞭避免死鎖，應該保證所有的事務獲取同一對象的鎖的順序是一致的，同時如果在同一個對象上想要獲取多個鎖，則總是應該先獲取限制最大的鎖。

ACCESS SHARE（訪問共享鎖）

隻與ACCESS EXCLUSIVE鎖沖突。

SELECT命令會在當前查詢的表上獲取一個ACCESS SHARE鎖。總的來說，任何隻讀操作都會獲取該鎖。

ROW SHARE（行共享鎖）

和EXCLUSIVE鎖和ACCESS EXCLUSIVE鎖沖突。

SELECT FOR UPDATE或者SELECT FOR SHARE命令會在目標表上獲取該鎖，並且所有被引用但是沒有FOR UPDATE的表上會加上ACCESS SHARED鎖。

ROW EXCLUSIVE（行排他鎖）

和SHARE，SHARE ROW EXCLUSIVE和ACCESS EXCLUSIVE鎖沖突。

UPDATE，DELETE和INSERT會在目標表上獲取該鎖，總的來說，任何對數據庫數據進行修改的命令會獲取到該鎖。

SHARE UPDATE EXCLUSIVE（共享更新排他鎖）

和SHARE UPDATE EXCLUSIVE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE沖突，該鎖可以保護表防止並發的(schema)改變和VACUUM(釋放空間)命令。

VACUUM，ANALYZE，CREATE INDEX CONCURRENTLY和ALTER TABLE VALIDATE以及其他ALTER TABLE類的命令會獲取該鎖。

SHARE（共享鎖）

和ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE鎖沖突。該鎖保護一個表防止並發的數據改變。

由CREATE INDEX命令獲得。

SHARE ROW EXCLUSIVE（行共享排他鎖）

和ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE以及ACCESS EXCLUSIVE鎖沖突，該鎖用於保護一個表防止並發的數據改變，同時是自排他的，所以在同一時間隻有同一個session可以持有該鎖。

該鎖不會被PGSQL的任何命令自動獲取。

EXCLUSIVE（排它鎖）

和ROW SHARE，ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE鎖沖突。該鎖隻允許並發的ACCESS SHARE鎖，隻有隻讀操作能在一個事務持有排他鎖的時候進行並發操作。

ACCESS EXCLUSIVE（訪問排他鎖）

和所有的鎖都沖突，該鎖保證隻有持有鎖的事務能夠訪問當前表。

被DROP TABLE，TRUNCATE，REINDEX，CLUSTER，VACUUM FULL和REFRESH MATERIALIZED VIEW命令自動獲取。有很多種形式的ALTER TABLE命令可以獲取該鎖，它同樣也是LOCK TABLE命令默認的鎖級別。

隻有ACCESS EXCLUSIVE鎖可以防止一個SELECT語句。

註意

一段獲取鎖，隻有當事務結束的時候才會釋放，但是如果一個鎖是在一個savepoint(保存點)之後被獲取，則當這個保存點回滾的時候這個鎖會被馬上釋放。

行鎖

除瞭表鎖，PgSQL還提供瞭行鎖。一個事務可以獲取相互沖突的兩種行鎖，包括在子事務中，但是兩個事務不能同時在同一行獲取相互沖突的兩種鎖。

FOR UPDATE

FOR UPDATE鎖使得SELECT語句可以獲取行鎖用於更新數據。這使得該行可以防止被其他的事務獲取鎖或者進行更改刪除操作，也就是說其他事務的操作會被阻塞直到當前事務結束；同樣的，SELECT FOR UPDATE命令會等待直到前一個事務結束。在REPEATABLE模式或者SERIALIZABLE模式下，如果一個將要被上鎖的行在事務開始之前被刪除瞭，則會返回一個error。

FOR UPDATE鎖同樣可以被DELETE命令獲取，以及UPDATE命令當使用在確定的行用來修改數據的時候也會獲取到該鎖。目前當使用確定的唯一索引時使用UPDATE命令可以獲取到該鎖（部分索引和聯合索引暫時不支持），但是未來可能會改變這種設計。

FOR NO KEY UPDATE

和FOR UPDATE命令類似，但是對於獲取鎖的要求更加寬松一些，在同一行中不會阻塞SELECT FOR KEY SHARE命令。同樣在UPDATE命令的時候如果沒有獲取到FOR UPDATE鎖的情況下會獲取到該鎖。

FOR SHARE

和FOR NO KEY UPDATE命令類似，不同點在於這個鎖是一個共享鎖而不是之前的鎖一樣是排他鎖，所以這個鎖會阻塞UPDATE，DELETE，SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE，但是不會阻塞SELECT FOR SHARE或者SELECT FOR KEY SHARE。

FOR KEY SHARE

和FOR SHARE表現類似，但是對加鎖的要求更加寬松，SELECT FOR UPDATE會被阻塞但是SELECT FOR NO KEY UPDATE不會被阻塞。KEY SHARE模式下的鎖會阻塞其他事務的DELETE或者是改變KEY值的UPDATE語句，但是對於其他的UPDATE或者是SELECT FOR NO KEY UPDATE，SELECT FOR SHARE以及SELECT FOR KEY SHARE則不會阻塞。

補充：Postgresql死鎖的處理

今天遇到一個奇怪的現象，select和delete表時正常執行，但truncate和drop表時會一直運行，也不報錯。

查瞭些資料才發現問題的原因，總結如下：

“drop table ” 和 “truncate table ” 需要申請排它鎖 “ACCESS EXCLUSIVE “， 執行這個命令卡住時，說明此時這張表上還有操作正在進行，比如查詢等，那麼隻有等待這個查詢操作完成，”drop table” 或”truncate table”或者增加字段的SQL 才能獲取這張表上的 “ACCESS EXCLUSIVE” 鎖 ，操作才能進行下去。

1.檢索出死鎖進程的ID。

SELECT * FROM pg_stat_activity WHERE datname=’死鎖的數據庫ID ‘;

檢索出來的字段中，【wating 】字段，數據為t的那條，就是死鎖的進程。找到對應的【procpid 】列的值。

2.將進程殺掉。

SELECT pg_cancel_backend(‘死鎖那條數據的procpid值 ‘);

結果：運行完後，再次更新這個表，sql順利執行。

如果pg_stat_activity 沒有記錄，則查詢pg_locks是否有這個對象的鎖

select oid,relname from pg_class where relname='table name';
select locktype,pid,relation,mode,granted,* from pg_locks where relation= '上面查詢出來的oid';
 
select pg_cancel_backend('進程ID');

另外pg_terminate_backend()函數也可以殺掉進程。

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：

• postgresql表死鎖問題的排查方式
• mysql中drop、truncate與delete的區別詳析
• MYSQL中Truncate的用法詳解
• MySQL深入詳解delete與Truncate及drop的使用區別
• MySQL數據庫數據刪除操作詳解