PostgreSQL锁机制——表级锁_select * from pg_locks where granted = 'f';

PostgreSQL提供了多种锁模式用于控制对表中数据的并发访问。大多数PostgreSQL命令会自动要求恰 当的锁以保证被引用的表在命令的执行过程中 不会以一种不兼容的方式删除或修改.
要检查在一个数据库服务器中当前未解除的锁列表，可以使用pg_locks系统视图
下面我们来看看PostgreSQL中的表级别锁有哪些,他们分别使用在什么样的场景.不同模式的锁意味着它们有着不同的冲突模式,即在不同的事务中不能对同一个表持有两种冲突的锁.但是 一 个事务决不会和自身冲突。例如，它可以在同一个表上获得ACCESS EXCLUSIVE锁然后接着获取ACCESS SHARE锁

ACCESS SHARE
只与ACCESS EXCLUSIVE锁模式冲突。SELECT命令在被引用的表上获得一个这种模式的锁。通常，任何只读取表而不修改它的查询都将获得这种锁模式。

--会话一
bill=# begin;
BEGIN
bill=# select * from t1 limit 10;

--会话二
bill=# select locktype,database,relation,mode,granted,fastpath from pg_locks where relation=19230;  
 locktype | database | relation |      mode       | granted | fastpath 
----------+----------+----------+-----------------+---------+----------
 relation |    16385 |    19230 | AccessShareLock | t       | t
(1 row)
1
2
3
4
5
6
7
8
9
10
11

ROW SHARE
与EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。SELECT FOR UPDATE和SELECT FOR SHARE命令在目标表上取得一个这种模式的 锁 (加上在被引用但没有选择FOR UPDATE/FOR SHARE的任何其他表上的ACCESS SHARE锁)

—会话一
bill=# begin;
BEGIN
bill=# select * from t1 for update limit 10

--会话二
bill=# select locktype,database,relation,mode,granted,fastpath from pg_locks where relation=19230; 
 locktype | database | relation |     mode     | granted | fastpath 
----------+----------+----------+--------------+---------+----------
 relation |    16385 |    19230 | RowShareLock | t       | t
(1 row)
1
2
3
4
5
6
7
8
9
10
11

ROW EXCLUSIVE
与SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。 命令UPDATE、DELETE和INSERT在目标表上取得这种锁模式(加上在任何其他被引用
表上的ACCESS SHARE锁)。通常，这种锁模式将被任何修改表中数据的命令取得。

--会话一
bill=# begin;
BEGIN
bill=# update t1 set info ='aaa' where id = 1;
UPDATE 1

--会话二
bill=#  select locktype,database,relation,mode,granted,fastpath from pg_locks where relation=19230; 
 locktype | database | relation |       mode       | granted | fastpath 
----------+----------+----------+------------------+---------+----------
 relation |    16385 |    19230 | RowExclusiveLock | t       | t
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12

SHARE UPDATE EXCLUSIVE
与SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表 不受并发模式改变和VACUUM运行的影响。由VACUUM(不带FULL)、ANALYZE、CREATE INDEX CONCURRENTLY、CREATE STATISTICS和ALTER TABLE VALIDATE以及其 他ALTER TABLE的变体获得。

--会话一
bill=# begin;
BEGIN
bill=# analyze t1;
ANALYZE

--会话二
bill=# select locktype,database,relation,mode,granted,fastpath from pg_locks where relation=19230; 
 locktype | database | relation |           mode           | granted | fastpath 
----------+----------+----------+--------------------------+---------+----------
 relation |    16385 |    19230 | ShareUpdateExclusiveLock | t       | f
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12

SHARE
与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表 不受并发数据改变的影响。由CREATE INDEX(不带CONCURRENTLY)取得。

--会话一
bill=# begin;
BEGIN
bill=# create index idx_t1 on t1 using btree(id);
CREATE INDEX

--会话二
bill=# select locktype,database,relation,mode,granted,fastpath from pg_locks where relation=19230; 
 locktype | database | relation |   mode    | granted | fastpath 
----------+----------+----------+-----------+---------+----------
 relation |    16385 |    19230 | ShareLock | t       | f
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12

SHARE ROW EXCLUSIVE
与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表 不受并发数据修改所影响，并且是自排他的，这样在一个时刻只能有一个会话持有它。由CREATE COLLATION、CREATE TRIGGER和很多 ALTER TABLE的很多形式所获得

--会话一
bill=# begin;
BEGIN
bill=# alter table t1 add column c1 jsonb;
ALTER TABLE

--会话二
bill=# select locktype,database,relation,mode,granted,fastpath from pg_locks where relation=19230; 
 locktype | database | relation |        mode         | granted | fastpath 
----------+----------+----------+---------------------+---------+----------
 relation |    16385 |    19230 | AccessExclusiveLock | t       | f
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12

EXCLUSIVE
与ROWSHARE、ROWEXCLUSIVE、SHAREUPDATEEXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式只允许 并发的ACCESS SHARE锁，即只有来自于表的读操作可以与一个持有该锁模式的事务并 行处理。由REFRESH MATERIALIZED VIEW CONCURRENTLY获得。(这里pg12官方文档是这么描述,但是看源码REFRESH MATERIALIZED VIEW CONCURRENTLY是无法获得EXCLUSIVE锁了)详情见src/include/storage/lockdefs.h

#define ExclusiveLock 7 /* blocks ROW SHARE/SELECT…FOR UPDATE */

--会话一
bill=# begin;
BEGIN
bill=# refresh materialized view CONCURRENTLY mv_t1;
REFRESH MATERIALIZED VIEW

--会话二(这里果然不是EXCLUSIVE锁了)
bill=# select locktype,database,relation,mode,granted,fastpath from pg_locks where relation=19230; 
 locktype | database | relation |      mode       | granted | fastpath 
----------+----------+----------+-----------------+---------+----------
 relation |    16385 |    19230 | AccessShareLock | t       | t
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12

ACCESS EXCLUSIVE
与所有模式的锁冲突(ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE)。这种模式保证持有者是访问该表的唯一事务。由ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW(不带CONCURRENTLY)命令获 取。ALTER TABLE的很多形式也在这个层面上获得锁这也是未 显式指定模式的LOCK TABLE命令的默认锁模式。
–会话一

bill=# begin;
BEGIN
bill=# drop table t1;
DROP TABLE

--会话二
bill=# select locktype,database,relation,mode,granted,fastpath from pg_locks where relation=19230; 
 locktype | database | relation |        mode         | granted | fastpath 
----------+----------+----------+---------------------+---------+----------
 relation |    16385 |    19230 | AccessExclusiveLock | t       | f
(1 row)
1
2
3
4
5
6
7
8
9
10
11

总结:
只有一个ACCESS EXCLUSIVE锁阻塞一个SELECT(不带FOR UPDATE/ SHARE)语句。