Locking issue with concurrent DELETE / INSERT in PostgreSQL - 解法 advisory lock

背景

两个会话，同时对同一个ID值的记录执行删除后插入，有一个会话会出现删除0，插入失败的现象。

现象

CREATE TABLE test (id INT PRIMARY KEY);  
  
INSERT INTO TEST VALUES (1);  
INSERT INTO TEST VALUES (2);  

执行如下几条SQL

begin;  
1:DELETE FROM test WHERE id=1;  
2:INSERT INTO test VALUES (1);  
3:COMMIT;  

现象

S1-1 runs (1 row deleted)  
S2-1 runs (but is blocked since S1 has a write lock)  
S1-2 runs (1 row inserted)  
S1-3 runs, releasing the write lock  
S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???  
S2-2 runs, reports a unique key constraint violation  

用户期望

S1-1 runs (1 row deleted)  
S2-1 runs (but is blocked since S1 has a write lock)    
S1-2 runs (1 row inserted)  
S1-3 runs, releasing the write lock  
S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???  用户期望这里的S1新插入的记录可以被删除  
S2-2 runs, reports a unique key constraint violation  不报错  

原因分析

行级锁。

S1-1 runs (1 row deleted)  
S2-1 runs (but is blocked since S1 has a write lock)   锁冲突，因为这条记录（ROWID）已经被S1锁住了  
S1-2 runs (1 row inserted)    
S1-3 runs, releasing the write lock  
S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???  S1提交，S2请求的这个ROWID已经被删除。所以DELETE 0  
S2-2 runs, reports a unique key constraint violation  报错，因为S1已经插入了同一个ID   

deferable 约束无法解决这个问题

https://www.postgresql.org/docs/11/static/sql-createtable.html

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]  

https://www.postgresql.org/docs/11/static/sql-set-constraints.html

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }  

例子

postgres=# create table t1(id int primary key deferrable) ;  
CREATE TABLE  
  
postgres=# insert into t1 values (1),(2);  
INSERT 0 2  
  
postgres=# begin;  
BEGIN  
postgres=# set constraints all deferred ;  
SET CONSTRAINTS  
postgres=# delete from t1 where id=1;  
DELETE 1  
postgres=# insert into t1 values (1);  
INSERT 0 1  
postgres=# end;  
COMMIT  
  
  
postgres=# begin;  
BEGIN  
postgres=# set constraints ALL deferred ;  
SET CONSTRAINTS  
postgres=# delete from t1 where id=1;  
DELETE 0  
postgres=# insert into t1 values (1); -- 不使用deferred，这里就直接报错  
INSERT 0 1  
使用deferred，现象不一样的地方，在事务结束时判断约束。  
postgres=# end;  
ERROR:  duplicate key value violates unique constraint "t1_pkey"  
DETAIL:  Key (id)=(1) already exists.  

推荐解决方案

adlock

《advisory lock 实现高并发非堵塞式 业务锁》

《PostgreSQL 使用advisory lock实现行级读写堵塞》

《PostgreSQL 无缝自增ID的实现 - by advisory lock》

《PostgreSQL 使用advisory lock或skip locked消除行锁冲突, 提高几十倍并发更新效率》

《PostgreSQL 秒杀场景优化》

例子

S1,对ID=1进行操作

postgres=# begin;  
BEGIN  
postgres=# select pg_try_advisory_xact_lock(1);  
 pg_try_advisory_xact_lock   
----------------------  
 t  
(1 row)  
业务上判断，返回TRUE后，继续下面的请求  
  
postgres=# delete from t1 where id=1;  
DELETE 1  
postgres=# insert into t1 values (1);  
INSERT 0 1  
postgres=# end;  
COMMIT  
事务结束自动释放pg_try_advisory_xact_lock  

S2,对ID=1进行操作

postgres=# begin;  
BEGIN  
postgres=# select pg_try_advisory_xact_lock(1);  
 pg_try_advisory_xact_lock   
----------------------  
 f  
(1 row)  
返回FALSE，业务上不断重试，pg_try_advisory_xact_lock(1)   
......  
直到S1提交，返回TRUE后，继续下面的SQL请求  
  
  
postgres=# delete from t1 where id=1;  
DELETE 1  
postgres=# insert into t1 values (1);  
INSERT 0 1  
postgres=# end;  
COMMIT  

注意事项

adlock的id是库级冲突，例如lock(1)，在同一个数据库再lock(1)就会冲突。

因此，如果在同一个库里面，期望对不同的表里面的数据使用同样的adlock手段，建议不同的表错开ID段，或者使用全局ID。

小结

adlock为轻量级锁，在本文提到的业务场景中，逻辑上把两个事务中的SQL做成了串行执行。达到的效果满足业务上的需求。

参考

https://dba.stackexchange.com/questions/27688/locking-issue-with-concurrent-delete-insert-in-postgresql

https://www.postgresql.org/docs/11/static/sql-createtable.html

https://www.postgresql.org/docs/11/static/sql-set-constraints.html

digoal’s 大量PostgreSQL文章入口