翻看某表有未有语句被锁住

  前日在思维postgresql里面锁是怎么落到实处的,商量了一时认为是master进程维护一张表,在各样语句获得锁的时候,查询该表能博得相应的锁就再而三,否则就等候,执行到位后释放锁。

  前边笔者再去研讨究竟怎么落到实处的,今天先达成怎么查看某表当前有何锁在地点。

 

会话1:

apple=# begin
apple-# ;
BEGIN
apple=# insert into test_time values(1, now());
INSERT 0 1

 

 

东西不付出,在东西第22中学询问表test_time表的锁境况。

 

会话二:

apple=# select tableoid from test_time;
 tableoid
----------
    33433
(1 row)

 

apple=# select * from pg_locks ;
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
 relation      |    24738 |    11673 |      |       |            |               |         |       |          | 2/5170             | 13694 | AccessShareLock  | t       | t
 virtualxid    |          |          |      |       | 2/5170     |               |         |       |          | 2/5170             | 13694 | ExclusiveLock    | t       | t
 relation      |    24738 |    33433 |      |       |            |               |         |       |          | 4/1183             | 18598 | RowExclusiveLock | t       | t
 relation      |    24738 |     3455 |      |       |            |               |         |       |          | 4/1183             | 18598 | AccessShareLock  | t       | t
 relation      |    24738 |     2663 |      |       |            |               |         |       |          | 4/1183             | 18598 | AccessShareLock  | t       | t
 relation      |    24738 |     2662 |      |       |            |               |         |       |          | 4/1183             | 18598 | AccessShareLock  | t       | t
 relation      |    24738 |     2685 |      |       |            |               |         |       |          | 4/1183             | 18598 | AccessShareLock  | t       | t
 relation      |    24738 |     2684 |      |       |            |               |         |       |          | 4/1183             | 18598 | AccessShareLock  | t       | t
 relation      |    24738 |     2615 |      |       |            |               |         |       |          | 4/1183             | 18598 | AccessShareLock  | t       | t
 relation      |    24738 |     1259 |      |       |            |               |         |       |          | 4/1183             | 18598 | AccessShareLock  | t       | t
 virtualxid    |          |          |      |       | 4/1183     |               |         |       |          | 4/1183             | 18598 | ExclusiveLock    | t       | t
 transactionid |          |          |      |       |            |          2404 |         |       |          | 4/1183             | 18598 | ExclusiveLock    | t       | f
(12 rows)

 

apple=# select * from pg_stat_activity ;
 datid | datname |  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting |        state        | backend_xid | backend_xmin |                  query
-------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------+-------------+--------------+-----------------------------------------
 24738 | apple   | 13694 |       10 | apple   | psql             |             |                 |          -1 | 2016-10-28 10:22:00.636574+08 | 2016-10-29 14:05:28.998165+08 | 2016-10-29 14:05:28.998165+08 | 2016-10-29 14:05:28.998167+08 | f       | active              |             |         2404 | select * from pg_stat_activity ;
 24738 | apple   | 18598 |       10 | apple   | psql             |             |                 |          -1 | 2016-10-29 12:00:22.004672+08 | 2016-10-29 12:00:28.376113+08 | 2016-10-29 12:00:49.990668+08 | 2016-10-29 12:00:49.994364+08 | f       | idle in transaction |        2404 |              | insert into test_time values(1, now());
(2 rows)

 

诸如此类就能够差不离的看看什么样语句了,再具体一点,综合为八个言语:

apple=# select * from pg_stat_activity where pid in (select pid from pg_locks where relation in (select tableoid from test_time ));
 datid | datname |  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting |        state        | backend_xid | backend_xmin |                                                            query
-------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------+-------------+--------------+-----------------------------------------------------------------------------------------------------------------------------
 24738 | apple   | 18598 |       10 | apple   | psql             |             |                 |          -1 | 2016-10-29 12:00:22.004672+08 | 2016-10-29 12:00:28.376113+08 | 2016-10-29 12:00:49.990668+08 | 2016-10-29 12:00:49.994364+08 | f       | idle in transaction |        2404 |              | insert into test_time values(1, now());
 24738 | apple   | 13694 |       10 | apple   | psql             |             |                 |          -1 | 2016-10-28 10:22:00.636574+08 | 2016-10-29 14:01:17.295566+08 | 2016-10-29 14:01:17.295566+08 | 2016-10-29 14:01:17.295568+08 | f       | active              |             |         2404 | select * from pg_stat_activity where pid in (select pid from pg_locks where relation in (select tableoid from test_time ));
