查看某表有沒有語句被鎖住

　　今天在思考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

                            圖一   表級鎖衝突模式