1.现象描述
遇到一个问题,select和delete表时正常执行,但truncate和drop表时会一直运行而且不报错。
2.分析
"drop table " 和 "truncate table " 需要申请排它锁 "ACCESS EXCLUSIVE ", 执行这个命令卡住时,
说明此时这张表上还有操作正在进行,比如查询等,那么只有等待这个查询操作完成,
"drop table" 或"truncate table"或者增加字段的SQL 才能获取这张表上的
"ACCESS EXCLUSIVE" 锁 ,操作才能进行下去。
3.模拟会话
会话1:update itpux set id=1 where name='xsq1';
会话2:update itpux set name='xsq3' where id=1;
会话3:update itpux set id=3 where id=1;
会话4:update itpux set name='xsq4' where id=1;
select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query from pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
datid | datname | pid | usename | query_start | wait_event_type | wait_event | state | backend_xid | backend_xmin |query
-------+----------+------+----------+-------------------------------+-----------------+---------------------+---------------------+-------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------
| | 2363 | | | Activity | AutoVacuumMain | | | |
| | 2366 | postgres | | Activity | LogicalLauncherMain | | | |
13593 | postgres | 2379 | postgres | 2021-12-30 05:57:28.134777+08 | Client | ClientRead | idle in transaction | 525 | | update itpux set id=1 where name='xsq1';
13593 | postgres | 2506 | postgres | 2021-12-30 05:58:06.966698+08 | Lock | transactionid | active | 526 | 525 | update itpux set name='xsq3' where id=1;
13593 | postgres | 2636 | postgres | 2021-12-30 06:15:49.946071+08 | | | active | | 525 | select datid , datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query from pg_stat_activity;
13593 | postgres | 3049 | postgres | 2021-12-30 06:11:35.725454+08 | Lock | tuple | active | 527 | 525 | update itpux set id=3 where id=1;
13593 | postgres | 3362 | postgres | 2021-12-30 06:15:39.426266+08 | Lock | tuple | active | 528 | 525 | update itpux set name='xsq4' where id=1;
(10 rows)
state=idle in transaction 说明开始了事物,但是没有提交。
backend_xid 事物id;525
backend_xmin: 造成锁的事物id;
wait_event_type=Lock; 说明它被锁。
4.查询对象是否锁表了
(1)
postgres=# select oid from pg_class where relname='itpux';
oid
-------
16405
(1 row)
postgres=# select database,relation,pid,mode,granted,transactionid from pg_locks;
database | relation | pid | mode | granted | transactionid
----------+----------+------+------------------+---------+---------------
13593 | 16405 | 3362 | RowExclusiveLock | t |
| | 3362 | ExclusiveLock | t |
13593 | 16405 | 3049 | RowExclusiveLock | t |
| | 3049 | ExclusiveLock | t |
13593 | 12143 | 2636 | AccessShareLock | t |
| | 2636 | ExclusiveLock | t |
13593 | 16405 | 2506 | RowExclusiveLock | t |
| | 2506 | ExclusiveLock | t |
13593 | 16405 | 2379 | AccessShareLock | t |
13593 | 16405 | 2379 | RowExclusiveLock | t |
| | 2379 | ExclusiveLock | t |
13593 | 16405 | 3049 | ExclusiveLock | f |
| | 2379 | ExclusiveLock | t | 525
| | 3049 | ExclusiveLock | t | 527
| | 2506 | ShareLock | f | 525
| | 2506 | ExclusiveLock | t | 526
| | 3362 | ExclusiveLock | t | 528
13593 | 16405 | 2506 | ExclusiveLock | t |
13593 | 16405 | 3362 | ExclusiveLock | f |
(2)如果查询到了结果,表示该表被锁 则需要释放锁定
select pg_cancel_backend(3362);
(3)再次检查。
select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query from pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
datname | pid | usename | query_start | wait_event_type | wait_event | state | backend_xid | backend_xmin | query
----------+------+----------+-------------------------------+-----------------+---------------+---------------------+-------------+--------------+------------------------------------------------------------------------------------------------------------
postgres | 2379 | postgres | 2021-12-30 05:57:28.134777+08 | Client | ClientRead | idle in transaction | 525 | | update itpux set id=1 where name='xsq1';
postgres | 2506 | postgres | 2021-12-30 05:58:06.966698+08 | Lock | transactionid | active | 526 | 525 | update itpux set name='xsq3' where id=1;
postgres | 3049 | postgres | 2021-12-30 06:11:35.725454+08 | Lock | tuple | active | 527 | 525 | update itpux set id=3 where id=1;
postgres | 2636 | postgres | 2021-12-30 06:36:57.656019+08 | | | active | | 525 |
select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query
from pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
(4)
select pg_cancel_backend(3049);
select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query from pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
datname | pid | usename | query_start | wait_event_type | wait_event | state | backend_xid | backend_xmin | query
----------+------+----------+-------------------------------+-----------------+---------------+---------------------+-------------+--------------+------------------------------------------------------------------------------------------------------------
postgres | 2379 | postgres | 2021-12-30 05:57:28.134777+08 | Client | ClientRead | idle in transaction | 525 | | update itpux set id=1 where name='xsq1';
postgres | 2506 | postgres | 2021-12-30 05:58:06.966698+08 | Lock | transactionid | active | 526 | 525 | update itpux set name='xsq3' where id=1;
postgres | 2636 | postgres | 2021-12-30 06:38:16.176023+08 | | | active | | 525 | select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query fr
om pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
(5)
select pg_cancel_backend(2379); ---没有能够杀死锁的肇事者。
select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query from pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
datname | pid | usename | query_start | wait_event_type | wait_event | state | backend_xid | backend_xmin | query
----------+------+----------+-------------------------------+-----------------+---------------+---------------------+-------------+--------------+------------------------------------------------------------------------------------------------------------
postgres | 2379 | postgres | 2021-12-30 05:57:28.134777+08 | Client | ClientRead | idle in transaction | 525 | | update itpux set id=1 where name='xsq1';
postgres | 2506 | postgres | 2021-12-30 05:58:06.966698+08 | Lock | transactionid | active | 526 | 525 | update itpux set name='xsq3' where id=1;
postgres | 2636 | postgres | 2021-12-30 06:38:48.706315+08 | | | active | | 525 | select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query fr
om pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
(6)
select pg_cancel_backend(2379);--不能杀锁的肇事者,只能杀死被锁的事务。
select pg_terminate_backend(2379); --这个语句可以杀死锁的肇事者。
select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query from pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
datname | pid | usename | query_start | wait_event_type | wait_event | state | backend_xid | backend_xmin | query
----------+------+----------+-------------------------------+-----------------+------------+---------------------+-------------+--------------+---------------------------------------------------------------------------------------------------------------
postgres | 2506 | postgres | 2021-12-30 05:58:06.966698+08 | Client | ClientRead | idle in transaction | 526 | | update itpux set name='xsq3' where id=1;
postgres | 2636 | postgres | 2021-12-30 06:41:32.672638+08 | | | active | | 526 | select datname,pid ,usename,query_start,wait_event_type,wait_event,state,backend_xid,backend_xmin,query from
pg_stat_activity where state='active' or state='idle in transaction' order by query_start;
5.总结
紧急情况下如果要杀死锁的肇事者,只能使用pg_terminate_backend函数,
而pg_cancel_backend函数只能取消被阻塞的事务。