遮天,琅琊榜海宴小说

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

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就是活動(dòng)狀態(tài)，query就是正在執(zhí)行的sql語句，query——start就是開始執(zhí)行的時(shí)間。

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ù)時(shí)，首先是要獲得表上的鎖，然后再獲得行上的鎖

postgresql中有8種表鎖

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

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

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

表級鎖模式

表級鎖模式	解釋
ACCESS SHARE	只與“ACCESS EXCLUSIVE” 鎖模式?jīng)_突;
	查詢命令（Select command）將會在它查詢的表上獲取”Access Shared” 鎖,一般地，任何一個(gè)對表上的只讀查詢操作都將獲取這種類型的鎖。
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 命令不會自動(dòng)獲得這種鎖。
EXCLUSIVE	與” ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE”模式?jīng)_突,這種索模式僅能與Access Share 模式并發(fā),換句話說，只有讀操作可以和持有”EXCLUSIVE”鎖的事務(wù)并行；
	任何Postgresql 命令不會自動(dòng)獲得這種類型的鎖；
ACCESS EXCLUSIVE	與所有模式鎖沖突(ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE),這種模式保證了當(dāng)前只有一個(gè)事務(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命令不會自動(dòng)獲得這種鎖
EXCLUSIVE	任何Postgresql命令不會自動(dòng)獲得這種類型的鎖
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。

nowait

當(dāng)事務(wù)要更新表中的數(shù)據(jù)時(shí)，應(yīng)該申請“ROW EXCLUSIVER”

行級鎖模式

只有兩種，共享鎖和排他鎖，或者可以說是“讀鎖” 或 “寫鎖“

由于多版本的實(shí)現(xiàn)，實(shí)際讀取行數(shù)據(jù)時(shí)，并不會在行上執(zhí)行任何鎖

行級鎖命令（顯式加行鎖）

SELECT …… FOR { UPDATE | SHARE } [OF table_name[,……]] [ NOWAIT]

備注：

1）指定 OF table_name，則只有被指定的表會被鎖定

2）例外情況，主查詢中引用了WITH查詢時(shí)，WITH查詢中的表不被鎖定

3）如果需要鎖定WITH查詢中的表，需在WITH查詢內(nèi)指定FOR UPDATA或FOR SHARE

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

您可能感興趣的文章:

PostgreSQL 實(shí)現(xiàn)登錄及修改密碼操作
postgresql表死鎖問題的排查方式
Postgresql - 查看鎖表信息的實(shí)現(xiàn)
基于postgresql數(shù)據(jù)庫鎖表問題的解決
基于postgresql行級鎖for update測試
Postgresql鎖機(jī)制詳解(表鎖和行鎖)
PostgreSQL中關(guān)閉死鎖進(jìn)程的方法
PostgreSQL用戶登錄失敗自動(dòng)鎖定的處理方案

標(biāo)簽：蚌埠珠海烏海株洲衡陽錦州晉城來賓

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《postgresql查詢鎖表以及解除鎖表操作》，本文關(guān)鍵詞 postgresql,查詢,鎖表,以及,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。