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

表鎖

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

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

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

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

ACCESS SHARE（訪問(wèn)共享鎖）

只與ACCESS EXCLUSIVE鎖沖突。

SELECT命令會(huì)在當(dāng)前查詢的表上獲取一個(gè)ACCESS SHARE鎖?？偟膩?lái)說(shuō)，任何只讀操作都會(huì)獲取該鎖。

ROW SHARE（行共享鎖）

和EXCLUSIVE鎖和ACCESS EXCLUSIVE鎖沖突。

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

ROW EXCLUSIVE（行排他鎖）

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

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

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

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

VACUUM，ANALYZE，CREATE INDEX CONCURRENTLY和ALTER TABLE VALIDATE以及其他ALTER TABLE類(lèi)的命令會(huì)獲取該鎖。

SHARE（共享鎖）

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

由CREATE INDEX命令獲得。

SHARE ROW EXCLUSIVE（行共享排他鎖）

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

該鎖不會(huì)被PGSQL的任何命令自動(dòng)獲取。

EXCLUSIVE（排它鎖）

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

ACCESS EXCLUSIVE（訪問(wèn)排他鎖）

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

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

只有ACCESS EXCLUSIVE鎖可以防止一個(gè)SELECT語(yǔ)句。

注意

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

行鎖

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

FOR UPDATE

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

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

FOR NO KEY UPDATE

和FOR UPDATE命令類(lèi)似，但是對(duì)于獲取鎖的要求更加寬松一些，在同一行中不會(huì)阻塞SELECT FOR KEY SHARE命令。同樣在UPDATE命令的時(shí)候如果沒(méi)有獲取到FOR UPDATE鎖的情況下會(huì)獲取到該鎖。

FOR SHARE

和FOR NO KEY UPDATE命令類(lèi)似，不同點(diǎn)在于這個(gè)鎖是一個(gè)共享鎖而不是之前的鎖一樣是排他鎖，所以這個(gè)鎖會(huì)阻塞UPDATE，DELETE，SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE，但是不會(huì)阻塞SELECT FOR SHARE或者SELECT FOR KEY SHARE。

FOR KEY SHARE

和FOR SHARE表現(xiàn)類(lèi)似，但是對(duì)加鎖的要求更加寬松，SELECT FOR UPDATE會(huì)被阻塞但是SELECT FOR NO KEY UPDATE不會(huì)被阻塞。KEY SHARE模式下的鎖會(huì)阻塞其他事務(wù)的DELETE或者是改變KEY值的UPDATE語(yǔ)句，但是對(duì)于其他的UPDATE或者是SELECT FOR NO KEY UPDATE，SELECT FOR SHARE以及SELECT FOR KEY SHARE則不會(huì)阻塞。

補(bǔ)充：Postgresql死鎖的處理

今天遇到一個(gè)奇怪的現(xiàn)象，select和delete表時(shí)正常執(zhí)行，但truncate和drop表時(shí)會(huì)一直運(yùn)行，也不報(bào)錯(cuò)。

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

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

1.檢索出死鎖進(jìn)程的ID。

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

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

2.將進(jìn)程殺掉。

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

結(jié)果：運(yùn)行完后，再次更新這個(gè)表，sql順利執(zhí)行。

如果pg_stat_activity 沒(méi)有記錄，則查詢pg_locks是否有這個(gè)對(duì)象的鎖

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

另外pg_terminate_backend()函數(shù)也可以殺掉進(jìn)程。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。