表级锁
人为创造锁等待状态，观察情况
在会话一 先向某表插入一条语句
test_pg=# insert into t_lock values (2,'abcDefGH');
INSERT 0 1
update一条语句，不结束
test_pg=# begin
test_pg-# ;
BEGIN
test_pg=*# update t_lock set username= 'ghjkl' where id='1';
UPDATE 1
在另一会话执行
test_pg=# alter table t_lock add dept_no int;创建视图，方便后续使用：
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 ;查询此试图，查看锁情况
locktype | datname | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid |
lock_conflict
----------+---------+----------+------+-------+------------+---------------+---------+-------+----------+------------------------------------------------------------
-------------------------------------------------------------------------------------------
relation | test_pg | t_lock | | | | | | | | Pid: 17129
+
| | | | | | | | | | Lock_Granted: false , Mode: AccessExclusiveLock , FastPath:
false , VirtualTransaction: 14/28 , Session_State: active +
| | | | | | | | | | Username: postgres , Database: test_pg , Client_Addr: 192.1
68.80.100/32 , Client_Port: 60576 , Application_Name: pgAdmin 4 - DB:lock_test +
| | | | | | | | | | Xact_Start: 2023-09-19 02:31:10.578448-07 , Query_Start: 20
23-09-19 02:31:10.578448-07 , Xact_Elapse: 00:00:31.948897 , Query_Elapse: 00:00:31.948897+
| | | | | | | | | | SQL (Current SQL in Transaction):
+
| | | | | | | | | | alter table t_lock add dept_no int;
+
| | | | | | | | | | --------
+
| | | | | | | | | | Pid: 16977
+
| | | | | | | | | | Lock_Granted: true , Mode: RowExclusiveLock , FastPath: fal
se , VirtualTransaction: 12/142 , Session_State: idle in transaction +
| | | | | | | | | | Username: postgres , Database: test_pg , Client_Addr: 192.1
68.80.100/32 , Client_Port: 60264 , Application_Name: pgAdmin 4 - DB:lock_test +
| | | | | | | | | | Xact_Start: 2023-09-19 02:29:22.744744-07 , Query_Start: 20
23-09-19 02:30:25.770099-07 , Xact_Elapse: 00:02:19.782601 , Query_Elapse: 00:01:16.757246+
| | | | | | | | | | SQL (Current SQL in Transaction):
+
| | | | | | | | | | update t_lock set username= 'ghjkl' where id='1';
此视图会按锁的大小排序，要快速解除锁堵塞状态，terminate 最大的锁对应的 PID 即可。
select pg_terminate_backend(
16977
);
上图PID：
Pid: 17129
Lock_Granted: false , Mode: AccessExclusiveLock , FastPath: false , VirtualTransaction: 14/28 , Session_State: active
Username: postgres , Database: test_pg , Client_Addr: 192.168.80.100/32 , Client_Port: 60576 , Application_Name: pgAdmin 4 - DB:lock_test
Xact_Start: 2023-09-19 17:31:10.578448+08 , Query_Start: 2023-09-19 17:31:10.578448+08 , Xact_Elapse: -00:03:25.8994 , Query_Elapse: -00:03:25.8994
SQL (Current SQL in Transaction):
alter table t_lock add dept_no int;
--------
Pid: 16977
Lock_Granted: true , Mode: RowExclusiveLock , FastPath: false , VirtualTransaction: 12/142 , Session_State: idle in transaction
Username: postgres , Database: test_pg , Client_Addr: 192.168.80.100/32 , Client_Port: 60264 , Application_Name: pgAdmin 4 - DB:lock_test
Xact_Start: 2023-09-19 17:29:22.744744+08 , Query_Start: 2023-09-19 17:30:25.770099+08 , Xact_Elapse: -00:01:38.065696 , Query_Elapse: -00:02:41.091051
SQL (Current SQL in Transaction):
update t_lock set username= 'ghjkl' where id='1';select pid,
pg_blocking_pids(pid),
wait_event_type,wait_event,
query
from pg_stat_activity;
pid | pg_blocking_pid |wait_event_type | wait_event | query
16977 | {} | Client | ClientRead | update t_lock set username= 'ghjk2' where id='2';
16828 | {} | Client | ClientRead | SHOW search_path
17129 | {16977} | Lock | relation | alter table t_lock add dept_no2 int;
17190 | {} | Client | ClientRead | select * from v_locks_monitor;
上面查询可以看出，17129的进程被锁，在等待16977的进程。
行级锁
行级锁
同一个事务可能会在相同的行上保持冲突的锁，甚至是在不同的子事务中。但是除此之外，两个事务永远不可能在相同
的行上持有冲突的锁
FOR UPDATE 更新
FOR UPDATE 会导致由 SELECT 语句检索到的行被锁定，就好像它们要被更新。这可以阻止它们被其他事务锁定、修改或者删除，直到当前事务结束。
也就是说其他尝试 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或者 SELECT FOR KEY SHARE 这些行的事务将被阻塞，直到当前事务结束。
反过来，SELECT FOR UPDATE 将等待已经在相同行上运行以上这些命令的并发事务，并且接着锁定并且返回被更新的行（或者没有行，因为行可能已被删除）。
FOR NO KEY UPDATE 无键更新
行为与 FOR UPDATE 类似，不过获得的锁较弱，这种锁将不会阻塞尝试在相同行上获得锁的 SELECT FOR KEY SHARE 命令。任何不获取 FOR UPDATE 锁的 UPDATE 也会获得这种锁模式。
FOR SHARE 共享
行为与 FOR NO KEY UPDATE 类似，不过它在每个检索到的杭上获得一个共享锁而不是排他锁。
一个共享锁会阻塞其他食物在这些行上执行 UPDATE、DELETE、SELECT FOR UPDATE 或者 SELECT FOR NO KEY UPDATE，但是它不会阻止它们执行 SELECT FOR SHARE 或者 SELECT FRO KEY SHARE。
FOR KEY SHARE 键共享
行为与 FOR SHARE 类似，不过锁较弱，SELECT FOR UPDATE 会被阻塞，但是 SELECT FOR NO KEY UPDATE 不会被阻塞，一个键共享锁会阻塞其他事务执行
修改键值的 DELETE 或者 UPDATE，但不会阻塞其他 UPDATE，也不会阻止 SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或者 SELECT FOR KEY SHARE。
执行语句 timeout 参数
lock_timeout
lock_timeout：获取一个表，索引，行上的锁超过这个时间，直接报错，不等待，0为禁用。
#show lock_timeout
statement_timeout
statement_timeout：当SQL语句的执行时间超过这个设置时间，终止执行SQL，0为禁用。
#show statment_timeout
idle_in_transaction_session_timeout
idle_in_transaction_session_timeout：在一个空闲的事务中，空闲时间超过这个值，将视为超时，0为禁用。
#show
idle_in_transaction_session_timeout
deadlock_timeout
dealdlock_timeout：死锁时间超过这个值将直接报错，不会等待，默认设置为1s。
#show
dealdlock_timeout
监控锁
所有活动事务持有的监控锁的基本配置即为系统视图 pg_locks。这个视图为每个可加锁的对象、已请求的锁模式和相关事务包含一行记录。非常重要的一点是，
pg_locks 持有内存中被跟踪的锁的信息，所以它不显示行级锁！（译注：据查以前的文档，有关行级锁的信息是存在磁盘上，而非内存）这个视图显示表级锁和劝告锁。
如果一个事务在等待一个行级锁，它通常在视图中显示为在等待该行级锁的当前所有者的固定事务 ID。这使得调试行级锁更为困难。事实上，在任何地方你都看不到行级
锁，直到有人阻塞了持有此锁的事务（然后你在 pg_locks 表里可以看到一个被上锁的元组）。pg_locks 是可读性欠佳的视图（不是很人性化），所以我们来让显示锁
定信息的视图更好接受些：
-- View with readable locks info and filtered out locks on system tables
CREATE VIEW active_locks AS
SELECT clock_timestamp(), pg_class.relname, pg_locks.locktype, pg_locks.database,
pg_locks.relation, pg_locks.page, pg_locks.tuple, pg_locks.virtualtransaction,
pg_locks.pid, pg_locks.mode, pg_locks.granted
FROM pg_locks JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE relname !~ '^pg_' and relname <> 'active_locks';
-- Now when we want to see locks just type
SELECT * FROM active_locks;查询
--查看会话session
select pg_backend_pid();
--查看会话持有的锁
select * from pg_locks where pid=3797;
--1,查看数据库
select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; //查询所有数据库,及其所占空间大小
--2. 查询存在锁的数据表
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname -- ,sa.*
from pg_locks a
join pg_class b on a.relation = b.oid
inner join pg_stat_activity sa on a.pid=sa.procpid
--3.查询某个表内,状态为lock的锁及关联的查询语句
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname -- ,sa.*
from pg_locks a
join pg_class b on a.relation = b.oid
inner join pg_stat_activity sa on a.pid=sa.procpid
where a.database=382790774 and sa.waiting_reason='lock'
order by sa.query_start
--4.查看数据库表大小
select pg_database_size('playboy');
--5.查看会话被谁阻塞
select pg_blocking_pids(3386);
死锁
显式锁定的使用可能会增加死锁的可能性，死锁是指两个（或多个）事务相互持有对方想要的锁。
例如，如果事务 1 在表 A 上获得一个排他锁，同时试图获取一个在表 B 上的排他锁， 而事务 2 已经持有表 B 的排他锁，同时却正在请求表 A 上的一个排他锁，那么两个事务就都不能进行下去。PostgreSQL能够自动检测到死锁情况 并且会通过中断其中一个事务从而允许其它事务完成来解决这个问题（具体哪个事务会被中 断是很难预测的，而且也不应该依靠这样的预测）。
要注意死锁也可能会作为行级锁的结果而发生（并且因此，它们即使在没有使用显式锁定的情况下也会发生)。考虑如下情况，两个并发事务在修改一个表。第一个事务执行：
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
这样就在指定帐号的行上获得了一个行级锁。然后，第二个事务执行：
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
第一个UPDATE语句成功地在指定行上获得了一个行级锁，因此它成功更新了该行。 但是第
二个UPDATE语句发现它试图更新的行已经被锁住了，因此它等待持有该锁的事务结束。事
务二现在就在等待事务一结束，然后再继续执行。现在，事务一执行：
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
事务一试图在指定行上获得一个行级锁，但是它得不到：事务二已经持有了这样的锁。所以
它要等待事务二完成。因此，事务一被事务二阻塞，而事务二也被事务一阻塞：一个死锁。
PostgreSQL将检测这样的情况并中断其中一个事务。
防止死锁的最好方法通常是保证所有使用一个数据库的应用都以一致的顺序在多个对象上获得锁。在上面的例子里，如果两个事务以同样的顺序更新那些行，那么就不会发生死锁。 我们也应该保证一个事务中在一个对象上获得的第一个锁是该对象需要的最严格的锁模式。如果我们无法提前验证这些，那么可以通过重试因死锁而中断的事务来即时处理死锁。
只要没有检测到死锁情况，寻求一个表级或行级锁的事务将无限等待冲突锁被释放。这意味着一个应用长时间保持事务开启不是什么好事（例如等待用户输入）。
附录二：查看阻塞会话，并生成kill sql
SELECT pg_cancel_backend(pid); –-session还在，事物回退;
SELECT pg_terminate_backend(pid); --session消失，事物回退
with recursive
tmp_lock as (
select distinct
--w.mode w_mode,w.page w_page,
--w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
--now()-w.query_start w_locktime,w.query w_query
w.pid as id,--w_pid,
r.pid as parentid--r_pid,
--r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,
--r.relation::regclass,
--r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
--r.query_start r_query_start,
--now()-r.query_start r_locktime,r.query r_query,
from (
select a.mode,a.locktype,a.database,
a.relation,a.page,a.tuple,a.classid,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
a.transactionid,
b.query as query,
b.xact_start,b.query_start,b.usename,b.datname
from pg_locks a,
pg_stat_activity b
where a.pid=b.pid
and not a.granted
) w,
(
select a.mode,a.locktype,a.database,
a.relation,a.page,a.tuple,a.classid,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
a.transactionid,
b.query as query,
b.xact_start,b.query_start,b.usename,b.datname
from pg_locks a,
pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity
where a.pid=b.pid
and a.granted
) r
where 1=1
and 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.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.transactionid is not distinct from w.transactionid
and r.pid <> w.pid
),
tmp0 as (
select *
from tmp_lock tl
union all
select t1.parentid,0::int4
from tmp_lock t1
where 1=1
and t1.parentid not in (select id from tmp_lock)
),
tmp3 (pathid,depth,id,parentid) as (
SELECT array[id]::text[] as pathid,1 as depth,id,parentid
FROM tmp0
where 1=1 and parentid=0
union
SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid
FROM tmp0 t1, tmp3 t0
where 1=1 and t1.parentid=t0.id
)
select distinct
'/'||array_to_string(a0.pathid,'/') as pathid,
a0.depth,
a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id,
--'select pg_cancel_backend('||a0.id|| ');' as cancel_pid,
--'select pg_terminate_backend('||a0.id|| ');' as term_pid,
case when a0.depth =1 then 'select pg_terminate_backend('|| a0.id || ');' else null end as term_pid,
case when a0.depth =1 then 'select cancel_backend('|| a0.id || ');' else null end as cancel_pid
,a2.datname,a2.usename,a2.application_name,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state
--,a2.backend_start,a2.xact_start,a2.query_start
from tmp3 a0
left outer join (select distinct '/'||id||'/' as prefix_id,id
from tmp0
where 1=1 ) a1
on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0
left outer join pg_stat_activity a2 -- select * from pg_stat_activity
on a0.id = a2.pid
order by '/'||array_to_string(a0.pathid,'/'),a0.depth;
输出结果格式如下：
400
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
