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

标簽

PostgreSQL , 原子性 , 并行鎖同行 , advisory lock

https://github.com/digoal/blog/blob/master/201810/20181018_04.md#%E8%83%8C%E6%99%AF 背景

兩個會話，同時對同一個ID值的記錄執行删除後插入，有一個會話會出現删除0，插入失敗的現象。

https://github.com/digoal/blog/blob/master/201810/20181018_04.md#%E7%8E%B0%E8%B1%A1 現象

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  不報錯  
           

https://github.com/digoal/blog/blob/master/201810/20181018_04.md#%E5%8E%9F%E5%9B%A0%E5%88%86%E6%9E%90 原因分析

行級鎖。

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   
           

https://github.com/digoal/blog/blob/master/201810/20181018_04.md#deferable-%E7%BA%A6%E6%9D%9F%E6%97%A0%E6%B3%95%E8%A7%A3%E5%86%B3%E8%BF%99%E4%B8%AA%E9%97%AE%E9%A2%98 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.  
           

https://github.com/digoal/blog/blob/master/201810/20181018_04.md#%E6%8E%A8%E8%8D%90%E8%A7%A3%E5%86%B3%E6%96%B9%E6%A1%88 推薦解決方案

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  
           

https://github.com/digoal/blog/blob/master/201810/20181018_04.md#%E6%B3%A8%E6%84%8F%E4%BA%8B%E9%A1%B9 注意事項

adlock的id是庫級沖突，例如lock(1)，在同一個資料庫再lock(1)就會沖突。

是以，如果在同一個庫裡面，期望對不同的表裡面的資料使用同樣的adlock手段，建議不同的表錯開ID段，或者使用全局ID。

https://github.com/digoal/blog/blob/master/201810/20181018_04.md#%E5%B0%8F%E7%BB%93 小結

adlock為輕量級鎖，在本文提到的業務場景中，邏輯上把兩個事務中的SQL做成了串行執行。達到的效果滿足業務上的需求。

https://github.com/digoal/blog/blob/master/201810/20181018_04.md#%E5%8F%82%E8%80%83 參考

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