PG锁

最新推荐文章于 2024-06-03 09:57:27 发布

三思呐三思

最新推荐文章于 2024-06-03 09:57:27 发布

阅读量1.7k

点赞数 2

分类专栏： PG 文章标签： postgres postgres锁

本文链接：https://blog.csdn.net/weixin_37692493/article/details/109174717

版权

PG 专栏收录该内容

24 篇文章 12 订阅

订阅专栏

文章目录

一、表级锁
- 1.1 表级锁模式
- 1.2 表级锁兼容性
二、行级锁
三、页面级锁
四、死锁
五、咨询锁
六、锁的维护
- 6.1 锁相关参数
- 6.2 锁监控

一、表级锁

1.1 表级锁模式

常见锁模式以及应用场景：

ACCESS SHARE ：select操作获取该模式锁资源，通常情况下所有只读取不修改表的查询都会获取该模式锁资源
ROW SHARE ： select for update 和 select for share 命令获取该模式锁资源
ROW EXCLUSIVE ： DML操作通过会获取该模式锁资源，通常情况下任何需要修改表数据的操作都会持有该模式锁资源
SHARE UPDATE EXCLUSIVE ：对于 vacuum(非full)、create index concurrnetly、reindex concurrently、create statistics、alter index相关、alter table相关操作会持有该模式锁资源，该模式锁资源主要是为了范围并发对同一张表DDL变更以及vacuum操作
SHARE ： create index (非concurrently)操作会持有该模式锁资源，该模式锁资源可阻止并发对表的创建索引操作
SHARE ROW EXCLUSIVE ： cerate trigger、一些alter table操作会持有该模式锁资源
EXCLUSIVE ： refresh materialized view concurrently操作会持有该模式锁资源
ACCESS EXCLUSIVE ：lock table模式锁定模式，drop table、truncate、reindex、 cluster、vacuum full、refresh materialized view (without concurrently) 还有一些alter table、alter index操作需要获取该模式锁资源，该模式锁资源保证了持有者唯一访问表。

1.2 表级锁兼容性

锁模式	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCLUSIVE	SHARE	SHARE ROW EXCLUSIVE	EXCLUSIVE	ACCESS EXCLUSIVE
ACCESS SHARE								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

二、行级锁

2.1 行级锁模式

常见锁模式以及应用场景：

FOR UPDATE ：对于所有的for update操作，对于被检索的数据行进行for update行锁锁定，阻止其他事务对持有for update行锁记录进行更新。在RR和SERIALIZABLE隔离级别下，如果一个被for update锁定的行在当前事务开始后被修改，该事务会抛出异常报错。对于update、delete操作同样需要获取for update行模式锁
FOR NO KEY UPDATE ：与for update行模式锁类似，但是其锁范围相对较弱。对于不需要获取 for update 行锁资源的所有delete、update操作都会持有该行模式锁。在RR和SERIALIZABLE隔离级别下，如果一个被for update锁定的行在当前事务开始后被修改，该事务会抛出异常报错。
FOR SHARE ：对于检索记录添加share lock，该模式锁资源会阻塞其他事务对持有锁记录进行delete、update、select for update、for no key update，但允许其他事务并发添加for share或者for key share
FOR KEY SHARE ：相对于for share，该模式锁相对更加弱一些,他允许其他事务并发持有for no key update模式锁资源

2.2 行级锁兼容性

锁模式	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

2.3 锁测试

1、RR隔离级别下 for update 测试

事务一	事务二
-	START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-	select id from t1 where id2=327;//id=2,6240
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;	-
-	update t1 set info =‘aaaaaaaaa’ where id=2;
select * from t1 where id2=327 for update;//被夯住	-
-	commit;
select for update抛出异常报错“ERROR: could not serialize access due to concurrent update”

for no key update操作也会出现相同的报错提示。

三、页面级锁

page-level share/exclusive ：控制对shared buffer pool中数据页的读写操作，在获取对应需要查询或者更新的行记录后会立即释放页面级别锁。应用开发者通常不需要关心页级锁。

四、死锁

PostgreSQL自动检测死锁情况并会自动回滚其中一个事务进行处理，从而其他事务完成。

db1=# select * from t1 where id in (1,2,3);
 id | id2  |               info               |          crt_time
----+------+----------------------------------+----------------------------
  2 |  327 | aaaaaaaaa                        | 2020-10-12 16:20:50.278564
  3 | 6098 | 8dc6d07062720d35f7824dceea5a3ef9 | 2020-10-12 17:20:50.278572
