一、表锁
ACCESS SHARE (AccessShareLock)
仅与ACCESS EXCLUSIVE锁模式冲突。
SELECT命令对引用的表获取此模式的锁。一般来说，任何只读取表而不修改表的查询都会获取此锁模式。
--session 1
postgres=*# select * from t1;
id | name
----+----------------------
1 | yc
2 | wz
1 | yc
2 | wz
(4 行记录)
postgres=# select * from pg_locks ;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpat
h | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+---------+-----------------+---------+--------
--+-----------
relation | 5 | 12073 | | | | | | | | 4/481 | 2232066 | AccessShareLock | t | t
|
virtualxid | | | | | 4/481 | | | | | 4/481 | 2232066 | ExclusiveLock | t | t
|
relation | 5 | 16388 | | | | | | | | 3/541 | 2228294 | AccessShareLock | t | t
|
virtualxid | | | | | 3/541 | | | | | 3/541 | 2228294 | ExclusiveLock | t | t
|
(4 行记录)
--session 2
postgres=# lock table t1 in ACCESS EXCLUSIVE mode;
https://www.postgresql.org/docs/17/monitoring-stats.html#WAIT-EVENT-LOCK-TABLE
pg_locks字段解释
locktype 锁定对象类型
database 锁目标所在的数据库的 OID，如果目标是共享对象则为零，如果目标是事务 ID 则为空
relation 锁所针对的关系的 OID，如果目标不是关系或关系的一部分，则为 null
page 关系中锁所针对的页码，如果目标不是关系页或元组则为空
tuple 页面中锁所针对的元组编号，如果目标不是元组则为 null
virtualxid 锁所针对的事务的虚拟 ID，如果目标不是虚拟事务 ID，则为 null；
transactionid 锁所针对的事务的 ID，如果目标不是事务 ID，
classid oid 包含锁目标的系统目录的 OID，如果目标不是一般数据库对象则为空
objid oid 系统目录中锁目标的 OID，如果目标不是一般数据库对象则为空
objsubid 锁所针对的列号（classid和objid指表本身），如果目标是其他通用数据库对象，则为零，如果目标不是通用数据库对象，则为空
virtualtransaction 持有或等待此锁的事务的虚拟 ID
pid 持有或等待此锁的服务器进程的进程 ID，如果锁由准备好的事务持有，则为 null
mode 此进程持有或期望的锁模式的名称
granted 如果持有锁则为 True，如果等待锁则为 false
fastpath 如果通过快速路径获取锁则为 True，如果通过主锁表获取锁则为 false
waitstart granted服务器进程开始等待此锁的时间，如果锁被持有则为 null。请注意，即使为 ，在等待开始后的很短时间内，此时间也可能为 null false。
postgres=# select locktype,database,relation,page,tuple,virtualxid,virtualtransaction,pid,mode,granted from pg_locks order by 3,10;
locktype | database | relation | page | tuple | virtualxid | virtualtransaction | pid | mode | granted
---------------+----------+----------+------+-------+------------+--------------------+---------+---------------------+---------
relation | 5 | 12073 | | | | 4/504 | 2239924 | AccessShareLock | t
relation | 5 | 16388 | | | | 5/4 | 2246838 | AccessExclusiveLock | f
relation | 5 | 16388 | | | | 3/541 | 2228294 | AccessShareLock | t
transactionid | | | | | | 5/4 | 2246838 | ExclusiveLock | t
virtualxid | | | | | 3/541 | 3/541 | 2228294 | ExclusiveLock | t
virtualxid | | | | | 4/504 | 4/504 | 2239924 | ExclusiveLock | t
virtualxid | | | | | 5/4 | 5/4 | 2246838 | ExclusiveLock | t
(7 行记录)
postgres=# select a.locktype,a.database,a.relation,a.pid,a.mode,a.granted,b.relname,b.reltype from pg_locks a,pg_class b where a.relation=b.oid and b.oid in (select relation from pg_locks where granted='f');
locktype | database | relation | pid | mode | granted | relname | reltype
----------+----------+----------+---------+---------------------+---------+---------+---------
relation | 5 | 16388 | 2246838 | AccessExclusiveLock | f | t1 | 16390
relation | 5 | 16388 | 2228294 | AccessShareLock | t | t1 | 16390
ROW SHARE (RowShareLock)
与EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。
SELECT命令在所有指定了FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE或FOR KEY SHARE选项的表上获取此模式的锁（此外，还在任何没有显式FOR …锁定选项的引用表上获取ACCESS SHARE锁）。
--session 1
postgres=*# select * from t1 where id=1 for update;
id | name
----+----------------------
1 | yc
1 | yc
(2 行记录)
postgres=# select a.locktype,a.database,a.relation,a.pid,a.mode,a.granted,b.relname,b.reltype from pg_locks a,pg_class b where a.relation=b.oid;
locktype | database | relation | pid | mode | granted | relname | reltype
----------+----------+----------+---------+-----------------+---------+-----------------------------------+---------
relation | 5 | 3455 | 2239924 | AccessShareLock | t | pg_class_tblspc_relfilenode_index | 0
relation | 5 | 2663 | 2239924 | AccessShareLock | t | pg_class_relname_nsp_index | 0
relation | 5 | 2662 | 2239924 | AccessShareLock | t | pg_class_oid_index | 0
relation | 5 | 1259 | 2239924 | AccessShareLock | t | pg_class | 83
relation | 5 | 12073 | 2239924 | AccessShareLock | t | pg_locks | 12075
relation | 5 | 16388 | 2228294 | RowShareLock | t | t1 | 16390
postgres=# select a.locktype,a.database,a.relation,a.pid,a.mode,a.granted,b.relname,b.reltype from pg_locks a,pg_class b where a.relation=b.oid and b.relname='t1';
locktype | database | relation | pid | mode | granted | relname | reltype
----------+----------+----------+---------+--------------+---------+---------+---------
relation | 5 | 16388 | 2228294 | RowShareLock | t | t1 | 16390
(1 行记录)
--session 2
postgres=# select * from t1 for update;
postgres=# select locktype,database,relation,page,tuple,virtualtransaction,pid,mode,granted,fastpath,waitstart from pg_locks where relation=16388;
locktype | database | relation | page | tuple | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+--------------------+---------+---------------------+---------+----------+-----------
relation | 5 | 16388 | | | 5/6 | 2246838 | RowShareLock | t | t |
relation | 5 | 16388 | | | 3/545 | 2228294 | RowShareLock | t | t |
tuple | 5 | 16388 | 0 | 9 | 5/6 | 2246838 | AccessExclusiveLock | t | f |
(3 行记录)
--session2
postgres=# SELECT * FROM t1 WHERE ctid = '(0, 9)';
id | name
----+----------------------
1 | yc
(1 行记录)
ROW EXCLUSIVE (RowExclusiveLock)
与SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。
UPDATE, DELETE, INSERT和MERGE命令在目标表上获取此模式的锁（此外，还在任何其他引用表上获取ACCESS SHARE锁）。一般来说，任何修改表中数据的命令都会获取此锁模式。
postgres=*# insert into t1 values(9,'kj');
INSERT 0 1
postgres=# select a.locktype,a.database,a.relation,a.pid,a.mode,a.granted,a.page,a.tuple,b.relname,b.reltype from pg_locks a,pg_class b where a.relation=b.oid and b.relname='t1';
locktype | database | relation | pid | mode | granted | page | tuple | relname | reltype
----------+----------+----------+---------+------------------+---------+------+-------+---------+---------
relation | 5 | 16388 | 2228294 | RowExclusiveLock | t | | | t1 | 16390
SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)
与SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。此模式保护表不受同时进行的架构更改和VACUUM操作影响。
由VACUUM（不包括FULL）、ANALYZE、CREATE INDEX CONCURRENTLY、CREATE STATISTICS、COMMENT ON、REINDEX CONCURRENTLY以及某些ALTER INDEX和ALTER TABLE变体获取
--session 1
postgres=# lock table t1 in SHARE ROW EXCLUSIVE mode;
LOCK TABLE
--session 2
postgres=# vacuum t1;
hang住
--session2
postgres=# vacuum t1;
hang住
postgres=# select a.locktype,a.database,a.relation,a.pid,a.mode,a.granted,b.relname,b.reltype from pg_locks a,pg_class b where a.relation=b.oid and b.relname='t1';
locktype | database | relation | pid | mode | granted | relname | reltype
----------+----------+----------+---------+--------------------------+---------+---------+---------
relation | 5 | 16388 | 2465659 | ShareRowExclusiveLock | t | t1 | 16390
relation | 5 | 16388 | 2464507 | ShareUpdateExclusiveLock | f | t1 | 16390
(2 行记录)
SHARE (ShareLock)
与ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。此模式保护表不受同时进行的数据更改影响。
由CREATE INDEX（不包括CONCURRENTLY）获取。
--session 1
postgres=# begin;
BEGIN
postgres=*# lock table t1 in SHARE mode;
LOCK TABLE
postgres=*#
--session 2
postgres=# begin;
BEGIN
postgres=*# insert into t1 values(9,'df');
--session 3
postgres=# select a.locktype,a.database,a.relation,a.pid,a.mode,a.granted,b.relname,b.reltype from pg_locks a,pg_class b where a.relation=b.oid and b.relname='t1';
locktype | database | relation | pid | mode | granted | relname | reltype
----------+----------+----------+---------+------------------+---------+---------+---------
relation | 5 | 16388 | 2465659 | ShareLock | t | t1 | 16390
relation | 5 | 16388 | 2464507 | RowExclusiveLock | f | t1 | 16390
(2 行记录)
SHARE ROW EXCLUSIVE (ShareRowExclusiveLock)
与ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。此模式保护表不受同时进行的数据更改影响，并且是自排他性的，因此一次只能由一个会话持有。
由CREATE TRIGGER和某些形式的ALTER TABLE获取。
EXCLUSIVE (ExclusiveLock)
与ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。此模式仅允许并发的ACCESS SHARE锁，即，只有对表的读取可以与持有此锁模式的事务并行进行。
由REFRESH MATERIALIZED VIEW CONCURRENTLY获取。
ACCESS EXCLUSIVE (AccessExclusiveLock)
与所有模式的锁（ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE和ACCESS EXCLUSIVE）冲突。此模式确保持有者是唯一以任何方式访问该表的事务。
由DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL和REFRESH MATERIALIZED VIEW（不包括CONCURRENTLY）命令获取。许多形式的ALTER INDEX和ALTER TABLE也在此级别获取锁。这也是未明确指定模式的LOCK TABLE语句的默认锁模式。
二、行锁
1. FOR UPDATE
FOR UPDATE 会导致 SELECT 语句检索的行被锁定，锁定的方式类似于更新锁（UPDATE）。这将防止其他事务对这些行进行锁定、修改或删除，直到当前事务结束。也就是说，尝试对这些行执行以下操作的其他事务将被阻塞，直到当前事务结束：
- UPDATE
- DELETE
- SELECT FOR UPDATE
- SELECT FOR NO KEY UPDATE
- SELECT FOR SHARE
- SELECT FOR KEY SHARE
相反，执行 SELECT FOR UPDATE 的事务会等待并发事务，如果该事务已经对相同的行执行了上述任何命令，然后才会锁定并返回更新后的行（如果该行已被删除，则不会返回行）。
--session1
postgres=# select * from t1 where id=5 for update;
id | name
----+----------------------
5 | sf
(1 行记录)
--session2
postgres=# begin;
BEGIN
postgres=*# select * from t1 where id=5 for update;
--session 3
postgres=# select a.locktype,a.database,a.relation,a.pid,a.mode,a.granted,a.page,a.tuple,b.relname,b.reltype from pg_locks a,pg_class b where a.relation=b.oid and b.relname='t1';
locktype | database | relation | pid | mode | granted | page | tuple | relname | reltype
----------+----------+----------+---------+---------------------+---------+------+-------+---------+---------
relation | 5 | 16388 | 2465659 | RowShareLock | t | | | t1 | 16390
relation | 5 | 16388 | 2464507 | RowShareLock | t | | | t1 | 16390
tuple | 5 | 16388 | 2464507 | AccessExclusiveLock | t | 0 | 15 | t1 | 16390
(3 行记录)
postgres=# select * from pg_locks where locktype<>'virtualxid';
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+---------+---------------------+---------+----------+-------------------------------
relation | 5 | 12073 | | | | | | | | 5/33 | 2537811 | AccessShareLock | t | t |
relation | 5 | 16388 | | | | | | | | 3/616 | 2465659 | RowShareLock | t | t |
relation | 5 | 16388 | | | | | | | | 4/624 | 2464507 | RowShareLock | t | t |
transactionid | | | | | | 775 | | | | 3/616 | 2465659 | ExclusiveLock | t | f |
transactionid | | | | | | 775 | | | | 4/624 | 2464507 | ShareLock | f | f | 2024-11-25 20:52:12.440856+08
tuple | 5 | 16388 | 0 | 15 | | | | | | 4/624 | 2464507 | AccessExclusiveLock | t | f |
(6 行记录)
postgres=# select * from t1 where ctid='(0,15)';
id | name
----+----------------------
5 | sf
(1 行记录)
2. FOR NO KEY UPDATE
FOR NO KEY UPDATE 的行为与 FOR UPDATE 类似，但它获取的是较弱的锁：此锁不会阻止 SELECT FOR KEY SHARE 命令在相同行上获取锁。这个锁模式也会被任何不获取 FOR UPDATE 锁的 UPDATE 操作所获取。
--session 1
postgres=# select * from t1 where id=5 for key share;
id | name
----+----------------------
5 | sf
(1 行记录)
--session 2
postgres=*# select * from t1 where id=5 for key share;
id | name
----+----------------------
5 | sf
(1 行记录)
--session 3
postgres=# select * from pg_locks where locktype<>'virtualxid';
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+---------+-----------------+---------+----------+-----------
relation | 5 | 12073 | | | | | | | | 5/34 | 2537811 | AccessShareLock | t | t |
relation | 5 | 16388 | | | | | | | | 3/617 | 2465659 | RowShareLock | t | t |
relation | 5 | 16388 | | | | | | | | 4/627 | 2464507 | AccessShareLock | t | t |
relation | 5 | 16388 | | | | | | | | 4/627 | 2464507 | RowShareLock | t | t |
transactionid | | | | | | 777 | | | | 4/627 | 2464507 | ExclusiveLock | t | f |
transactionid | | | | | | 776 | | | | 3/617 | 2465659 | ExclusiveLock | t | f |
(6 行记录)
3. FOR SHARE 锁
FOR SHARE 锁通常用于那些需要共享访问的场景。它获取的是 共享锁，意味着其他事务可以并发地读取该行，但无法修改或删除该行。具体来说，FOR SHARE 锁会阻止其他事务执行以下操作：
- UPDATE
- DELETE
- SELECT FOR UPDATE
- SELECT FOR NO KEY UPDATE
但不会阻止其他事务执行 SELECT FOR SHARE 或 SELECT FOR KEY SHARE，这使得该行数据可以在多个事务之间共享读取而不产生冲突。共享锁 用于当你希望多个事务都能查看同一行数据，但又不希望它们修改或删除该行时。
--session 1
postgres=# select * from t1 where id=5 for share;
id | name
----+----------------------
5 | hj
(1 行记录)
--session 2
postgres=# update t1 set name='hj' where id=5;
--挂起
--session 3
postgres=# select * from pg_locks where locktype<>'virtualxid';
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+---------+------------------+---------+----------+-------------------------------
relation | 5 | 12073 | | | | | | | | 5/36 | 2537811 | AccessShareLock | t | t |
relation | 5 | 16388 | | | | | | | | 3/619 | 2465659 | RowShareLock | t | t |
relation | 5 | 16388 | | | | | | | | 4/630 | 2464507 | RowExclusiveLock | t | t |
transactionid | | | | | | 782 | | | | 4/630 | 2464507 | ExclusiveLock | t | f |
transactionid | | | | | | 781 | | | | 4/630 | 2464507 | ShareLock | f | f | 2024-11-25 21:12:39.741459+08
tuple | 5 | 16388 | 0 | 20 | | | | | | 4/630 | 2464507 | ExclusiveLock | t | f |
transactionid | | | | | | 781 | | | | 3/619 | 2465659 | ExclusiveLock | t | f |
(7 行记录)
4. FOR KEY SHARE 锁
FOR KEY SHARE 锁是一种较弱的锁，它的行为类似于 FOR SHARE，但相较于共享锁，它更加宽松：
FOR KEY SHARE 锁不会阻止其他事务执行 SELECT FOR NO KEY UPDATE 或 SELECT FOR SHARE，但会阻止 SELECT FOR UPDATE。
它允许其他事务执行 普通的 UPDATE 操作（即不修改主键或唯一约束的列），但会阻止对主键或唯一列值的修改。
这种锁通常用于 读写锁的场景，例如，当你希望其他事务可以读取数据，但无法删除或修改主键列时使用。
- FOR UPDATE 是一种独占锁，阻止其他事务对同一行进行修改或删除，直到事务结束。
- FOR NO KEY UPDATE 锁是较弱的锁，允许并发读取但阻止修改关键列。
- FOR SHARE 锁是一种共享锁，允许多个事务读取但不允许修改或删除数据。
- FOR KEY SHARE 锁是最弱的锁，允许读取，但仅阻止修改主键或唯一列。
三、死锁
当PostgreSQL检测到死锁时，它会自动中止其中一个事务，允许其他事务继续执行。通常，哪一个事务会被中止是由系统自动选择的，无法预测。因此，开发者不应该依赖系统中止某个特定事务来解决死锁问题。
-session 1
postgres=# begin;
BEGIN
postgres=*# update t1 set name='s1' where id=1;
UPDATE 1
--session 2
postgres=# begin;
BEGIN
postgres=*# update t1 set name='fs' where id=2;
UPDATE 1
postgres=*# update t1 set name='s1' where id=1;
--session 1
postgres=*# update t1 set name='fs' where id=2;
--session 2
错误: 检测到死锁
描述: 进程2464507等待在事务 783上的ShareLock; 由进程2465659阻塞.
进程2465659等待在事务 784上的ShareLock; 由进程2464507阻塞.
提示: 详细信息请查看服务器日志.
背景: 当更新关系"t1"的元组(0, 17)时
日志
2024-11-25 21:31:28.813 CST [2464507] 错误: 检测到死锁
2024-11-25 21:31:28.813 CST [2464507] 详细信息: 进程2464507等待在事务 783上的ShareLock; 由进程2465659阻塞.
进程2465659等待在事务 784上的ShareLock; 由进程2464507阻塞.
进程 2464507: update t1 set name='s1' where id=1;
进程 2465659: update t1 set name='fs' where id=2;
2024-11-25 21:31:28.813 CST [2464507] 提示: 详细信息请查看服务器日志.
2024-11-25 21:31:28.813 CST [2464507] 上下文: 当更新关系"t1"的元组(0, 17)时
2024-11-25 21:31:28.813 CST [2464507] 语句: update t1 set name='s1' where id=1;
死锁通常发生在多个事务相互等待对方持有的锁时。PostgreSQL能够自动检测死锁并中止其中一个事务。
为了避免死锁，建议所有事务按照一致的顺序获取锁，并尽量缩短事务的持锁时间。
死锁的处理可以通过自动重试机制来减少死锁的影响。
最后修改时间：2024-12-05 10:54:31
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
