PostgreSQL并發删除插入同一條記錄時的奇怪現象及分析

用sysbench 0.4.12對PostgreSQL進行壓測時遇到了主鍵限制違反的錯誤。

然後發現原因在于PostgreSQL在并發執行依次更新删除插入同一條記錄的事務時(比如就像下面的事務)，就可能會報主鍵限制違反的錯誤。

begin;

update tb1 set c=2 where id=1;

delete from tb1 where id=1;

insert into tb1 values(1,2);

commit;

完整的再現方法是這樣的：

1. 建表

create table tb1(id int primary key,c int);

insert into tb1 values(1,1);

2. 準備SQL腳本

test.sql:

update tb1 set c=2 where id=1

3. 執行測試

[postgres(at)localhost ~]$ pgbench -n -f test.sql -c 2 -j 2 -t 2

client 1 aborted in state 3: ERROR: duplicate key value violates unique

constraint "tb1_pkey"

DETAIL: Key (id)=(1) already exists.

transaction type: Custom query

scaling factor: 1

query mode: simple

number of clients: 2

number of threads: 2

number of transactions per client: 2

number of transactions actually processed: 2/4

latency average: 0.000 ms

tps = 130.047467 (including connections establishing)

tps = 225.060485 (excluding connections establishing)

4. 檢視日志

事先已經配置PostgreSQL列印所有SQL

點選(此處)折疊或打開

[postgres(at)localhost ~]$ cat pg95data/pg_log/postgresql-2015-10-25_141648.log

2015-10-25 14:16:48.144 EDT 57177 0 LOG: database system was shut down at 2015-10-25 14:16:47 EDT

2015-10-25 14:16:48.146 EDT 57177 0 LOG: MultiXact member wraparound protections are now enabled

2015-10-25 14:16:48.149 EDT 57175 0 LOG: database system is ready to accept connections

2015-10-25 14:16:48.150 EDT 57181 0 LOG: autovacuum launcher started

2015-10-25 14:16:57.960 EDT 57184 0 LOG: connection received: host=[local]

2015-10-25 14:16:57.961 EDT 57184 0 LOG: connection authorized: user=postgres database=postgres

2015-10-25 14:16:57.971 EDT 57186 0 LOG: connection received: host=[local]

2015-10-25 14:16:57.971 EDT 57187 0 LOG: connection received: host=[local]

2015-10-25 14:16:57.972 EDT 57186 0 LOG: connection authorized: user=postgres database=postgres

2015-10-25 14:16:57.972 EDT 57187 0 LOG: connection authorized: user=postgres database=postgres

2015-10-25 14:16:57.975 EDT 57186 0 LOG: statement: begin;

2015-10-25 14:16:57.975 EDT 57186 0 LOG: statement: update tb1 set c=2 where id=1

2015-10-25 14:16:57.975 EDT 57187 0 LOG: statement: begin;

2015-10-25 14:16:57.976 EDT 57187 0 LOG: statement: update tb1 set c=2 where id=1

2015-10-25 14:16:57.978 EDT 57186 39682 LOG: statement: delete from tb1 where id=1;

2015-10-25 14:16:57.979 EDT 57186 39682 LOG: statement: insert into tb1 values(1,2);

2015-10-25 14:16:57.979 EDT 57186 39682 LOG: statement: commit;

2015-10-25 14:16:57.980 EDT 57186 0 LOG: statement: begin;

2015-10-25 14:16:57.981 EDT 57186 0 LOG: statement: update tb1 set c=2 where id=1

2015-10-25 14:16:57.981 EDT 57187 39683 LOG: statement: delete from tb1 where id=1;

2015-10-25 14:16:57.981 EDT 57186 39684 LOG: statement: delete from tb1 where id=1;

2015-10-25 14:16:57.981 EDT 57186 39684 LOG: statement: insert into tb1 values(1,2);