(2 rows)

事务一	事务二
begin;	begin;
update t1 set info=‘bbbbbbbbb’ where id=3;	update t1 set info=‘bbbbbbbbb’ where id=2;
-	update t1 set info=‘cccccccc’ where id=3;//被夯住
update t1 set info=‘cccccccc’ where id=2; //返回报错	update执行成功
commit //自动rollback	commit;

## 事务一update报错内容
ERROR:  deadlock detected
DETAIL:  Process 26174 waits for ShareLock on transaction 1738606; blocked by process 26178.
Process 26178 waits for ShareLock on transaction 1738607; blocked by process 26174.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (51,123) in relation "t1"

## 事务二提交后数据变更结果
db1=# select * from t1 where id in (1,2,3);
 id | id2  |   info    |          crt_time
----+------+-----------+----------------------------
  2 |  327 | bbbbbbbbb | 2020-10-12 16:20:50.278564
  3 | 6098 | cccccccc  | 2020-10-12 17:20:50.278572
(2 rows)

## server log详细内容报错
2020-10-15 16:53:03.688 CST [26174] ERROR:  deadlock detected
2020-10-15 16:53:03.688 CST [26174] DETAIL:  Process 26174 waits for ShareLock on transaction 1738606; blocked by process 26178.
	Process 26178 waits for ShareLock on transaction 1738607; blocked by process 26174.
	Process 26174: update t1 set info='cccccccc' where id=2;
	Process 26178: update t1 set info='cccccccc' where id=3;
2020-10-15 16:53:03.688 CST [26174] HINT:  See server log for query details.
2020-10-15 16:53:03.688 CST [26174] CONTEXT:  while updating tuple (51,123) in relation "t1"
2020-10-15 16:53:03.688 CST [26174] STATEMENT:  update t1 set info='cccccccc' where id=2;

五、咨询锁

5.1 什么是咨询锁

咨询锁是一种具有应用层面的锁，数据库中一般不会主动调用相关咨询锁资源，需要开发同学在应用层决定是否需要使用这类锁。咨询锁使用的模型是悲观锁模型，相对于传统意义上的锁，咨询锁更快，可以避免表膨胀，并在会话结束时由服务器自动清除。

咨询锁可分为会话/事务级别：

会话级别的锁需要显式释放，随着连接的关闭自动释放
事务级别的锁也需要显式释放，或者会随着事务的结束（提交或者回滚）一并释放

锁级别：

_lock ：会话级别锁
_xact_lock ：事务级别锁

排它锁/共享锁的获取以及释放：

_lock() : 获取指定参数下的会话/事务级别排它锁资源
_lock_shared() : 获取指定参数下的会话/事务级别共享锁资源
_unlock() ：释放指定参数下的会话级别排它锁资源
_unlock_shared() ：释放指定参数下的会话级别共享锁资源
_unlock_all() ：释放当前会话下持有的所有会话级别锁资源

锁获取模式：

pg_try_advisory ：若无法获取资源则一直等待
pg_advisory ：若无法获取资源，直接返回false，不会一直等待

5.2 咨询锁相关函数

函数名称	返回类型	描述
pg_advisory_lock(key bigint)	void	session级别排他锁
pg_advisory_lock(key1 int, key2 int)	void	session级别排他锁
pg_advisory_lock_shared(key bigint)	void	session级别共享锁
pg_advisory_lock_shared(key1 int, key2 int)	void	session级别共享锁
pg_advisory_unlock(key bigint)	boolean	释放session级别排他锁
pg_advisory_unlock(key1 int, key2 int)	boolean	释放session级别排他锁
pg_advisory_unlock_all()	void	释放本session持有的所有session级别的锁
pg_advisory_unlock_shared(key bigint)	boolean	释放session级别共享锁
pg_advisory_unlock_shared(key1 int, key2 int)	boolean	释放session级别共享锁
pg_advisory_xact_lock(key bigint)	void	获取事务级别排他锁
pg_advisory_xact_lock(key1 int, key2 int)	void	获取事务级别排他锁
pg_advisory_xact_lock_shared(key bigint)	void	获取事务级别共享锁
pg_advisory_xact_lock_shared(key1 int, key2 int)	void	获取事务级别共享锁
pg_try_advisory_lock(key bigint)	boolean	试图获取session级别排他锁，成功返回true，否则返回false
pg_try_advisory_lock(key1 int, key2 int)	boolean	试图获取session级别排他锁，成功返回true，否则返回false
pg_try_advisory_lock_shared(key bigint)	boolean	试图获取session级别共享锁，成功返回true，否则返回false
pg_try_advisory_lock_shared(key1 int, key2 int)	boolean	试图获取session级别共享锁，成功返回true，否则返回false
pg_try_advisory_xact_lock(key bigint)	boolean	试图获取事务级别排他锁，成功返回true，否则返回false
pg_try_advisory_xact_lock(key1 int, key2 int)	boolean	试图获取事务级别排他锁，成功返回true，否则返回false
pg_try_advisory_xact_lock_shared(key bigint)	boolean	试图获取事务级别共享锁，成功返回true，否则返回false
pg_try_advisory_xact_lock_shared(key1 int, key2 int)	boolean	试图获取事务级别共享锁，成功返回true，否则返回false

