快捷導(dǎo)航

postgresql查詢(xún)鎖表以及解除鎖表操作

更新時(shí)間：2020年12月25日 10:08:57 作者：會(huì)飛的魚(yú)3YH

這篇文章主要介紹了postgresql查詢(xún)鎖表以及解除鎖表操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

1.-- 查詢(xún)ACTIVITY的狀態(tài)等信息

select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT,
  T.QUERY_START
 from PG_STAT_ACTIVITY T
 where T.DATNAME = '數(shù)據(jù)庫(kù)用戶(hù)名';

上面查詢(xún)結(jié)果中：pid就是ACTIVITY的唯一標(biāo)識(shí)，state就是活動(dòng)狀態(tài)，query就是正在執(zhí)行的sql語(yǔ)句，query——start就是開(kāi)始執(zhí)行的時(shí)間。

2.-- 查詢(xún)死鎖的ACTIVITY

select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT,
  T.QUERY_START
 from PG_STAT_ACTIVITY T
 where T.DATNAME = '數(shù)據(jù)庫(kù)用戶(hù)名'
  and T.WAIT_EVENT_TYPE = 'Lock';

3.將第二條查詢(xún)語(yǔ)句的pid字段的數(shù)字值記錄下來(lái)，執(zhí)行下面的查詢(xún)語(yǔ)句可以解鎖：

-- 通過(guò)pid解鎖對(duì)應(yīng)的ACTIVITY

select PG_CANCEL_BACKEND('6984');

上面的查詢(xún)語(yǔ)句，執(zhí)行了pg_cancel_backend()函數(shù)，該函數(shù)是取消后臺(tái)操作，回滾未提交事物的用途。

補(bǔ)充：PostgreSQL 之鎖機(jī)制

當(dāng)要增刪改查表中的數(shù)據(jù)時(shí)，首先是要獲得表上的鎖，然后再獲得行上的鎖

postgresql中有8種表鎖

最普通的是共享鎖 share 和排他鎖 exclusive

因?yàn)槎喟姹镜脑?，修改一條語(yǔ)句的同時(shí)，允許了讀數(shù)據(jù)，為了處理這種情況，又增加了兩種鎖”access share”和”acess excusive”，鎖中的關(guān)鍵字 access 是與多版本相關(guān)的

為了處理表鎖和行鎖之間的關(guān)系，有了意向鎖的概念，這時(shí)又加了兩種鎖，即意向共享鎖和意向排他鎖，由于意向鎖之間不會(huì)產(chǎn)生沖突，而且意向排它鎖相互之間也不會(huì)產(chǎn)生沖突，于是又需要更嚴(yán)格一些的鎖，這樣就產(chǎn)生了“share update exclusive” 和 ”share row exclusive”

表級(jí)鎖模式

表級(jí)鎖模式	解釋
ACCESS SHARE	只與“ACCESS EXCLUSIVE” 鎖模式?jīng)_突;
	查詢(xún)命令（Select command）將會(huì)在它查詢(xún)的表上獲取”Access Shared” 鎖,一般地，任何一個(gè)對(duì)表上的只讀查詢(xún)操作都將獲取這種類(lèi)型的鎖。
ROW SHARE	與”Exclusive'和”Access Exclusive”鎖模式?jīng)_突;
	”Select for update”和”Select for share”命令將獲得這種類(lèi)型鎖，并且所有被引用但沒(méi)有 FOR UPDATE 的表上會(huì)加上”Access shared locks”鎖。
ROW EXCLUSIVE	與 “Share,Shared roexclusive,Exclusive,Access exclusive”模式?jīng)_突;
	“Update,Delete,Insert”命令會(huì)在目標(biāo)表上獲得這種類(lèi)型的鎖，并且在其它被引用的表上加上”Access shared”鎖,一般地，更改表數(shù)據(jù)的命令都將在這張表上獲得”Row exclusive”鎖。
SHARE UPDATE EXCLUSIVE	”Share update exclusive,Share,Share row ,exclusive,exclusive,Access exclusive”模式?jīng)_突，這種模式保護(hù)一張表不被并發(fā)的模式更改和VACUUM;
	“Vacuum(without full), Analyze ”和 “Create index concurrently”命令會(huì)獲得這種類(lèi)型鎖。
SHARE	與“Row exclusive,Shared update exclusive,Share row exclusive ,Exclusive,Access exclusive”鎖模式?jīng)_突,這種模式保護(hù)一張表數(shù)據(jù)不被并發(fā)的更改;
	“Create index”命令會(huì)獲得這種鎖模式。
SHARE ROW EXCLUSIVE	與“Row exclusive,Share update exclusive,Shared,Shared row exclusive,Exclusive,Access Exclusive”鎖模式?jīng)_突;
	任何Postgresql 命令不會(huì)自動(dòng)獲得這種鎖。
EXCLUSIVE	與” ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE”模式?jīng)_突,這種索模式僅能與Access Share 模式并發(fā),換句話說(shuō)，只有讀操作可以和持有”EXCLUSIVE”鎖的事務(wù)并行；
	任何Postgresql 命令不會(huì)自動(dòng)獲得這種類(lèi)型的鎖；
ACCESS EXCLUSIVE	與所有模式鎖沖突(ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE),這種模式保證了當(dāng)前只有一個(gè)事務(wù)訪問(wèn)這張表;“ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL” 命令會(huì)獲得這種類(lèi)型鎖，在Lock table 命令中，如果沒(méi)有申明其它模式，它也是缺省模式。

表鎖的沖突關(guān)系

Requested Lock Mode	Current Lock Mode
Requested Lock Mode	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE							X	X
ROW SHARE							X	X
ROW EXCLUSIVE					X	X	X	X
SHARE UPDATE EXCLUSIVE				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCLUSIVE			X	X	X	X	X	X
EXCLUSIVE		X	X	X	X	X	X	X
ACCESS EXCLUSIVE	X	X	X	X	X	X	X	X

表鎖類(lèi)型對(duì)應(yīng)的數(shù)據(jù)庫(kù)操作

鎖類(lèi)型	對(duì)應(yīng)的數(shù)據(jù)庫(kù)操作
ACCESS SHARE	select
ROW SHARE	select for update, select for share
ROW EXCLUSIVE	update,delete,insert
SHARE UPDATE EXCLUSIVE	vacuum(without full),analyze,create index concurrently
SHARE	create index
SHARE ROW EXCLUSIVE	任何Postgresql命令不會(huì)自動(dòng)獲得這種鎖
EXCLUSIVE	任何Postgresql命令不會(huì)自動(dòng)獲得這種類(lèi)型的鎖
ACCESS EXCLUSIVE	alter table,drop table,truncate,reindex,cluster,vacuum full

表級(jí)鎖命令（顯式在表上加鎖的命令）

testdb=# \h lock
Command:  LOCK
Description: lock a table
 
Syntax:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
 
where lockmode is one of:
 ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
 | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

注：

name：要鎖定的現(xiàn)有表的鎖名稱(chēng)(可選模式限定)。如果在表名之前指定了ONLY，則僅該表被鎖定如果未指定ONLY，則表及其所有后代表(如果有)被鎖定。

lock_mode：鎖模式指定此鎖與之沖突的鎖。如果未指定鎖定模式，則使用最嚴(yán)格的訪問(wèn)模式ACCESS EXCLUSIVE。