版权声明：本文为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