Skip to main content
 首页 » 数据库

PostgreSQL中的行锁

2022年07月19日153三少

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 SHARE

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

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


本文参考链接：https://www.cnblogs.com/ricklz/p/12671100.html
  文章来源：
  https://www.itguest.com/post/abdj3c1.html
阅读延展