10.PostgreSQL锁的处理_pgsql 锁表

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函数只能取消被阻塞的事务。