pgsql中的行锁

2022-04-14 22:49:51

pgsql中的行锁

pgsql中的行锁

前言

日常的工作中，对于同一个资源的操作，有时候我们难免要加上锁，以防止在操作中被别的进程给删除或者更改掉了，那么更多还是行级锁，那么我们就来探究下。

用户可见的锁

从系统视图pg_locks中可见

用户可见的锁，用户自己能够主动调用的，可以在pg_locks中看到是否grant的锁。包括regular lock和咨询锁。

regular Lock

regular lock分为表级别和行级别两种。

行级别

通过一些数据库操作自动获得一些行锁，行锁并不阻塞数据查询，只阻塞writes和locker，比如如下操作。

FOR UPDATE
FOR NO KEY UPFATE
FOR SHARE
FOR KEY SHARE

FOR UPDATE

FOR UPDATE锁可以使得SELECT语句获取行级锁，用于更新数据。锁定该行可以防止该行在本次的操作过程中，被其他的事务获取锁或者进行更改删除操作。就是说其他事务的操作会被阻塞直到当前事务结束；同样的，SELECT FOR UPDATE命令会等待直
到前一个事务结束。即尝试 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或 SELECT FOR KEY SHARE 的其他事务将被阻塞。反过来，SELECT FOR UPDATE将等待已经在相同行上运行以上这些命令的
并发事务，并且接着锁定并且返回被更新的行（或者没有行，因为行可能已被删除）。不过，在一个REPEATABLE READ或SERIALIZABLE事务中，如果一个要被锁定的行在事务开始后被更改，将会抛出一个错误。

任何在一行上的DELETE命令也会获得FOR UPDATE锁模式，在某些列上修改值的UPDATE也会获得该锁模式。当前UPDATE情况中被考虑的列集合是那些具有能用于外键的唯一索引的列（所以部分索引和表达式索引不被考虑），但是这种要求未来有可能会改变。

FOR NO KEY UPDATE

和FOR UPDATE命令类似，但是对于获取锁的要求更加宽松一些，在同一行中不会阻塞SELECT FOR KEY SHARE命令。同样在UPDATE命令的时候如果没有获取到FOR UPDATE锁的情况下会获取到该锁。

行为与FOR NO KEY UPDATE类似，不过它在每个检索到的行上获得一个共享锁而不是排他锁。一个共享锁会阻塞其他事务在这些行上执行UPDATE、DELETE、SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE，但是它不会阻止它们执行SELECT FOR SHARE或者SELECT FOR KEY SHARE。

行为与FOR SHARE类似，不过锁较弱：SELECT FOR UPDATE会被阻塞，但是SELECT FOR NO KEY UPDATE不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的DELETE或者UPDATE，但不会阻塞其他UPDATE，也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE。

测试下加锁之后的数据可见性

create table test_lock
(
    id   serial not null,
    name text   not null
);

alter table test_lock
    owner to postgres;

create unique index test_lock_id_uindex
    on test_lock (id);

INSERT INTO public.test_lock (id, name) VALUES (1, '小明');
INSERT INTO public.test_lock (id, name) VALUES (2, '小白');

加锁测试（FOR UPDATE）

查询1

/*查询事务1*/
begin;
select *
from test_lock
where id = 1
for update

查询2

/*查询事务2*/
begin;
select *
from test_lock
where id = 1
for update

当事务1在查询中锁住资源的时候，事务2就一直查不到数据，等待事务1提交

查询1事务提交

commit

事务2的查询马上结束等待，查询出当前的数据

别忘了事务2的commit提交

加锁测试（FOR UPDATE,UPDATE）

UPDATE和DELETE，操作也是带锁的，测试下和FOR UPDATE的阻塞情况
查询1

/*查询事务1*/
begin;
update test_lock set name='mignming' where id=1

查询2

/*查询事务2*/
begin;
select *
from test_lock
where id = 1
for update

发现SELECT FOR UPDATE被阻塞了

提交查询1，update的事务

commit

然后查询2结束阻塞，获取到了事务1更新的数据

别忘了事务2的commit提交

命令说明

begin;--开启事务

begin transaction;--开启事务

commit;--提交

rollback;--回滚

set lock_timeout=5000;--设置超时时间

需要注意的点

