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

2 minute read

背景

两个会话,同时对同一个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

Flag Counter

digoal’s 大量PostgreSQL文章入口