2015-10-25 14:16:57.981 EDT 57186 39684 LOG: statement: commit;

2015-10-25 14:16:57.983 EDT 57187 39683 LOG: statement: insert into tb1 values(1,2);

2015-10-25 14:16:57.983 EDT 57187 39683 ERROR: duplicate key value violates unique constraint "tb1_pkey"

2015-10-25 14:16:57.983 EDT 57187 39683 DETAIL: Key (id)=(1) already exists.

2015-10-25 14:16:57.983 EDT 57187 39683 STATEMENT: insert into tb1 values(1,2);

分析這段日志，發現和我的認識不符，我一直認為事務裡的第一條UPDATE會獲得一個行鎖，沒有得到鎖的事務會等到得到鎖的事務送出後把鎖釋放，這樣的話之後的操作就變成了串行操作，不會出現沖突。

于是，我把這個問題作為BUG送出到社群的Bug郵件清單。

http://www.postgresql.org/message-id/[email protected]

結果社群不認為這是Bug，而與PG實作MVCC的機制有關。并且手冊中确實也有說明。雖然UPDATE仍然是阻塞的，在持有行鎖的那個事務送出後，讀已送出隔離級别下，被解除阻塞的事務會再次進行更新操作。但是這次更新操作可能會看到不一緻的資料快照。

然而,不光是UPDATE，SELECT ... FOR UPDATE也可能看到不一緻的快照，實驗如下:

1. SQL腳本

test10.sql:

select * from tb1 where id=1 for update;

...

以上内容重複多次

2. 執行測試

[postgres@localhost ~]$ psql -f test10.sql >b1.log 2>&1 &

[postgres@localhost ~]$ psql -f test10.sql >b2.log 2>&1 &

3. 檢視日志

b1.log:

BEGIN

id | c

----+---

(0 rows)

UPDATE 0

DELETE 0

psql:test10.sql:29: ERROR: duplicate key value violates unique constraint "tb1_pkey"

ROLLBACK

從日志可以看出，“select * from tb1 where id=1 for update”看到了一個不一緻的狀态。這不就是“髒讀”嗎！

解釋

那麼，怎麼解釋這個事情？

PostgreSQL的處理邏輯是這樣的（手冊也有說明）：

兩個事務并發更新同一條記錄時會導緻一個事務被鎖住，持有鎖的事務送出後，被解除阻塞的事務的隔離級别如果是“讀已送出”則對更新對象行再次進行where條件評估，如果仍然滿足原來的where條件這執行更新否則不更新。

需要注意的是，where條件的再評估是針對初始檢索篩選出的行而不是對整個表重新執行檢索，是以如果這期間有insert過來的新行也滿足where條件，或者某個被更新的行從原來不滿足where條件變成了滿足where條件，是不會被處理的。

另外，被insert的行總被認為是新行，哪怕它的主鍵和之前剛剛删除的一行相同（我之前沒有意識到這一點，是以老在糾結）。

關于這個問題的詳細解釋，如下

參考PG的MVCC實作原理，邏輯上的行由1個或多個行版本(tuple)構成，這些tuple通過内部的t_ctid指向最新版本的tuple。像下面這樣.

開始時，邏輯行上隻有1個tuple，它的t_ctid指向自己(0,1) 。

postgres=# begin;

postgres=# select * from tb1;

 id | c

  1 | 2

(1 row)

postgres=# SELECT * FROM heap_page_items(get_raw_page('tb1', 0));

 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid

----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------

  1 | 8160 | 1 | 32 | 148302 | 0 | 0 | (0,1) | 2 | 11008 | 24 | |

UPDATE後，出現了2個tuple，第2個tuple是新版，是以這兩個tuple的t_ctid都指向(0,2) 。

postgres=# update tb1 set c=2 where id=1;

