Re: BUG #13723: "duplicate key" error occurs when update delete and insert the same row concurrently

At 2015-10-25 23:38:23, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>
>I see no bug here; you're just making a mistaken assumption about how
>cross-transaction serialization works. At some point you're going to end
>up with a timing in which both clients are trying to do the DELETE. Only
>one does it; the other waits for that row change to commit, sees it's
>done, and concludes that there's nothing for it to do. (In particular,
>it will not see the row that was inserted later in the other transaction,
>because that's too new.) Now the second one's INSERT fails because
>there's already a row with id=1.
>
>If you want this sort of coding to execute stably, you could consider
>taking out a table-level lock, or some other way of preventing clients
>from concurrently deleting+inserting the same key. Or, just don't do
>that in the first place.
>

> regards, tom lane

In my opinion, the first update sql in the transaction should obtain a "FOR NO KEY UPDATE" Row-level Lock,
And "FOR NO KEY UPDATE" Row-level Lock is conflicting with each other,
So, the rest two sqls(delete and insert) in the two transactions should be executed sequentially instead of simultaneously.

http://www.postgresql.org/docs/9.4/static/explicit-locking.html#ROW-LOCK-COMPATIBILITY
--------------------------------------------------
FOR UPDATE
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends.
...

FOR NO KEY UPDATE
...
This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.
--------------------------------------------------

Is my understand wrong?

BTW:the similar problem(deadlock)
when i removed the primary key constraint from tb1 and run the above test again,the error became "deadlock".
postgres=# \d tb1
Table "public.tb1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
c | integer |

The error log:
------------------------------
2015-10-25 18:50:09.678 EDT 57676 0 LOG: statement: begin;
2015-10-25 18:50:09.678 EDT 57676 0 LOG: statement: update tb1 set c=2 where id=1
2015-10-25 18:50:09.679 EDT 57677 0 LOG: statement: begin;
2015-10-25 18:50:09.679 EDT 57677 0 LOG: statement: update tb1 set c=2 where id=1
2015-10-25 18:50:09.679 EDT 57676 39855 LOG: statement: delete from tb1 where id=1;
2015-10-25 18:50:10.680 EDT 57677 39856 ERROR: deadlock detected
2015-10-25 18:50:10.680 EDT 57677 39856 DETAIL: Process 57677 waits for ShareLock on transaction 39855; blocked by process 57676.
Process 57676 waits for ShareLock on transaction 39856; blocked by process 57677.
Process 57677: update tb1 set c=2 where id=1
Process 57676: delete from tb1 where id=1;
2015-10-25 18:50:10.680 EDT 57677 39856 HINT: See server log for query details.
2015-10-25 18:50:10.680 EDT 57677 39856 CONTEXT: while updating tuple (0,205) in relation "tb1"
2015-10-25 18:50:10.680 EDT 57677 39856 STATEMENT: update tb1 set c=2 where id=1
2015-10-25 18:50:10.680 EDT 57676 39855 LOG: statement: insert into tb1 values(1,2);
2015-10-25 18:50:10.681 EDT 57676 39855 LOG: statement: commit;

And if separated deleting and inserting to two transactions, "deadlock" error still ocurrs.
[postgres(at)localhost ~]$ cat test3.sql
begin;
update tb1 set c=2 where id=1
delete from tb1 where id=1;
commit;
insert into tb1 values(1,2);
[postgres(at)localhost ~]$ pgbench -n -f test3.sql -c 2 -j 2 -t 200
client 0 aborted in state 1: ERROR: deadlock detected
DETAIL: Process 58002 waits for ShareLock on transaction 72640; blocked by process 58003.
Process 58003 waits for ShareLock on transaction 72641; blocked by process 58002.
HINT: See server log for query details.
CONTEXT: while updating tuple (7,4) in relation "tb1"
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 2
number of transactions per client: 200
number of transactions actually processed: 203/400
latency average: 0.000 ms
tps = 128.549274 (including connections establishing)
tps = 128.917578 (excluding connections establishing)

Best Regards,
Chen Huajun