快捷導(dǎo)航

postgresql查詢鎖表以及解除鎖表操作

更新時間：2020年12月25日 10:08:57 作者：會飛的魚3YH

這篇文章主要介紹了postgresql查詢鎖表以及解除鎖表操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

1.-- 查詢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ù)庫用戶名';

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

2.-- 查詢死鎖的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ù)庫用戶名'
  and T.WAIT_EVENT_TYPE = 'Lock';

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

-- 通過pid解鎖對應(yīng)的ACTIVITY

select PG_CANCEL_BACKEND('6984');

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

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

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

postgresql中有8種表鎖

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

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

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

表級鎖模式

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

表鎖的沖突關(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

表鎖類型對應(yīng)的數(shù)據(jù)庫操作

鎖類型	對應(yīng)的數(shù)據(jù)庫操作
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命令不會自動獲得這種鎖
EXCLUSIVE	任何Postgresql命令不會自動獲得這種類型的鎖
ACCESS EXCLUSIVE	alter table,drop table,truncate,reindex,cluster,vacuum full

表級鎖命令（顯式在表上加鎖的命令）

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)有表的鎖名稱(可選模式限定)。如果在表名之前指定了ONLY，則僅該表被鎖定如果未指定ONLY，則表及其所有后代表(如果有)被鎖定。

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