UPDATE 1

  1 | 8160 | 1 | 32 | 148302 | 148304 | 0 | (0,2) | 16386 | 8960 | 24 | |

  2 | 8128 | 1 | 32 | 148304 | 0 | 0 | (0,2) | 32770 | 10240 | 24 | |

(2 rows)

DELETE後，最新的tuple的t_xmax被标記上了删除它的事務的事務ID。

postgres=# delete from tb1 where id=1;

DELETE 1

  2 | 8128 | 1 | 32 | 148304 | 148304 | 0 | (0,2) | 40962 | 8224 | 24 | |

到目前為止，UPDATE和DELETE都作用的tuple都可以用t_ctid串起來，我們可以姑且稱之為“tuple鍊”。但是INSERT操作是不一樣的，它開始了一個新的tuple鍊。

postgres=# insert into tb1 values(1,2);

INSERT 0 1

  3 | 8096 | 1 | 32 | 148304 | 0 | 2 | (0,3) | 2 | 2048 | 24 | |

(3 rows)

在我們的例子中，由于并發更新而被阻塞的UPDATE操作在阻塞解除後，根據它操作對象的tuple（第一個tuple）的t_ctid找到這個tuple鍊上最新的tuple，即第2個tuple（它沒有在整個表上再次執行檢索，是以他沒有發現第3個tuple）。由于第2個tuple已經被删除了，是以UPDATE的影響行數是0。這個事務中後面的DELETE遇到了和UPDATE同樣的問題，開始時它沒有搶過其他并發事務，等其他并發事務執行完了，它同樣沒有看到新插入的行，是以DELETE的影響行數也是0。因為這個原因，執行INSERT操作時，表中已經有了相同key的記錄了，作為報主鍵限制違反的錯誤。

關于PostgreSQL MVCC的原理，可以參考  http://blog.chinaunix.net/uid-20726500-id-4040024.html。

分析到這裡，我們可以知道，即使沒有第一個update，就像下面這樣，問題也能再現。

[postgres@localhost ~]$ cat test0.sql

[postgres@localhost ~]$ pgbench -n -f test0.sql -c 2 -j 2 -t 1

client 0 aborted in state 2: ERROR: duplicate key value violates unique constraint "tb1_pkey"

DETAIL: Key (id)=(1) already exists.

number of transactions per client: 1

number of transactions actually processed: 1/2

tps = 94.393053 (including connections establishing)

tps = 223.788743 (excluding connections establishing)

是以關鍵在于，在一個讀已送出事務中，delete + insert同一個key就可能出現問題。

如果沒有主鍵會發生什麼？

如果沒有主鍵，你會發現事情會變得更糟。

先把主鍵去掉

drop table tb1;

create table tb1(id int,c int);

再次測試，沒有報錯。

number of transactions actually processed: 2/2

tps = 169.047418 (including connections establishing)

tps = 338.094836 (excluding connections establishing)

但你會發現插入了2條記錄。

最後

這個問題的危害還是有限的。

1，首先如果持有鎖的事務復原了，不會出現任何問題，它看到狀态還是來自一個已送出的事務（隻不過這個狀态不是最終狀态），是以不能算是“髒讀”。

2，其次隻有更新操作（包括select ... for update）可能會看到不一緻狀态，隻讀操作不會。

3，在同一個事務中，先後删除再插入同一個key也沒有什麼意義，也就測試程式可能會這麼幹（如果你的應用也是這麼寫的，請改掉！）。

如果非要在一個事務中删除再插入同一個key(或者遇到其它的更新操作會看到不一緻狀态的場景)，可以把隔離級别調高到可重複讀或可串行化。但是調高以後，你會發現錯誤消息變成了“并發沖突”。但這個變化是有意義的，“并發沖突”代表一個可以重試的錯誤，應用捕獲到這個錯誤後可以嘗試再次執行，而“主鍵沖突”的錯誤沒有這層含義。那麼，看上去這個問題僅僅成了一個錯誤消息不當的問題了（實際上當然不是這麼簡單）。