ACCESS翻看某表有没出告知句被钉住

  今天于揣摩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

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

                            图一律   表级锁冲突模式

 

相关文章