PostgreSQL识别阻塞会话

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:PostgreSQL识别阻塞会话
在Oracle当中,一般使用v$session中的获取阻塞信息(blocking_session),而在PostgreSQL中,我们来看看怎么识别阻塞会话。我们首先来做个测试。

postgres=# create table t1(id int);
CREATE TABLE

现在我们来给表增加一个字段,我们使用begin来开始事务,但是我们不用end块来结束。

postgres=# begin;
BEGIN
postgres=# alter table t1 add column name text;
ALTER TABLE

此时在另外一个节点执行插入就会hang住,一直等待,因为修改的DDL语句的事务仍然没有结束。

postgres=# insert into t1(id) values(1);

我们可以使用postgresql中的pg_stat_activity视图,可以看到它并不像oracle中的v$session视图,有blocking_session字段。而pg是通过backend_type来实现的。

postgres=# \d pg_stat_activity
                      View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
 datid            | oid                      |           |          |
 datname          | name                     |           |          |
 pid              | integer                  |           |          |
 usesysid         | oid                      |           |          |
 usename          | name                     |           |          |
 application_name | text                     |           |          |
 client_addr      | inet                     |           |          |
 client_hostname  | text                     |           |          |
 client_port      | integer                  |           |          |
 backend_start    | timestamp with time zone |           |          |
 xact_start       | timestamp with time zone |           |          |
 query_start      | timestamp with time zone |           |          |
 state_change     | timestamp with time zone |           |          |
 wait_event_type  | text                     |           |          |
 wait_event       | text                     |           |          |
 state            | text                     |           |          |
 backend_xid      | xid                      |           |          |
 backend_xmin     | xid                      |           |          |
 query            | text                     |           |          |
 backend_type     | text                     |           |          |


postgres=# select datname,pid,usename,wait_event_type,wait_event,state,query from pg_stat_activity where backend_type = 'client backend'
and pid != pg_backend_pid();
 datname  | pid  | usename  | wait_event_type | wait_event |        state        |                query
----------+------+----------+-----------------+------------+---------------------+--------------------------------------
 postgres | 3231 | postgres | Client          | ClientRead | idle in transaction | alter table t1 add column name text;
 postgres | 3386 | postgres | Lock            | relation   | active              | insert into t1(id) values(1);

(2 rows)

我们可以很容易看到阻塞的对象和他们执行的SQL语句。当然这个是会话数比较少的情况,当会话数比较多,通过查看pg_stat_activity来识别阻塞的会话会变得很棘手。

当您想知道PostgreSQL中当前持有或者是授权那些锁的时候,可以查看pg_locks,主要看RowExclusiveLock和AccessExclusiveLock。

postgres=# \d pg_locks
                   View "pg_catalog.pg_locks"
       Column       |   Type   | Collation | Nullable | Default
--------------------+----------+-----------+----------+---------
 locktype           | text     |           |          |
 database           | oid      |           |          |
 relation           | oid      |           |          |
 page               | integer  |           |          |
 tuple              | smallint |           |          |
 virtualxid         | text     |           |          |
 transactionid      | xid      |           |          |
 classid            | oid      |           |          |
 objid              | oid      |           |          |
 objsubid           | smallint |           |          |
 virtualtransaction | text     |           |          |
 pid                | integer  |           |          |
 mode               | text     |           |          |
 granted            | boolean  |           |          |
 fastpath           | boolean  |           |          |

postgres=# select locktype,database,relation,pid,mode,granted from pg_locks where pid != pg_backend_pid();
   locktype    | database | relation | pid  |        mode         | granted
---------------+----------+----------+------+---------------------+---------
 virtualxid    |          |          | 3386 | ExclusiveLock       | t
 virtualxid    |          |          | 3231 | ExclusiveLock       | t
 relation      |    13806 |    16434 | 3231 | AccessExclusiveLock | t
 transactionid |          |          | 3231 | ExclusiveLock       | t
 relation      |    13806 |    16434 | 3386 | RowExclusiveLock    | f
 relation      |    13806 |    16439 | 3231 | AccessExclusiveLock | t
 relation      |    13806 |    16437 | 3231 | ShareLock           | t
(7 rows)

上面,可以看到会话3386尝试访问行级别锁,但是没有授予。这个是当前插入的会话。我们可以把pg_locks与pg_database和pg_class,通过加入pid来获取更多的信息,如下所示:

postgres=# select b.locktype,d.datname,c.relname,b.pid,b.mode from pg_locks b,pg_database d,pg_class c where b.pid in (3386,3231) and b.database = d.oid and b.relation = c.oid;
 locktype | datname  | relname | pid  |        mode
----------+----------+---------+------+---------------------
 relation | postgres | t1      | 3231 | AccessExclusiveLock
 relation | postgres | t1      | 3386 | RowExclusiveLock
(2 rows)

那么我们如何识别阻塞会话呢?很简单,使用pg_blocking_pids函数传递被堵塞的会话,就可以看到阻塞者。

postgres=# select pg_blocking_pids(3386);
 pg_blocking_pids 
------------------
 {3231}
(1 row)

然后如果想杀掉这个会话可以使用pg_terminate_backend函数传入阻塞的源头会话。然后就可以插入成功了。

postgres=# select pg_terminate_backend(3231);
 pg_terminate_backend 
----------------------
 t
分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *