锁类型 对应的数据库操作
ACCESS SHARE select
ROW SHARE select for update, select for share
ROW EXCLUSIVE update,delete,insert
SHARE UPDATE EXCLUSIVE vacuum(without full),analyze,create index concurrently
SHARE create index
SHARE ROW EXCLUSIVE 任何Postgresql命令不会自动获得这种锁，创建trigger和某些形式的alter table会获得该锁
EXCLUSIVE 任何Postgresql命令不会自动获得这种类型的锁 ，刷新雾化视图获得该锁
ACCESS EXCLUSIVE alter table,drop table,truncate,reindex,cluster,vacuum full
1.ACCESS SHARE
select语句会获取该锁
"ACCESS SHARE"锁模式只与"ACCESS EXCLUSIVE" 锁模式冲突;
即select与"ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL"冲突
session 1
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 16384 | 11653 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2685 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2684 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 3455 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2663 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2662 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2615 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 1259 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
virtualxid | | | | | 4/19 | | | | | 4/19 | 27353 | ExclusiveLock | t | t
(9 rows)
aaa=# select * from t1;
i | j
---+---
3 | 1
3 | 1
3 | 1
3 | 1
3 | 1
3 | 1
1 | 2
2 | 2
3 | 2
3 | 3
3 | 4
3 | 5
(12 rows)
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 16384 | 16388(加锁的表) | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t //此处可以看到加的ACCESS SHARE锁
relation | 16384 | 11653(pg_locks表) | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2685 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2684 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 3455 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2663 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2662 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2615 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 1259 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
virtualxid | | | | | 4/19 | | | | | 4/19 | 27353 | ExclusiveLock | t | t
session 2
aaa=# alter table t1 add column k varchar(10);
session 3
select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
relation | 13321 | 11653 | | | | | | | | 3/209 | 27713 | AccessShareLock | t | t
virtualxid | | | | | 3/209 | | | | | 3/209 | 27713 | ExclusiveLock | t | t
relation | 16384 | 11653 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2685 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2684 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 3455 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2663 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2662 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 2615 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
relation | 16384 | 1259 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | t
virtualxid | | | | | 4/19 | | | | | 4/19 | 27353 | ExclusiveLock | t | t
virtualxid | | | | | 5/131 | | | | | 5/131 | 27624 | ExclusiveLock | t | t
transactionid | | | | | | 618 | | | | 5/131 | 27624 | ExclusiveLock | t | f
relation | 16384 | 16388 | | | | | | | | 4/19 | 27353 | AccessShareLock | t | f session 1 select获取到的锁
relation | 16384 | 16388 | | | | | | | | 5/131 | 27624 | AccessExclusiveLock | f | f session 3 alter table需要获取ACCESS EXCLUSIVE锁，两个锁模式冲突，所以session 3未获取到锁，grant为f
(15 rows)
2.ROW SHARE (该实验测试删掉了上面加的k列)
select for update, select for share 会获取该锁
"Row Share" 锁模式与"Exclusive’和"Access Exclusive"锁模式冲突;
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 16384 | 11653 | | | | | | | | 5/138 | 27624 | AccessShareLock | t | t
virtualxid | | | | | 5/138 | | | | | 5/138 | 27624 | ExclusiveLock | t | t
(2 rows)
aaa=# begin;
BEGIN
aaa=# select * from t1 for update;
i | j
---+---
3 | 1
3 | 1
3 | 1
3 | 1
3 | 1
3 | 1
1 | 2
2 | 2
3 | 2
3 | 3
3 | 4
3 | 5
(12 rows)
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 16384 | 11653 | | | | | | | | 5/139 | 27624 | AccessShareLock | t | t
relation | 16384 | 16388 | | | | | | | | 5/139 | 27624 | RowShareLock | t | t //获取表的rowshare锁
virtualxid | | | | | 5/139 | | | | | 5/139 | 27624 | ExclusiveLock | t | t
transactionid | | | | | | 621 | | | | 5/139 | 27624 | ExclusiveLock | t | f
(4 rows)
3.ROW EXCLUSIVE
"Row exclusive" 与 "Share,Shared row exclusive,Exclusive,Access exclusive"模式冲突;即DML与"CREATE INDEX","CREATE TRIGGER","REFRESH ATERIALIZED VIEW CONCURRENTLY","ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL"冲突
"Update,Delete,Insert"命令会在目标表上获得这种类型的锁，并且在其它被引用的表上加上"Access shared"锁,一般地，更改表数据的命令都将在这张表上获得"Row exclusive"锁。
只示范delete与alter table锁冲突，即"Row exclusive" 与 "Access exclusive"
session 1
aaa=# begin;
BEGIN
aaa=# select * from t1;
i | j
---+---
3 | 1
3 | 1
3 | 1
3 | 1
3 | 1
3 | 1
1 | 2
2 | 2
3 | 2
3 | 3
3 | 4
3 | 5
(12 rows)
aaa=# delete from t1 where j=4;
DELETE 1
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
relation | 16384 | 11653 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 3455 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2663 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2662 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2685 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2684 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2615 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 1259 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 16388 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 16388 | | | | | | | | 4/21 | 27353 | RowExclusiveLock | t | t
virtualxid | | | | | 4/21 | | | | | 4/21 | 27353 | ExclusiveLock | t | t
virtualxid | | | | | 5/140 | | | | | 5/140 | 27624 | ExclusiveLock | t | t
transactionid | | | | | | 622 | | | | 4/21 | 27353 | ExclusiveLock | t | f
(13 rows)
session 2
aaa=# begin;
BEGIN
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
relation | 16384 | 11653 | | | | | | | | 6/108 | 29310 | AccessShareLock | t | t
virtualxid | | | | | 6/108 | | | | | 6/108 | 29310 | ExclusiveLock | t | t
relation | 16384 | 11653 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 3455 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2663 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2662 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2685 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2684 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2615 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 1259 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 16388 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t 第一次执行select获得的锁
relation | 16384 | 16388 | | | | | | | | 4/21 | 27353 | RowExclusiveLock | t | t 第二次执行delete所获得的锁
virtualxid | | | | | 4/21 | | | | | 4/21 | 27353 | ExclusiveLock | t | t
transactionid | | | | | | 622 | | | | 4/21 | 27353 | ExclusiveLock | t | f
(14 rows)
aaa=# alter table t1 add column k varchar(10);
session 1
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
relation | 16384 | 11653 | | | | | | | | 6/108 | 29310 | AccessShareLock | t | t
virtualxid | | | | | 6/108 | | | | | 6/108 | 29310 | ExclusiveLock | t | t
relation | 16384 | 11653 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 3455 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2663 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2662 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2685 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2684 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 2615 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
relation | 16384 | 1259 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | t
virtualxid | | | | | 4/21 | | | | | 4/21 | 27353 | ExclusiveLock | t | t
relation | 16384 | 16388 | | | | | | | | 6/108 | 29310 | AccessExclusiveLock | f | f alter table所需的锁
relation | 16384 | 16388 | | | | | | | | 4/21 | 27353 | AccessShareLock | t | f
relation | 16384 | 16388 | | | | | | | | 4/21 | 27353 | RowExclusiveLock | t | f
transactionid | | | | | | 623 | | | | 6/108 | 29310 | ExclusiveLock | t | f
transactionid | | | | | | 622 | | | | 4/21 | 27353 | ExclusiveLock | t | f
4 SHARE UPDATE EXCLUSIVE
"Share update exclusive,Share,Share row exclusive,exclusive,Access exclusive"模式冲突，这种模式保护一张表不被并发的模式更改和VACUUM;
"Vacuum(without full), Analyze "和 "Create index concurrently"命令会获得这种类型锁。
即"Vacuum(without full), Analyze "和 "Create index concurrently"与"CREATE INDEX","CREATE TRIGGER","REFRESH ATERIALIZED VIEW CONCURRENTLY","ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL"冲突
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 16384 | 11653 | | | | | | | | 4/23 | 27353 | AccessShareLock | t | t
virtualxid | | | | | 4/23 | | | | | 4/23 | 27353 | ExclusiveLock | t | t
(2 rows)
aaa=# begin
aaa-# ;
BEGIN
aaa=# Analyze t1;
ANALYZE
aaa=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+----------
relation | 16384 | 11653 | | | | | | | | 4/24 | 27353 | AccessShareLock | t | t
virtualxid | | | | | 4/24 | | | | | 4/24 | 27353 | ExclusiveLock | t | t
transactionid | | | | | | 624 | | | | 4/24 | 27353 | ExclusiveLock | t | f
relation | 16384 | 16388 | | | | | | | | 4/24 | 27353 | ShareUpdateExclusiveLock | t | f analyze表获得锁
5.SHARE
与"Row exclusive,Shared update exclusive,Share row exclusive ,Exclusive,Access exclusive"锁模式冲突,这种模式保护一张表数据不被并发的更改;
"Create index"命令会获得这种锁模式。
即 "Create index"与， "Update,Delete,Insert"，"Vacuum(without full), Analyze "，"CREATE TRIGGER","REFRESH ATERIALIZED VIEW CONCURRENTLY","ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL"冲突
6.SHARE ROW EXCLUSIVE
与"Row exclusive,Share update exclusive,Shared,Shared row exclusive,Exclusive,Access Exclusive"锁模式冲突;
任何Postgresql 命令不会自动获得这种锁。创建trigger和某些形式的alter table会获得该锁
7. EXCLUSIVE
与" ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE"模式冲突,这种索模式仅能与Access Share 模式并发,换句话说，只有读操作可以和持有"EXCLUSIVE"锁的事务并行；
任何Postgresql 命令不会自动获得这种类型的锁；只有再刷新物化视图的时候获得该锁，刷新雾化视图只可以刷新
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 命令中，如果没有申明其它模式，它也是缺省模式。