表鎖

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關鍵字則如果獲取不到鎖，將不會等待而是直接返回，放棄執(zhí)行當前指令并拋出一個錯誤(error)。一旦獲取到鎖，將一直持有鎖直到事務結束。(沒有主動釋放鎖的命令，鎖總是會在事務結束的時候被釋放)。

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

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

如果一個事務想要修改表中的數(shù)據，應該使用SHARE ROW EXCLUSIVE(共享行排他)鎖而不是SHARE鎖。共享行排他鎖將能夠保證在同一時間只有當前事務能夠運行。不加這個鎖的話可能會造成死鎖：兩個事務同時想要獲取SHARE鎖，并且接下來又想要同時獲取ROW EXCLUSIVE鎖去進行數(shù)據更新(注意：同一個事務獲取的兩種不同的鎖不會造成沖突，所以對于同一個事務，它可以在獲取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會在目標表上獲取該鎖，總的來說，任何對數(shù)據庫數(shù)據進行修改的命令會獲取到該鎖。

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

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

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

SHARE（共享鎖）

和ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE ROW EXCLUSIVE，EXCLUSIVE和ACCESS EXCLUSIVE鎖沖突。該鎖保護一個表防止并發(fā)的數(shù)據改變。

由CREATE INDEX命令獲得。

SHARE ROW EXCLUSIVE（行共享排他鎖）

和ROW EXCLUSIVE，SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE以及ACCESS EXCLUSIVE鎖沖突，該鎖用于保護一個表防止并發(fā)的數(shù)據改變，同時是自排他的，所以在同一時間只有同一個session可以持有該鎖。

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

EXCLUSIVE（排它鎖）

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

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

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

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表現(xiàn)類似，但是對加鎖的要求更加寬松，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死鎖的處理

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

查了些資料才發(fā)現(xiàn)問題的原因，總結如下：

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

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

SELECT * FROM pg_stat_activity WHERE datname='死鎖的數(shù)據庫ID ';

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

2.將進程殺掉。

SELECT pg_cancel_backend('死鎖那條數(shù)據的procpid值 ');

結果：運行完后，再次更新這個表，sql順利執(zhí)行。

如果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()函數(shù)也可以殺掉進程。

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

最新評論