(2 rows)

 

 

本条时候能够见见该表有五个语句获取了锁,当然还察看了3个东西自身也会给该表加很多锁的。

 

上面是postgresql的锁的介绍:

 

以下内容转自:http://francs3.blog.163.com/blog/static/40576727201082134343604/

表级锁类型

表级锁类型分为多种,以下对种种表级锁类型进行简要介绍下,
锁的抵触形式能够参照三.一的图1:表级锁争辩形式。

2.1 ACCESS SHARE 

“ACCESS SHARE”锁形式只与“ACCESS EXCLUSIVE” 锁形式争辨;

询问命令(Select command)将会在它查询的表上获取”Access Shared”
锁,一般地,任何三个对表上的只读查询操作都将得到那种类型的锁。

2.2 ROW SHARE 

 “Row Share” 锁格局与”Exclusive’和”Access Exclusive”锁情势争执;

”Select for update”和”Select for
share”命令将赢得那体系型锁,并且拥有被引述但不曾 FOR UPDATE 的表上会加上”Access
shared locks”锁。

2.3 ROW EXCLUSIVE 

 “Row exclusive” 与 “Share,Shared roexclusive,Exclusive,Access
exclusive”格局顶牛;

“Update,Delete,Insert”命令会在对象表上得到那种类型的锁,并且在其他被引述的表上加上”Access
shared”锁,一般地,更改表数据的一声令下都将在那张表上收获”Row exclusive”锁。

2.4 SHARE UPDATE EXCLUSIVE 

”Share update exclusive,Share,Share row ,exclusive,exclusive,Access
exclusive”格局争辩,那种情势尊敬一张表不被出现的格局改变和VACUUM;

 “Vacuum(without full), Analyze ”和 “Create index
concurrently”命令会获取那系列型锁。  

2.5 SHARE 

与“Row exclusive,Shared update exclusive,Share row exclusive
,Exclusive,Access
exclusive”锁形式抵触,那种形式保养一张表数据不被出现的更动;

“Create index”命令会获取那种锁方式。

2.6 SHARE ROW EXCLUSIVE 

与“Row exclusive,Share update exclusive,Shared,Shared row
exclusive,Exclusive,Access Exclusive”锁格局争论;

别的Postgresql 命令不会自行获取那种锁。

2.7 EXCLUSIVE 

与” ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW
EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE”格局争执,那种索情势仅能与Access
Share 形式出现,换句话说,只有读操作能够和全数”EXCLUSIVE”锁的事务并行;

别的Postgresql 命令不会自动获得那连串型的锁; 

2.8 ACCESS EXCLUSIVE 

与全体格局锁争执(ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE
EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS
EXCLUSIVE),这种形式保险了当下唯有一个事务访问那张表;

“ALTE奥迪Q5 TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTE福睿斯, VACUUM FULL”
命令会取得那连串型锁,在Lock table
命令中,要是未有表明其余情势,它也是缺省情势。

3、表级锁争辩形式
3.1  Conflicting lock modes

Requested Lock Mode

Current Lock Mode

ACCESS SHARE

ROW SHARE

ROW EXCLUSIVE

SHARE UPDATE EXCLUSIVE

SHARE

SHARE ROW EXCLUSIVE

EXCLUSIVE

ACCESS EXCLUSIVE

ACCESS SHARE

 

 

 

 

 

 

X

X

ROW SHARE

 

 

 

 

 

 

X

X

ROW EXCLUSIVE

 

 

 

 

X

X

X

X

SHARE UPDATE EXCLUSIVE

 

 

 

X

X

X

X

X

SHARE

 

 

X

X

 

X

X

X

SHARE ROW EXCLUSIVE

 

 

X

X

X

X

X

X

EXCLUSIVE

 

X

X

X

X

X

X

X

ACCESS EXCLUSIVE

X

X

X

X

X

X

X

X

                            图一   表级锁争辨形式

 

相关文章