首页 > > 网络编程 > 其它 >

查看某表有没有语句被锁住

2018-06-17 19:51:39来源：未知阅读 ()

　　今天在思考postgresql里面锁是怎么实现的，讨论了暂时认为是master进程维护一张表，在各个语句获取锁的时候，查询该表能获取对应的锁就继续，否则就等待，执行完成后释放锁。

　　后面我再去研究到底怎么实现的，今天先实现怎么查看某表当前有哪些锁在上面。

会话1：

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

事物不提交，在事物2中查询表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)

这个时候可以看到该表有两个语句获取了锁，当然还看到了一个事物本身也会给该表加很多锁的。

下面是postgresql的锁的介绍：

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

表级锁类型

表级锁类型分为八种，以下对各种表级锁类型进行简单介绍下, 锁的冲突模式可以参考3.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),这种模式保证了当前只有一个事务访问这张表;

“ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL” 命令会获得这种类型锁，在Lock table 命令中，如果没有申明其它模式，它也是缺省模式。

三、表级锁冲突模式
3.1 Conflicting lock modes

Requested Lock Mode	Current Lock Mode
Requested 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