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

                            图一律   表级锁冲突模式

 

相关文章