锁分为64位和32位，bigint表示64位、int表示32位。对于同一行记录，若分为使用64位、32位资源锁进行加锁，是不会互相干预的，他们是不同空间上的锁。

5.3 资源锁测试

1、库粒度的资源锁

## 会话一 请求对t2表的id=2进行添加锁，加锁成功。由于pg_try_advisory_lock指定参数为id，其实时在数据库级别添加了锁资源
db1=# select pg_try_advisory_lock(id),id from t2 where id=2;
 pg_try_advisory_lock | id
----------------------+----
 t                    |  2
(1 row)

## 会话二 请求对t1表的id=2进行加锁，加锁失败
db1=# select pg_try_advisory_lock(id),id from t1 where id=2;
 pg_try_advisory_lock | id
----------------------+----
 f                    |  2
(1 row)

## 会话一 释放已申请锁资源
db1=# select pg_advisory_unlock(id),id from t2 where id = 2;
 pg_advisory_unlock | id
--------------------+----
 t                  |  2
(1 row)

## 会话二 再次尝试申请t1表id=2的锁资源，加锁成功
db1=# select pg_try_advisory_lock(id),id from t1 where id=2;
 pg_try_advisory_lock | id
----------------------+----
 t                    |  2
(1 row)

2、行粒度的资源锁

## 会话一尝试对t1表的id=2记录获取资源锁，获取成功
db1=# select pg_try_advisory_lock(cast('t1'::regclass::oid as int),id),id from t1 where id=2;
 pg_try_advisory_lock | id
----------------------+----
 t                    |  2
(1 row)

## 会话二尝试对t1表的id=2记录获取资源锁，获取失败
db1=# select pg_try_advisory_lock(cast('t1'::regclass::oid as int),id),id from t1 where id=2;
 pg_try_advisory_lock | id
----------------------+----
 f                    |  2
(1 row)

## 会话二尝试对t1表的id=2记录获取资源锁，获取成功
db1=# select pg_try_advisory_lock(cast('t1'::regclass::oid as int),id),id from t1 where id=3;
 pg_try_advisory_lock | id
----------------------+----
 t                    |  3
(1 row)

六、锁的维护

6.1 锁相关参数

deadlock_timeout（integer）：默认1s，表示pg数据库仅对锁超时大于1s的情况进行死锁检测。
log_lock_waits : 默认关闭，若打开该参数则表示会将锁超时超过deadlock_timeout的信息记录到日志中。
lock_timeout : 锁等待超时，默认为0表示禁用锁超时。若该参数设置>0则表示会话等待锁资源Ns后如果仍无法获取到相关锁资源则终止相关语句执行。
idle_in_transaction_session_timeout : 对于一个空闲的事务，超过多少时间后自动终止，单位为毫秒。默认为0表示禁用该参数。会话终止会释放该会话所有锁资源。

锁相关参数：https://www.postgresql.org/docs/12/runtime-config-locks.html

6.2 锁监控

1、PG中锁相关的两个重要的视图

2、查看谁锁了谁

当一个进程处于等待（被堵塞）状态时，是谁干的？可以使用如下函数，快速得到捣蛋（堵塞别人）的PID。

1）请求锁时被堵，是哪些PID堵的?

pg_blocking_pids(int) int[] Process ID(s) that are blocking specified server process ID from acquiring a lock

2）请求safe快照时被堵(SSI隔离级别，请求安全快照冲突)，是哪些PID堵的?

pg_safe_snapshot_blocking_pids(int) int[] Process ID(s) that are blocking specified server process ID from acquiring a safe snapshot

示例：

select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;

- pid : 当前会话，被阻塞者
- pg_blocking_pids(pid) ： 阻塞者
- wait_event_type ： 会话状态，Lock表示被阻塞无法获取锁资源
- wait_event ： 等待事件
- query ： 会话执行相关查询

