开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 标签 PostgreSQL , 原子性 , 并行锁同行 , advisory lock 背景 两个会话,同时对同一个ID值的记录执行删除后插入,有一个会话会出现删除0,插入失败的现象。 现象 CREATE TABLE test (id INT PRIMARY KEY); INSER...
+关注继续查看

标签

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Consuming Hidden WCF RIA Services
原文 http://codeseekah.com/2013/07/05/consuming-hidden-wcf-ria-services/ A Silverlight application made it to my desk yesterday, an application that c...
737 0
perl: warning: Falling back to the standard locale ("C").
/********************************************************************************** * perl: warning: Falling back to the standard locale ("C"). * 说明: * 使用debootstrap的时候,遇到这个问题,记录解决方法。
774 0
业界首个机密计算容器运行时—Inclavare Containers正式进入CNCF!
Inclavare Containers 通过云原生计算基金会(CNCF)TOC 投票正式成为 CNCF 官方沙箱项目。
0 0
解决ecshop出现Warning: file_put_contents
解决ecshop出现Warning: file_put_contents
0 0
【Ant Design Pro】使用ant design pro做为你的开发模板(四) 联调正式后台接口与运行时全局配置
【Ant Design Pro】使用ant design pro做为你的开发模板(四) 联调正式后台接口与运行时全局配置
0 0
《如何参与贡献 PolarDB for PG 开源》|学习笔记
快速学习《如何参与贡献 PolarDB for PG 开源》。
0 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载