亚洲高清三级视频,国产欧美精品,国产成人精品免费视

主頁 > 知識庫 > postgresql查詢鎖表以及解除鎖表操作

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

1.-- 查詢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 = '數據庫用戶名';

上面查詢結果中：pid就是ACTIVITY的唯一標識，state就是活動狀態，query就是正在執行的sql語句，query——start就是開始執行的時間。

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 = '數據庫用戶名'
  and T.WAIT_EVENT_TYPE = 'Lock';

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

-- 通過pid解鎖對應的ACTIVITY

select PG_CANCEL_BACKEND('6984');

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

補充：PostgreSQL 之鎖機制

當要增刪改查表中的數據時，首先是要獲得表上的鎖，然后再獲得行上的鎖

postgresql中有8種表鎖

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

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

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

表級鎖模式

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

表鎖的沖突關系

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

表鎖類型對應的數據庫操作

鎖類型	對應的數據庫操作
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：要鎖定的現有表的鎖名稱(可選模式限定)。如果在表名之前指定了ONLY，則僅該表被鎖定如果未指定ONLY，則表及其所有后代表(如果有)被鎖定。

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