事务一	事务二	事务三
begin;	begin	-
update t1 set info =‘aaaaaaaaabbbbbbbbb’ where id=2;	-	-
-	update t1 set info =‘aaaaaaaaa’ where id=2;//被夯住	-
-	-	select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;
-	update执行成功, info =‘aaaaaaaaa’	-
commit//执行报错	commit	-

## 事务三查看锁等待
## 可以看到28363会话正在等待27152会话
db1=# select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;
  pid  | pg_blocking_pids | wait_event_type |     wait_event      |                                           query
-------+------------------+-----------------+---------------------+-------------------------------------------------------------------------------------------
  9020 | {}               | Activity        | LogicalLauncherMain |
  9017 | {}               | Activity        | AutoVacuumMain      |
 28363 | {27152}          | Lock            | transactionid       | update t1 set info ='aaaaaaaaa' where id=2;
 27219 | {}               |                 |                     | select pid,pg_blocking_pids(pid),wait_event_type,wait_event,query from pg_stat_activity ;
 27152 | {}               | Client          | ClientRead          | update t1 set info ='aaaaaaaaabbbbbbbbb' where id=2;
  9015 | {}               | Activity        | BgWriterHibernate   |
  9014 | {}               | Activity        | CheckpointerMain    |
  9016 | {}               | Activity        | WalWriterMain       |
(8 rows)


## 事务一commit相关报错
db1=# commit;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

2、通过数据库日志（开启lock_timeout, log_lockwait参数）(csvlog)跟踪锁等待信息

3、通过SQL查看锁冲突具体信息

1）锁冲突查询SQL

with    
t_wait as    
(    
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   
),   
t_run as   
(   
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   
),   
t_overlap as   
(   
  select r.* from t_wait w join t_run r on   
  (   
    r.locktype is not distinct from w.locktype and   
    r.database is not distinct from w.database and   
    r.relation is not distinct from w.relation and   
    r.page is not distinct from w.page and   
    r.tuple is not distinct from w.tuple and   
    r.virtualxid is not distinct from w.virtualxid and   
    r.transactionid is not distinct from w.transactionid and   
    r.classid is not distinct from w.classid and   
    r.objid is not distinct from w.objid and   
    r.objsubid is not distinct from w.objsubid and   
    r.pid <> w.pid   
  )    
),    
t_unionall as    
(    
  select r.* from t_overlap r    
  union all    
  select w.* from t_wait w    
)    
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   
string_agg(   
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||   
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||   
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||    
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||    
'SQL (Current SQL in Transaction): '||chr(10)||  
case when query is null then 'NULL' else query::text end,    
chr(10)||'--------'||chr(10)    
order by    
  (  case mode    
    when 'INVALID' then 0   
    when 'AccessShareLock' then 1   
    when 'RowShareLock' then 2   
    when 'RowExclusiveLock' then 3   
    when 'ShareUpdateExclusiveLock' then 4   
    when 'ShareLock' then 5   
    when 'ShareRowExclusiveLock' then 6   
    when 'ExclusiveLock' then 7   
    when 'AccessExclusiveLock' then 8   
    else 0   
  end  ) desc,   
  (case when granted then 0 else 1 end)  
) as lock_conflict  
from t_unionall   
group by   
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

2）如果觉得写SQL麻烦，可以将它创建为视图，直接查询视图

create view v_locks_monitor as   
with    
t_wait as    
(    
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   
),   
t_run as   
(   
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   
),   
t_overlap as   
(   
  select r.* from t_wait w join t_run r on   
  (   
    r.locktype is not distinct from w.locktype and   
    r.database is not distinct from w.database and   
    r.relation is not distinct from w.relation and   
    r.page is not distinct from w.page and   
    r.tuple is not distinct from w.tuple and   
    r.virtualxid is not distinct from w.virtualxid and   
    r.transactionid is not distinct from w.transactionid and   
    r.classid is not distinct from w.classid and   
    r.objid is not distinct from w.objid and   
    r.objsubid is not distinct from w.objsubid and   
    r.pid <> w.pid   
  )    
),    
t_unionall as    
(    
  select r.* from t_overlap r    
  union all    
  select w.* from t_wait w    
)    
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   
string_agg(   
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||   
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||   
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||    
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||    
'SQL (Current SQL in Transaction): '||chr(10)||  
case when query is null then 'NULL' else query::text end,    
chr(10)||'--------'||chr(10)    
order by    
  (  case mode    
    when 'INVALID' then 0   
    when 'AccessShareLock' then 1   
    when 'RowShareLock' then 2   
    when 'RowExclusiveLock' then 3   
    when 'ShareUpdateExclusiveLock' then 4   
    when 'ShareLock' then 5   
    when 'ShareRowExclusiveLock' then 6   
    when 'ExclusiveLock' then 7   
    when 'AccessExclusiveLock' then 8   
    else 0   
  end  ) desc,   
  (case when granted then 0 else 1 end)  
) as lock_conflict  
from t_unionall   
group by   
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

