PostgreSQL中的锁机制

环境:PG12~15 Centos7.9   

对于数据库的锁机制, pg12和pg15是完全相同的.

随着对PG内部操作的一些了解,很多都会对数据库对象造成锁,但是不同的锁之间有不同的关系,需要系统整理一下.

本文依据:

https://www.postgresql.org/docs/15/explicit-locking.html

PostgreSQL提供了多种lock mode来控制对表中数据的并发访问。在MVCC未提供所需行为的情况下，这些模式可用于应用程序控制的锁定。大多数PostgreSQL命令会自动获取适当模式的锁，以确保在命令执行时不会以不兼容的方式删除或修改引用的表。（例如，TRUNCATE不能安全地与同一张表上的其他操作并发执行，因此它获得ACCESS EXCLUSIVE表上的锁以强制执行。）

要检查数据库服务器中当前未完成的锁的列表，请使用pg_locks系统视图.

一 表级锁

下面显示可用的lock mode,以及postgreSQL自动使用它们的context,我们还可以使用LOCK命令显式运行这些锁.

注意:这些锁定模式都是表级锁定,即使名称中包含"row",它也是表级锁,这是有历史原因的.

两个事务不能同时在同一张表上持有冲突模式的锁,但是事务永远不会与自身发生冲突,它可能先获取一张表上的ACCESS EXCLUSIVE锁,然后再获取该表的ACCESS SHARE,这个是不排斥的.对于同一张表来说,非冲突锁可以由多个事务并发持有.

Table-Level Lock Modes:

锁类型	锁对应操作
ACCESS SHARE	是由SELECT查询引用的表所获取,一般来说,任何只读表而不修改表的查询都会获取这种锁模式.
ROW SHARE	SELECT使用了如下选项:FOR UPDATE,FOR NO KEY UPDATE,FOR SHARE,或者FOR KEY SHARE等,
ROW EXCLUSIVE	一般是由UPDATE/DELETE/INSERT/MERGE命令引起的,通常任何修改表内数据的命令都会获取该锁.
SHARE UPDATE EXCLUSIVE	被VACUUM(without FULL),ANALYZE,CREATE INDEX CONCURRENTLY,CREATE STATISTICS,COMMENT ON,REINDEX CONCURRENTLY,以及ALTER INDEX和ALTER TABLE的某些变体
SHARE	CREATE INDEX (without CONCURRENTLY).
SHARE ROW EXCLUSIVE	CREATE TRIGGER和一些类型的ALTER TABLE
EXCLUSIVE	REFRESH MATERIALIZED VIEW CONCURRENTLY
ACCESS EXCLUSIVE	DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, REFRESH MATERIALIZED VIEW (without CONCURRENTLY). 许多ALTER INDEX 和 ALTER TABLE 也都会请求这个等级的锁. 这个也是LOCK TABLE不加任何选项的默认获取的锁.

TIPS:只有ACCESS EXCLUSIVE能够阻挡SELECT(without FOR UPDATE/SHARE)的执行.

二 行级锁

和表级锁类似,同一个事务能够在同一行上持有冲突的锁,即使在不同的子事务中也能持有.但是,两个事务不能在同一行上持有冲突锁.

行级锁不影响事务查询,他们只阻挡writer和blocker到同一行,行级锁在事务结束或保存点回滚时释放,和表级锁一样.

行级锁类型	引起对应锁的操作	描述
FOR UPDATE	SELECT .. FOR UPDATE , DELETE , UPDATE具有唯一索引的字段(将来新特性可能会变)	类似更新操作,这可以防止行在当前事务结束之前被其他事务锁定/修改或删除,申请该锁的事务将等待不并存的其他锁完成会话,然后锁定并返回更新的行
FOR NO KEY UPDATE	UPDATE不获取FOR UPDATE锁的其他操作	类似FOR UPDATE,但是锁弱一点,它可以允许其它的SELECT FOR KEY SHARE操作锁定相同行
FOR SHARE		比FOR NO KEY UPDATE更弱一点,共享锁会阻止其他事务在此行执行UPDATE,DELETE,SELECT FOR UPDATE或SELECT FOR NO KEY UPDATE,但是不会阻止其他的FOR SHARE或FOR KEY SHARE
FOR KEY SHARE		比FOR SHARE更弱,除了FOR UPDATE,别的都不阻止.

三 页级锁

除了表锁和行锁,页级共享/排他锁用于控制对share buffer pool的page的读写访问,这些锁在获取或更新一行之后立即释放.

四 DeadLocks

使用显式锁定会增加死锁的可能性,其中2个或更多事务各自持有对方想要的锁:

事务1有表A的排他锁,想获取表B的排他锁

事务2有表B的排他锁,想获取表A的排他锁

那么两个事务都无法继续,PG自动检测死锁情况并通过中止涉及的其中一个事务来释放死锁(中止的事务是随机的,不能预测)

实验:

构建测试表:student

create table student(id int primary key ,name varchar);
insert into student values(1,'xiaozhao');
insert into student values(2,'xiaoqian');

1. 会话窗口1:

begin;
update student set name='xiaosun' where id=1; 

2. 会话窗口2:

begin;
update student set name='xiaoli' where id=2; 
update student set name='xiaozhou' where id=1;      --执行该条发现会话被锁了

3. 会话窗口1:

update student set name='xiaowu' where id=2; 

结果:数据库自动将其中一个会话kill了:

db_test=> update student set name='xiaowu' where id=2; 
ERROR:  deadlock detected
DETAIL:  Process 4718 waits for ShareLock on transaction 1079; blocked by process 7556.
Process 7556 waits for ShareLock on transaction 1078; blocked by process 4718.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "student"

结论:防止死锁的最佳方法是所有访问数据库的应用程序以一致的顺序获取多个对象上的锁来避免死锁.在示例中,假如两个事务都以相同的顺序更新行,就不会发生死锁了.

只要没有检测到死锁,那么想获取表级或行级锁的事务就会无限期的等待冲突锁的释放,所以应用程序长时间保持事务打开是不恰当的.

五 咨询锁

属于PG的自有特性,不在此做过多描述