Locking issue with concurrent DELETE / INSERT in PostgreSQL - 解法 advisory lock-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

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

简介: postgresql 高并发 数据库

背景
两个会话,同时对同一个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
转自阿里云德哥

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

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章