3）锁冲突视图查询事务查询

-[ RECORD 1 ]-+------------------------------------------------------------------------------------------------------------------------------------------------------
locktype      | transactionid
datname       | db1
relation      |
page          |
tuple         |
virtualxid    |
transactionid | 1738627
classid       |
objid         |
objsubid      |
lock_conflict | Pid: 28363                                                                                                                                           +
              | Lock_Granted: true , Mode: ExclusiveLock , FastPath: false , VirtualTransaction: 3/1372985 , Session_State: idle in transaction                      +
              | Username: postgres , Database: db1 , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql                                                    +
              | Xact_Start: 2020-10-19 22:22:40.71729+08 , Query_Start: 2020-10-19 22:22:53.991278+08 , Xact_Elapse: 00:00:57.879438 , Query_Elapse: 00:00:44.60545  +
              | SQL (Current SQL in Transaction):                                                                                                                    +
              | select pg_backend_pid();                                                                                                                             +
              | --------                                                                                                                                             +
              | Pid: 31024                                                                                                                                           +
              | Lock_Granted: false , Mode: ShareLock , FastPath: false , VirtualTransaction: 6/1244302 , Session_State: active                                      +
              | Username: postgres , Database: db1 , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql                                                    +
              | Xact_Start: 2020-10-19 22:23:02.130503+08 , Query_Start: 2020-10-19 22:23:24.715491+08 , Xact_Elapse: 00:00:36.466225 , Query_Elapse: 00:00:13.881237+
              | SQL (Current SQL in Transaction):                                                                                                                    +
              | update t1 set info ='cccccc' where id=2;

4）通过锁冲突视图查询，已经清晰看到每一个发生了锁等待的对象，按锁的大小排序展示出来。只需要找到terminate最大的锁对应的PID即可。

db1=# select pg_terminate_backend(28363);   //找到terminate最大的PID并进行kill
 pg_terminate_backend
----------------------
 t
(1 row)

db1=# \x 1
Expanded display is on.
db1=# select * from v_locks_monitor ;       //锁冲突恢复
(0 rows)

4、Lock trace，通过审计日志分析事务锁等待情况

1）开启审计日志

log_destination = 'csvlog'  
logging_collector = on  
log_truncate_on_rotation = on  
log_statement = 'all'

2）psql登录到对应数据库中，挂一个打印锁等待的窗口

select * from v_locks_monitor;      //锁冲突查询视图
\watch 0.2                          //查询间隔

3）tail 挂一个日志观测窗口

for ((i=1;i>0;i=1)); do grep RowExclusiveLock *.csv ; sleep 0.2; done  
或  
for ((i=1;i>0;i=1)); do grep acquired *.csv ; sleep 0.2; done

4）通过步骤二发现阻塞会话PID，然后根据PID信息在步骤三中的审计日志查看具体的相关操作，对锁阻塞进行具体分析

>>文档参考<<

咨询锁文档博客：https://www.cnblogs.com/wy123/archive/2020/08/14/13499526.html
官方文档：https://www.postgresql.org/docs/13/explicit-locking.html
PostgreSQL 谁堵塞了谁（锁等待检测）：https://developer.aliyun.com/article/746217

三思呐三思

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
PG锁

文章目录一、表级锁1.1 表级锁模式1.2 表级锁兼容性二、行级锁2.1 行级锁模式2.2 行级锁兼容性2.3 锁测试三、页面级锁四、死锁五、咨询锁5.1 什么是咨询锁5.2 咨询锁相关函数5.3 资源锁测试六、锁的维护6.1 锁相关参数6.2 锁监控一、表级锁1.1 表级锁模式常见锁模式以及应用场景：ACCESS SHARE ：select操作获取该模式锁资源，通常情况下所有只读取不修改表的查询都会获取该模式锁资源ROW SHARE ： select for update 和 select f
复制链接

扫一扫