连表查询加锁时，不支持单边连接形式，例如：

select u.*,r.* from db_user u left join db_role r on u.roleid=r.id for update;

支持以下形式，并锁住了两个表中关联的数据：

select u.*,r.* from db_user u, db_role r where u.roleid=r.id for update;

举个栗子

有一个分类表category，有一个文档表document。一个分类对应多个文档，删除分类的时候有一个限制，分类下面必须没有文档才能删除。这时候删除可能出现这样的场景，当删除分类的时候，后面新建了一个文档，两事务并行，根据pgsql默认的事务隔离级别，读已提交。新建文档，通过分类id获取到的分类信息，是进行分类删除之前的信息，也就是查询到这个分类存在。当两个事物一起执行了，有可能出现新建的文档的分类id不存在的情况。那就出现了脏数据了。

如何解决呢？

首先想到的肯定是加锁了。

如果一个资源加锁了，后面的操作必须要等到前面资源操作结束才能获取到资源信息。

这是pgsq文档对读已提交隔离中行锁的查询描述，需要注意的是UPDATE,DELETE也是会锁住资源的

UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令在搜索目标行时的行为和SELECT一样：它们将只找到在命令开始时已经被提交的行。不过，在被找到时，这样的目标行可能已经被其它并发事务更新（或删除或锁住）。在这种情况下，即将进行的更新将等待第一个更新事务提交或者回滚（如果它还在进行中）。如果第一个更新事务回滚，那么它的作用将被忽略并且第二个事务可以继续更新最初发现的行。如果第一个更新事务提交，若该行被第一个更新者删除，则第二个更新事务将忽略该行，否则第二个更新者将试图在该行的已被更新的版本上应用它的操作。该命令的搜索条件（WHERE子句）将被重新计算来看该行被更新的版本是否仍然符合搜索条件。如果符合，则第二个更新者使用该行的已更新版本继续其操作。在SELECT FOR UPDATE和SELECT FOR SHARE的情况下，这意味着把该行的已更新版本锁住并返回给客户端。

所以，解决方法就是在，添加文档的时候对分类id加锁，这样删除分类的锁就和下面查询的锁互斥了，两者必须有个先后执行的顺序，会避免脏数据的产生。

	WITH lock_document_categories_cte AS (
		SELECT id
		FROM document_categories
		WHERE id = ${categoryId}
		AND enterprise_id = ${enterpriseId}
		FOR UPDATE
	),lock_document_directories_cte AS (
		SELECT id
		FROM document_directories
		WHERE id = ${directoryId}
		AND enterprise_id = ${enterpriseId}
		FOR UPDATE
	)
	INSERT INTO documents (
		enterprise_id, directory_id, category_id, code, name, author_id
	) VALUES (
		${enterpriseId}, (SELECT * FROM lock_document_directories_cte), (SELECT * FROM lock_document_categories_cte), ${code}, ${name}, ${authorId}
	)
	RETURNING id

通过lock_document_categories_cte中的FOR UPDATE，锁住资源，这样就和delete中的锁互斥了。

总结

UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE。都会对资源加锁。当加锁的资源在被执行的时候。后面的操作，要等前面资源操作执行完成才能进行操作，即将进行的更新将等待第一个更新事务提交或者回滚（如果它还在进行中）。如果第一个更新事务回滚，那么它的作用将被忽略并且第二个事务可以继续更新最初发现的行。如果第一个更新事务提交，若该行被第一个更新者删除，则第二个更新事务将忽略该行，否则第二个更新者将试图在该行的已被更新的版本上应用它的操作。该命令的搜索条件（WHERE子句）将被重新计算来看该行被更新的版本是否仍然符合搜索条件。如果符合，则第二个更新者使用该行的已更新版本继续其操作。在SELECT FOR UPDATE和SELECT FOR SHARE的情况下，这意味着把该行的已更新版本锁住并返回给客户端。

参考

【Postgresql锁机制（表锁和行锁）】https://blog.csdn.net/turbo_zone/article/details/84036511
【postgresql行级锁for update测试】https://blog.csdn.net/shuoyu816/article/details/80086810
【PostgreSQL 锁解密】https://www.oschina.net/translate/postgresql-locking-revealed
【显式锁定】http://postgres.cn/docs/11/explicit-locking.html

码农公寓