背景
两个会话，同时对同一个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://www.postgresql.org/docs/11/static/sql-createtable.html
https://www.postgresql.org/docs/11/static/sql-set-constraints.html
转自阿里云德哥
