表鎖

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命令用于獲取一個表鎖，獲取過程將阻塞一直到等待的鎖被其他事務(wù)釋放。如果使用NOWAIT關(guān)鍵字則如果獲取不到鎖，將不會等待而是直接返回，放棄執(zhí)行當(dāng)前指令并拋出一個錯誤(error)。一旦獲取到鎖，將一直持有鎖直到事務(wù)結(jié)束。(沒有主動釋放鎖的命令，鎖總是會在事務(wù)結(jié)束的時候被釋放)。

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

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

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

ACCESS SHARE（訪問共享鎖）

只與ACCESS EXCLUSIVE鎖沖突。

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

ROW SHARE（行共享鎖）

和EXCLUSIVE鎖和ACCESS EXCLUSIVE鎖沖突。

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

ROW EXCLUSIVE（行排他鎖）

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

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

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ù)據(jù)改變。

由CREATE INDEX命令獲得。

SHARE ROW EXCLUSIVE（行共享排他鎖）

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

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

EXCLUSIVE（排它鎖）

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

ACCESS EXCLUSIVE（訪問排他鎖）

和所有的鎖都沖突，該鎖保證只有持有鎖的事務(wù)能夠訪問當(dāng)前表。

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

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

注意

一段獲取鎖，只有當(dāng)事務(wù)結(jié)束的時候才會釋放，但是如果一個鎖是在一個savepoint(保存點)之后被獲取，則當(dāng)這個保存點回滾的時候這個鎖會被馬上釋放。

行鎖

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

FOR UPDATE

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

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

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模式下的鎖會阻塞其他事務(wù)的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)問題的原因，總結(jié)如下：

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

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

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

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

2.將進程殺掉。

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

結(jié)果：運行完后，再次更新這個表，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’);