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