Обсуждение: BUG #13723: "duplicate key" error occurs when update delete and insert the same row concurrently

The following bug has been logged on the website:

Bug reference:      13723
Logged by:          ChenHuajun
Email address:      chjischj@163.com
PostgreSQL version: 9.4.5
Operating system:   CentOS 6
Description:

When i run sysbench's complex test with PostgreSQL, the following error
always occured.
duplicate key value violates unique constraint "%s"

It seems to be a bug which occurs when executing update,delete and
insert(within one transaction) the same row concurrently.

And i reproduced this issue via pgbench. Just as the following

1. prepare table & data
create table tb1(id int primary key,c int);
insert into tb1 values(1,1);

2. prepare test sql script
[postgres@localhost ~]$ cat test.sql
begin;
update tb1 set c=2 where id=1
delete from tb1 where id=1;
insert into tb1 values(1,2);
commit;

3. run test
[postgres@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. show log
[postgres@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;//*ERROR!*, Both process 57186 and 57187 had excuted "update tb1
set c=2 where id=1" successfully at the same time.
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);


I have tested PostgreSQL 9.3.1,9.4.5 and 9.5beta1,all of them has this
issue.

On Sunday, October 25, 2015, <chjischj@163.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13723
> Logged by:          ChenHuajun
> Email address:      chjischj@163.com <javascript:;>
> PostgreSQL version: 9.4.5
> Operating system:   CentOS 6
> Description:
>
> When i run sysbench's complex test with PostgreSQL, the following error
> always occured.
> duplicate key value violates unique constraint "%s"
>
> It seems to be a bug which occurs when executing update,delete and
> insert(within one transaction) the same row concurrently.
>
>
While I probably cannot explain this adequately it is your understanding of
concurrency and the various transaction isolation levels that is wrong; not
PostgreSQL.

David J.

chjischj@163.com writes:
> When i run sysbench's complex test with PostgreSQL, the following error
> always occured.
> duplicate key value violates unique constraint "%s"

> It seems to be a bug which occurs when executing update,delete and
> insert(within one transaction) the same row concurrently.

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

QXQgMjAxNS0xMC0yNSAyMzozODoyMywgIlRvbSBMYW5lIiA8dGdsQHNzcy5wZ2gucGEudXM+IHdy
b3RlOgoKPgo+SSBzZWUgbm8gYnVnIGhlcmU7IHlvdSdyZSBqdXN0IG1ha2luZyBhIG1pc3Rha2Vu
IGFzc3VtcHRpb24gYWJvdXQgaG93Cj5jcm9zcy10cmFuc2FjdGlvbiBzZXJpYWxpemF0aW9uIHdv
cmtzLiAgQXQgc29tZSBwb2ludCB5b3UncmUgZ29pbmcgdG8gZW5kCj51cCB3aXRoIGEgdGltaW5n
IGluIHdoaWNoIGJvdGggY2xpZW50cyBhcmUgdHJ5aW5nIHRvIGRvIHRoZSBERUxFVEUuICBPbmx5
Cj5vbmUgZG9lcyBpdDsgdGhlIG90aGVyIHdhaXRzIGZvciB0aGF0IHJvdyBjaGFuZ2UgdG8gY29t
bWl0LCBzZWVzIGl0J3MKPmRvbmUsIGFuZCBjb25jbHVkZXMgdGhhdCB0aGVyZSdzIG5vdGhpbmcg
Zm9yIGl0IHRvIGRvLiAgKEluIHBhcnRpY3VsYXIsCj5pdCB3aWxsIG5vdCBzZWUgdGhlIHJvdyB0
aGF0IHdhcyBpbnNlcnRlZCBsYXRlciBpbiB0aGUgb3RoZXIgdHJhbnNhY3Rpb24sCj5iZWNhdXNl
IHRoYXQncyB0b28gbmV3LikgIE5vdyB0aGUgc2Vjb25kIG9uZSdzIElOU0VSVCBmYWlscyBiZWNh
dXNlCj50aGVyZSdzIGFscmVhZHkgYSByb3cgd2l0aCBpZD0xLgo+Cj5JZiB5b3Ugd2FudCB0aGlz
IHNvcnQgb2YgY29kaW5nIHRvIGV4ZWN1dGUgc3RhYmx5LCB5b3UgY291bGQgY29uc2lkZXIKPnRh
a2luZyBvdXQgYSB0YWJsZS1sZXZlbCBsb2NrLCBvciBzb21lIG90aGVyIHdheSBvZiBwcmV2ZW50
aW5nIGNsaWVudHMKPmZyb20gY29uY3VycmVudGx5IGRlbGV0aW5nK2luc2VydGluZyB0aGUgc2Ft
ZSBrZXkuICBPciwganVzdCBkb24ndCBkbwo+dGhhdCBpbiB0aGUgZmlyc3QgcGxhY2UuCj4KCj4J
CQlyZWdhcmRzLCB0b20gbGFuZQoKCkluIG15IG9waW5pb24sIHRoZSBmaXJzdCB1cGRhdGUgc3Fs
IGluIHRoZSB0cmFuc2FjdGlvbiBzaG91bGQgb2J0YWluIGEgIkZPUiBOTyBLRVkgVVBEQVRFIiBS
b3ctbGV2ZWwgTG9jaywKQW5kICJGT1IgTk8gS0VZIFVQREFURSIgUm93LWxldmVsIExvY2sgaXMg
Y29uZmxpY3Rpbmcgd2l0aCBlYWNoIG90aGVyLApTbywgdGhlIHJlc3QgdHdvIHNxbHMoZGVsZXRl
IGFuZCBpbnNlcnQpIGluIHRoZSB0d28gdHJhbnNhY3Rpb25zIHNob3VsZCBiZSBleGVjdXRlZCBz
ZXF1ZW50aWFsbHkgaW5zdGVhZCBvZiBzaW11bHRhbmVvdXNseS4KCgpodHRwOi8vd3d3LnBvc3Rn
cmVzcWwub3JnL2RvY3MvOS40L3N0YXRpYy9leHBsaWNpdC1sb2NraW5nLmh0bWwjUk9XLUxPQ0st
Q09NUEFUSUJJTElUWQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLQpGT1IgVVBEQVRFCkZPUiBVUERBVEUgY2F1c2VzIHRoZSByb3dzIHJldHJpZXZlZCBi
eSB0aGUgU0VMRUNUIHN0YXRlbWVudCB0byBiZSBsb2NrZWQgYXMgdGhvdWdoIGZvciB1cGRhdGUu
IFRoaXMgcHJldmVudHMgdGhlbSBmcm9tIGJlaW5nIGxvY2tlZCwgbW9kaWZpZWQgb3IgZGVsZXRl
ZCBieSBvdGhlciB0cmFuc2FjdGlvbnMgdW50aWwgdGhlIGN1cnJlbnQgdHJhbnNhY3Rpb24gZW5k
cy4KLi4uCgpGT1IgTk8gS0VZIFVQREFURQouLi4KVGhpcyBsb2NrIG1vZGUgaXMgYWxzbyBhY3F1
aXJlZCBieSBhbnkgVVBEQVRFIHRoYXQgZG9lcyBub3QgYWNxdWlyZSBhIEZPUiBVUERBVEUgbG9j
ay4KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KCklz
IG15IHVuZGVyc3RhbmQgd3Jvbmc/CgoKQlRXOnRoZSBzaW1pbGFyIHByb2JsZW0oZGVhZGxvY2sp
CndoZW4gaSByZW1vdmVkIHRoZSBwcmltYXJ5IGtleSBjb25zdHJhaW50IGZyb20gdGIxIGFuZCBy
dW4gdGhlIGFib3ZlIHRlc3QgYWdhaW4sdGhlIGVycm9yIGJlY2FtZSAiZGVhZGxvY2siLgpwb3N0
Z3Jlcz0jIFxkIHRiMQogICAgICBUYWJsZSAicHVibGljLnRiMSIKIENvbHVtbiB8ICBUeXBlICAg
fCBNb2RpZmllcnMgCi0tLS0tLS0tKy0tLS0tLS0tLSstLS0tLS0tLS0tLQogaWQgICAgIHwgaW50
ZWdlciB8IAogYyAgICAgIHwgaW50ZWdlciB8CgoKVGhlIGVycm9yIGxvZzoKLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tCjIwMTUtMTAtMjUgMTg6NTA6MDkuNjc4IEVEVCA1NzY3NiAwIExP
RzogIHN0YXRlbWVudDogYmVnaW47CjIwMTUtMTAtMjUgMTg6NTA6MDkuNjc4IEVEVCA1NzY3NiAw
IExPRzogIHN0YXRlbWVudDogdXBkYXRlIHRiMSBzZXQgYz0yIHdoZXJlIGlkPTEKMjAxNS0xMC0y
NSAxODo1MDowOS42NzkgRURUIDU3Njc3IDAgTE9HOiAgc3RhdGVtZW50OiBiZWdpbjsKMjAxNS0x
MC0yNSAxODo1MDowOS42NzkgRURUIDU3Njc3IDAgTE9HOiAgc3RhdGVtZW50OiB1cGRhdGUgdGIx
IHNldCBjPTIgd2hlcmUgaWQ9MQoyMDE1LTEwLTI1IDE4OjUwOjA5LjY3OSBFRFQgNTc2NzYgMzk4
NTUgTE9HOiAgc3RhdGVtZW50OiBkZWxldGUgZnJvbSB0YjEgd2hlcmUgaWQ9MTsKMjAxNS0xMC0y
NSAxODo1MDoxMC42ODAgRURUIDU3Njc3IDM5ODU2IEVSUk9SOiAgZGVhZGxvY2sgZGV0ZWN0ZWQK
MjAxNS0xMC0yNSAxODo1MDoxMC42ODAgRURUIDU3Njc3IDM5ODU2IERFVEFJTDogIFByb2Nlc3Mg
NTc2Nzcgd2FpdHMgZm9yIFNoYXJlTG9jayBvbiB0cmFuc2FjdGlvbiAzOTg1NTsgYmxvY2tlZCBi
eSBwcm9jZXNzIDU3Njc2LgogICAgICAgIFByb2Nlc3MgNTc2NzYgd2FpdHMgZm9yIFNoYXJlTG9j
ayBvbiB0cmFuc2FjdGlvbiAzOTg1NjsgYmxvY2tlZCBieSBwcm9jZXNzIDU3Njc3LgogICAgICAg
IFByb2Nlc3MgNTc2Nzc6IHVwZGF0ZSB0YjEgc2V0IGM9MiB3aGVyZSBpZD0xCiAgICAgICAgUHJv
Y2VzcyA1NzY3NjogZGVsZXRlIGZyb20gdGIxIHdoZXJlIGlkPTE7CjIwMTUtMTAtMjUgMTg6NTA6
MTAuNjgwIEVEVCA1NzY3NyAzOTg1NiBISU5UOiAgU2VlIHNlcnZlciBsb2cgZm9yIHF1ZXJ5IGRl
dGFpbHMuCjIwMTUtMTAtMjUgMTg6NTA6MTAuNjgwIEVEVCA1NzY3NyAzOTg1NiBDT05URVhUOiAg
d2hpbGUgdXBkYXRpbmcgdHVwbGUgKDAsMjA1KSBpbiByZWxhdGlvbiAidGIxIgoyMDE1LTEwLTI1
IDE4OjUwOjEwLjY4MCBFRFQgNTc2NzcgMzk4NTYgU1RBVEVNRU5UOiAgdXBkYXRlIHRiMSBzZXQg
Yz0yIHdoZXJlIGlkPTEKMjAxNS0xMC0yNSAxODo1MDoxMC42ODAgRURUIDU3Njc2IDM5ODU1IExP
RzogIHN0YXRlbWVudDogaW5zZXJ0IGludG8gdGIxIHZhbHVlcygxLDIpOwoyMDE1LTEwLTI1IDE4
OjUwOjEwLjY4MSBFRFQgNTc2NzYgMzk4NTUgTE9HOiAgc3RhdGVtZW50OiBjb21taXQ7CgpBbmQg
aWYgc2VwYXJhdGVkIGRlbGV0aW5nIGFuZCBpbnNlcnRpbmcgdG8gdHdvIHRyYW5zYWN0aW9ucywg
ImRlYWRsb2NrIiBlcnJvciBzdGlsbCBvY3VycnMuCltwb3N0Z3Jlc0Bsb2NhbGhvc3Qgfl0kIGNh
dCB0ZXN0My5zcWwgCmJlZ2luOwp1cGRhdGUgdGIxIHNldCBjPTIgd2hlcmUgaWQ9MQpkZWxldGUg
ZnJvbSB0YjEgd2hlcmUgaWQ9MTsKY29tbWl0OwppbnNlcnQgaW50byB0YjEgdmFsdWVzKDEsMik7
Cltwb3N0Z3Jlc0Bsb2NhbGhvc3Qgfl0kIHBnYmVuY2ggLW4gLWYgdGVzdDMuc3FsIC1jIDIgLWog
MiAtdCAyMDAKY2xpZW50IDAgYWJvcnRlZCBpbiBzdGF0ZSAxOiBFUlJPUjogIGRlYWRsb2NrIGRl
dGVjdGVkCkRFVEFJTDogIFByb2Nlc3MgNTgwMDIgd2FpdHMgZm9yIFNoYXJlTG9jayBvbiB0cmFu
c2FjdGlvbiA3MjY0MDsgYmxvY2tlZCBieSBwcm9jZXNzIDU4MDAzLgpQcm9jZXNzIDU4MDAzIHdh
aXRzIGZvciBTaGFyZUxvY2sgb24gdHJhbnNhY3Rpb24gNzI2NDE7IGJsb2NrZWQgYnkgcHJvY2Vz
cyA1ODAwMi4KSElOVDogIFNlZSBzZXJ2ZXIgbG9nIGZvciBxdWVyeSBkZXRhaWxzLgpDT05URVhU
OiAgd2hpbGUgdXBkYXRpbmcgdHVwbGUgKDcsNCkgaW4gcmVsYXRpb24gInRiMSIKdHJhbnNhY3Rp
b24gdHlwZTogQ3VzdG9tIHF1ZXJ5CnNjYWxpbmcgZmFjdG9yOiAxCnF1ZXJ5IG1vZGU6IHNpbXBs
ZQpudW1iZXIgb2YgY2xpZW50czogMgpudW1iZXIgb2YgdGhyZWFkczogMgpudW1iZXIgb2YgdHJh
bnNhY3Rpb25zIHBlciBjbGllbnQ6IDIwMApudW1iZXIgb2YgdHJhbnNhY3Rpb25zIGFjdHVhbGx5
IHByb2Nlc3NlZDogMjAzLzQwMApsYXRlbmN5IGF2ZXJhZ2U6IDAuMDAwIG1zCnRwcyA9IDEyOC41
NDkyNzQgKGluY2x1ZGluZyBjb25uZWN0aW9ucyBlc3RhYmxpc2hpbmcpCnRwcyA9IDEyOC45MTc1
NzggKGV4Y2x1ZGluZyBjb25uZWN0aW9ucyBlc3RhYmxpc2hpbmcpCgoKCkJlc3QgUmVnYXJkcywK
Q2hlbiBIdWFqdW4KCg==

On Sun, Oct 25, 2015 at 1:28 PM, chenhj <chjischj@163.com> wrote:

> At 2015-10-25 23:38:23, "Tom Lane" <tgl@sss.pgh.pa.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 en=
d
> >up with a timing in which both clients are trying to do the DELETE.  Onl=
y
> >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=3D1.
> >
> >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 "F=
OR 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 b=
e 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 de=
leted 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?
>
> Yes.

Those locks you refer to are "EXPLICIT" locks.  If you want to take one you
have to write it into your query.

David J.
=E2=80=8B

RG9lcyBub3QgdGhlIGZvbGxvd2luZyBzdGF0ZW1lbnRzIGluIG1hbnVhbCBtZWFucyBhbnkgVVBE
QVRFIHNob3VsZCBhdXRvbWF0aWNhbGx5IGFjcXVpcmUgZXRoZXIgYSBGT1IgVVBEQVRFIG9yIGEg
Rk9SIE5PIEtFWSBVUERBVEUgUm93LWxldmVsIExvY2s/CgoKaHR0cDovL3d3dy5wb3N0Z3Jlc3Fs
Lm9yZy9kb2NzLzkuNC9zdGF0aWMvZXhwbGljaXQtbG9ja2luZy5odG1sI0xPQ0tJTkctUk9XUwoK
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KSW4gYWRkaXRp
b24gdG8gdGFibGUtbGV2ZWwgbG9ja3MsdGhlcmUgYXJlIHJvdy1sZXZlbCBsb2Nrcyx3aGljaCBh
cmUgbGlzdGVkIGFzIGJlbG93IHdpdGggdGhlIGNvbnRleHRzIGluIHdoaWNoIHRoZXkgYXJlIHVz
ZWQgYXV0b21hdGljYWxseSBieSBQb3N0Z3JlU1FMLi4uLgoKUm93LWxldmVsIExvY2sgTW9kZXMK
CkZPUiBVUERBVEUKCi4uLgoKVGhlIEZPUiBVUERBVEUgbG9jayBtb2RlIGlzIGFsc28gYWNxdWly
ZWQgYnkgYW55IERFTEVURSBvbiBhIHJvdywgYW5kIGFsc28gYnkgYW4gVVBEQVRFIHRoYXQgbW9k
aWZpZXMgdGhlIHZhbHVlcyBvbiBjZXJ0YWluIGNvbHVtbnMuIC4uLgoKLi4uCgoKRk9SIE5PIEtF
WSBVUERBVEUKCi4uLgoKVGhpcyBsb2NrIG1vZGUgaXMgYWxzbyBhY3F1aXJlZCBieSBhbnkgVVBE
QVRFIHRoYXQgZG9lcyBub3QgYWNxdWlyZSBhIEZPUiBVUERBVEUgbG9jay4KCi0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCgoKQmVzdCBSZWdhcnRzLApDaGVu
IEh1YWp1bgoKCgoKCk9uIDIwMTUtMTAtMjYgMDU6MzcgLCBEYXZpZCBHLiBKb2huc3RvbiBXcm90
ZToKCgpPbiBTdW4sIE9jdCAyNSwgMjAxNSBhdCAxOjI4IFBNLCBjaGVuaGogPGNoamlzY2hqQDE2
My5jb20+IHdyb3RlOgoKQXQgMjAxNS0xMC0yNSAyMzozODoyMywgIlRvbSBMYW5lIiA8dGdsQHNz
cy5wZ2gucGEudXM+IHdyb3RlOgoKPgo+SSBzZWUgbm8gYnVnIGhlcmU7IHlvdSdyZSBqdXN0IG1h
a2luZyBhIG1pc3Rha2VuIGFzc3VtcHRpb24gYWJvdXQgaG93Cj5jcm9zcy10cmFuc2FjdGlvbiBz
ZXJpYWxpemF0aW9uIHdvcmtzLiAgQXQgc29tZSBwb2ludCB5b3UncmUgZ29pbmcgdG8gZW5kCj51
cCB3aXRoIGEgdGltaW5nIGluIHdoaWNoIGJvdGggY2xpZW50cyBhcmUgdHJ5aW5nIHRvIGRvIHRo
ZSBERUxFVEUuICBPbmx5Cj5vbmUgZG9lcyBpdDsgdGhlIG90aGVyIHdhaXRzIGZvciB0aGF0IHJv
dyBjaGFuZ2UgdG8gY29tbWl0LCBzZWVzIGl0J3MKPmRvbmUsIGFuZCBjb25jbHVkZXMgdGhhdCB0
aGVyZSdzIG5vdGhpbmcgZm9yIGl0IHRvIGRvLiAgKEluIHBhcnRpY3VsYXIsCj5pdCB3aWxsIG5v
dCBzZWUgdGhlIHJvdyB0aGF0IHdhcyBpbnNlcnRlZCBsYXRlciBpbiB0aGUgb3RoZXIgdHJhbnNh
Y3Rpb24sCj5iZWNhdXNlIHRoYXQncyB0b28gbmV3LikgIE5vdyB0aGUgc2Vjb25kIG9uZSdzIElO
U0VSVCBmYWlscyBiZWNhdXNlCj50aGVyZSdzIGFscmVhZHkgYSByb3cgd2l0aCBpZD0xLgo+Cj5J
ZiB5b3Ugd2FudCB0aGlzIHNvcnQgb2YgY29kaW5nIHRvIGV4ZWN1dGUgc3RhYmx5LCB5b3UgY291
bGQgY29uc2lkZXIKPnRha2luZyBvdXQgYSB0YWJsZS1sZXZlbCBsb2NrLCBvciBzb21lIG90aGVy
IHdheSBvZiBwcmV2ZW50aW5nIGNsaWVudHMKPmZyb20gY29uY3VycmVudGx5IGRlbGV0aW5nK2lu
c2VydGluZyB0aGUgc2FtZSBrZXkuICBPciwganVzdCBkb24ndCBkbwo+dGhhdCBpbiB0aGUgZmly
c3QgcGxhY2UuCj4KCj4JCQlyZWdhcmRzLCB0b20gbGFuZQoKCkluIG15IG9waW5pb24sIHRoZSBm
aXJzdCB1cGRhdGUgc3FsIGluIHRoZSB0cmFuc2FjdGlvbiBzaG91bGQgb2J0YWluIGEgIkZPUiBO
TyBLRVkgVVBEQVRFIiBSb3ctbGV2ZWwgTG9jaywKQW5kICJGT1IgTk8gS0VZIFVQREFURSIgUm93
LWxldmVsIExvY2sgaXMgY29uZmxpY3Rpbmcgd2l0aCBlYWNoIG90aGVyLApTbywgdGhlIHJlc3Qg
dHdvIHNxbHMoZGVsZXRlIGFuZCBpbnNlcnQpIGluIHRoZSB0d28gdHJhbnNhY3Rpb25zIHNob3Vs
ZCBiZSBleGVjdXRlZCBzZXF1ZW50aWFsbHkgaW5zdGVhZCBvZiBzaW11bHRhbmVvdXNseS4KCgpo
dHRwOi8vd3d3LnBvc3RncmVzcWwub3JnL2RvY3MvOS40L3N0YXRpYy9leHBsaWNpdC1sb2NraW5n
Lmh0bWwjUk9XLUxPQ0stQ09NUEFUSUJJTElUWQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLQpGT1IgVVBEQVRFCkZPUiBVUERBVEUgY2F1c2VzIHRoZSBy
b3dzIHJldHJpZXZlZCBieSB0aGUgU0VMRUNUIHN0YXRlbWVudCB0byBiZSBsb2NrZWQgYXMgdGhv
dWdoIGZvciB1cGRhdGUuIFRoaXMgcHJldmVudHMgdGhlbSBmcm9tIGJlaW5nIGxvY2tlZCwgbW9k
aWZpZWQgb3IgZGVsZXRlZCBieSBvdGhlciB0cmFuc2FjdGlvbnMgdW50aWwgdGhlIGN1cnJlbnQg
dHJhbnNhY3Rpb24gZW5kcy4KLi4uCgpGT1IgTk8gS0VZIFVQREFURQouLi4KVGhpcyBsb2NrIG1v
ZGUgaXMgYWxzbyBhY3F1aXJlZCBieSBhbnkgVVBEQVRFIHRoYXQgZG9lcyBub3QgYWNxdWlyZSBh
IEZPUiBVUERBVEUgbG9jay4KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0KCklzIG15IHVuZGVyc3RhbmQgd3Jvbmc/Clllcy4KCgpUaG9zZSBsb2NrcyB5
b3UgcmVmZXIgdG8gYXJlICJFWFBMSUNJVCIgbG9ja3MuICBJZiB5b3Ugd2FudCB0byB0YWtlIG9u
ZSB5b3UgaGF2ZSB0byB3cml0ZSBpdCBpbnRvIHlvdXIgcXVlcnkuCgoKRGF2aWQgSi4K4oCLCiAK

On Sun, Oct 25, 2015 at 10:28 AM, chenhj <chjischj@163.com> wrote:
> At 2015-10-25 23:38:23, "Tom Lane" <tgl@sss.pgh.pa.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.

That is true only if the UPDATE actually updates the row.  If you log the
query completion tags, I think you will
find the error is preceded by an UPDATE of zero rows.  Now you could argue
that this itself is a violation: that if a single statement sees that a row
was deleted it is obliged to also see the row that was inserted in the same
transaction as the deletion.  But this is documented, and is unlikely to
change.

From http://www.postgresql.org/docs/current/static/transaction-iso.html

Because of the above rule, it is possible for an updating command to see an
> inconsistent snapshot: it can see the effects of concurrent updating
> commands on the same rows it is trying to update, but it does not see
> effects of those commands on other rows in the database. This behavior
> makes Read Committed mode unsuitable for commands that involve complex
> search conditions;


Can you reproduce the problem under a higher transaction isolation level?

Cheers,

Jeff

PkNhbiB5b3UgcmVwcm9kdWNlIHRoZSBwcm9ibGVtIHVuZGVyIGEgaGlnaGVyIHRyYW5zYWN0aW9u
IGlzb2xhdGlvbiBsZXZlbD8KPgo+Q2hlZXJzLAo+Cj5KZWZmCgoKVW5kZXIgaGlnaGVyIHRyYW5z
YWN0aW9uIGlzb2xhdGlvbiBsZXZlbChSRVBFQVRBQkxFIFJFQUQgb3IgU0VSSUFMSVpBQkxFKSwK
b25seSBvbmUgdHJhbnNhY3Rpb24ncyBVUERBVEUgY291bGQgYmUgZXhlY3V0ZWQsIHRoZSBzZWNv
bmQgdHJhbnNhY3Rpb24ncyBVUERBVEUgd2lsbCBiZSBibG9ja2VkIHV0aWwgdGhlIGZpcnN0IHRy
YW5zYWN0aW9uIGNvbW1pdHRlZCBhbmQgdGhlbiB0aHJvdyAiY291bGQgbm90IHNlcmlhbGl6ZSBh
Y2Nlc3MgZHVlIHRvIGNvbmN1cnJlbnQgdXBkYXRlIiBlcnJvci4KCgpbcG9zdGdyZXNAbG9jYWxo
b3N0IH5dJCBjYXQgdGVzdDUuc3FsCkJFR0lOIFRSQU5TQUNUSU9OIElTT0xBVElPTiBMRVZFTCBS
RVBFQVRBQkxFIFJFQUQ7CnVwZGF0ZSB0YjEgc2V0IGM9MiB3aGVyZSBpZD0xCmRlbGV0ZSBmcm9t
IHRiMSB3aGVyZSBpZD0xOwppbnNlcnQgaW50byB0YjEgdmFsdWVzKDEsMik7CmNvbW1pdDsKW3Bv
c3RncmVzQGxvY2FsaG9zdCB+XSQgcGdiZW5jaCAtbiAtYyAyIC1qIDIgLXQgMiAtZiB0ZXN0NS5z
cWwKY2xpZW50IDAgYWJvcnRlZCBpbiBzdGF0ZSAxOiBFUlJPUjogIGNvdWxkIG5vdCBzZXJpYWxp
emUgYWNjZXNzIGR1ZSB0byBjb25jdXJyZW50IHVwZGF0ZQouLi4KLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tCjIwMTUtMTAtMjUgMjI6NTU6MjUuNzE3IEVEVCAyMjAzIDAgTE9HOiAgY29ubmVj
dGlvbiByZWNlaXZlZDogaG9zdD1bbG9jYWxdCjIwMTUtMTAtMjUgMjI6NTU6MjUuNzE4IEVEVCAy
MjAzIDAgTE9HOiAgY29ubmVjdGlvbiBhdXRob3JpemVkOiB1c2VyPXBvc3RncmVzIGRhdGFiYXNl
PXBvc3RncmVzCjIwMTUtMTAtMjUgMjI6NTU6MjUuNzIyIEVEVCAyMjA1IDAgTE9HOiAgY29ubmVj
dGlvbiByZWNlaXZlZDogaG9zdD1bbG9jYWxdCjIwMTUtMTAtMjUgMjI6NTU6MjUuNzIzIEVEVCAy
MjA2IDAgTE9HOiAgY29ubmVjdGlvbiByZWNlaXZlZDogaG9zdD1bbG9jYWxdCjIwMTUtMTAtMjUg
MjI6NTU6MjUuNzIzIEVEVCAyMjA1IDAgTE9HOiAgY29ubmVjdGlvbiBhdXRob3JpemVkOiB1c2Vy
PXBvc3RncmVzIGRhdGFiYXNlPXBvc3RncmVzCjIwMTUtMTAtMjUgMjI6NTU6MjUuNzI0IEVEVCAy
MjA2IDAgTE9HOiAgY29ubmVjdGlvbiBhdXRob3JpemVkOiB1c2VyPXBvc3RncmVzIGRhdGFiYXNl
PXBvc3RncmVzCjIwMTUtMTAtMjUgMjI6NTU6MjUuNzI2IEVEVCAyMjA1IDAgTE9HOiAgc3RhdGVt
ZW50OiBCRUdJTiBUUkFOU0FDVElPTiBJU09MQVRJT04gTEVWRUwgUkVQRUFUQUJMRSBSRUFEOwoy
MDE1LTEwLTI1IDIyOjU1OjI1LjcyNiBFRFQgMjIwNiAwIExPRzogIHN0YXRlbWVudDogQkVHSU4g
VFJBTlNBQ1RJT04gSVNPTEFUSU9OIExFVkVMIFJFUEVBVEFCTEUgUkVBRDsKMjAxNS0xMC0yNSAy
Mjo1NToyNS43MjYgRURUIDIyMDYgMCBMT0c6ICBzdGF0ZW1lbnQ6IHVwZGF0ZSB0YjEgc2V0IGM9
MiB3aGVyZSBpZD0xCjIwMTUtMTAtMjUgMjI6NTU6MjUuNzI3IEVEVCAyMjA1IDAgTE9HOiAgc3Rh
dGVtZW50OiB1cGRhdGUgdGIxIHNldCBjPTIgd2hlcmUgaWQ9MQoyMDE1LTEwLTI1IDIyOjU1OjI1
LjcyOCBFRFQgMjIwNiA3OTUyMCBMT0c6ICBzdGF0ZW1lbnQ6IGRlbGV0ZSBmcm9tIHRiMSB3aGVy
ZSBpZD0xOwoyMDE1LTEwLTI1IDIyOjU1OjI1LjcyOCBFRFQgMjIwNiA3OTUyMCBMT0c6ICBzdGF0
ZW1lbnQ6IGluc2VydCBpbnRvIHRiMSB2YWx1ZXMoMSwyKTsKMjAxNS0xMC0yNSAyMjo1NToyNS43
MjggRURUIDIyMDYgNzk1MjAgTE9HOiAgc3RhdGVtZW50OiBjb21taXQ7CjIwMTUtMTAtMjUgMjI6
NTU6MjUuNzI5IEVEVCAyMjA2IDAgTE9HOiAgc3RhdGVtZW50OiBCRUdJTiBUUkFOU0FDVElPTiBJ
U09MQVRJT04gTEVWRUwgUkVQRUFUQUJMRSBSRUFEOwoyMDE1LTEwLTI1IDIyOjU1OjI1LjcyOSBF
RFQgMjIwNSA3OTUyMSBFUlJPUjogIGNvdWxkIG5vdCBzZXJpYWxpemUgYWNjZXNzIGR1ZSB0byBj
b25jdXJyZW50IHVwZGF0ZQoyMDE1LTEwLTI1IDIyOjU1OjI1LjcyOSBFRFQgMjIwNSA3OTUyMSBT
VEFURU1FTlQ6ICB1cGRhdGUgdGIxIHNldCBjPTIgd2hlcmUgaWQ9MQoyMDE1LTEwLTI1IDIyOjU1
OjI1LjczMCBFRFQgMjIwNiAwIExPRzogIHN0YXRlbWVudDogdXBkYXRlIHRiMSBzZXQgYz0yIHdo
ZXJlIGlkPTEKMjAxNS0xMC0yNSAyMjo1NToyNS43MzAgRURUIDIyMDYgNzk1MjIgTE9HOiAgc3Rh
dGVtZW50OiBkZWxldGUgZnJvbSB0YjEgd2hlcmUgaWQ9MTsKMjAxNS0xMC0yNSAyMjo1NToyNS43
MzAgRURUIDIyMDYgNzk1MjIgTE9HOiAgc3RhdGVtZW50OiBpbnNlcnQgaW50byB0YjEgdmFsdWVz
KDEsMik7CjIwMTUtMTAtMjUgMjI6NTU6MjUuNzMwIEVEVCAyMjA2IDc5NTIyIExPRzogIHN0YXRl
bWVudDogY29tbWl0OwoKCkJlc3QgUmVnYXJkcywKQ2hlbiBIdWFqdW4KCgoKCgo=

On Mon, Oct 26, 2015 at 10:47 AM, chenhj <chjischj@163.com> wrote:

> >Can you reproduce the problem under a higher transaction isolation level?
> >
> >Cheers,
> >
> >Jeff
>
> Under higher transaction isolation level(REPEATABLE READ or SERIALIZABLE),
> only one transaction's UPDATE could be executed, the second transaction's
> UPDATE will be blocked util the first transaction committed and then
> throw "could not serialize access due to concurrent update" error.
>

Yes, this is the correct  behavior.  It can't serialize, so it gives an
error which the user can then decide how to handle (simply retry the same
transaction would probably be best, in this case).

It would be nice if pgbench had a mode where it would do the retry for you
upon retryable errors, but right now it doesn't.

Cheers,

Jeff

SGksSmVmZgoKCj4gVGhhdCBpcyB0cnVlIG9ubHkgaWYgdGhlIFVQREFURSBhY3R1YWxseSB1cGRh
dGVzIHRoZSByb3cuICBJZiB5b3UgbG9nIHRoZSBxdWVyeSBjb21wbGV0aW9uIHRhZ3MsIEkgdGhp
bmsgeW91IHdpbGwKPiBmaW5kIHRoZSBlcnJvciBpcyBwcmVjZWRlZCBieSBhbiBVUERBVEUgb2Yg
emVybyByb3dzLiAgTm93IHlvdSBjb3VsZCBhcmd1ZSB0aGF0IHRoaXMgaXRzZWxmIGlzIGEgdmlv
bGF0aW9uOiAKPiB0aGF0IGlmIGEgc2luZ2xlIHN0YXRlbWVudCBzZWVzIHRoYXQgYSByb3cgd2Fz
IGRlbGV0ZWQgaXQgaXMgb2JsaWdlZCB0byBhbHNvIHNlZSB0aGUgcm93IHRoYXQgd2FzIGluc2Vy
dGVkIAo+IGluIHRoZSBzYW1lIHRyYW5zYWN0aW9uIGFzIHRoZSBkZWxldGlvbi4gIEJ1dCB0aGlz
IGlzIGRvY3VtZW50ZWQsIGFuZCBpcyB1bmxpa2VseSB0byBjaGFuZ2UuCgoKTXkgdGVzdCByZXN1
bHQgaXMganVzdCBhcyB5b3Ugc2FpZCwgYXMgdGhlIGZvbGxvd2luZzoKCgp0ZXN0OS5zcWw6Ci0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQpiZWdpbjsKc2VsZWN0ICogZnJv
bSB0YjEgd2hlcmUgaWQ9MTsKdXBkYXRlIHRiMSBzZXQgYz0yIHdoZXJlIGlkPTE7CmRlbGV0ZSBm
cm9tIHRiMSB3aGVyZSBpZD0xOwppbnNlcnQgaW50byB0YjEgdmFsdWVzKDEsMik7CmNvbW1pdDsK
YmVnaW47CnNlbGVjdCAqIGZyb20gdGIxIHdoZXJlIGlkPTE7CnVwZGF0ZSB0YjEgc2V0IGM9MiB3
aGVyZSBpZD0xOwpkZWxldGUgZnJvbSB0YjEgd2hlcmUgaWQ9MTsKaW5zZXJ0IGludG8gdGIxIHZh
bHVlcygxLDIpOwpjb21taXQ7Ci4uLgotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0KCgpbcG9zdGdyZXNAbG9jYWxob3N0IH5dJCBwc3FsIC1mIHRlc3Q5LnNxbCA+YTEubG9n
IDI+JjEgJgpbcG9zdGdyZXNAbG9jYWxob3N0IH5dJCBwc3FsIC1mIHRlc3Q5LnNxbCA+YTIubG9n
ICAyPiYxICYKCgphMi5sb2c6Ci0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LQouLi4KQkVHSU4KIGlkIHwgYwotLS0tKy0tLQogIDEgfCAyCigxIHJvdykKCgpVUERBVEUgMApE
RUxFVEUgMApwc3FsOnRlc3Q5LnNxbDoxNzogRVJST1I6ICBkdXBsaWNhdGUga2V5IHZhbHVlIHZp
b2xhdGVzIHVuaXF1ZSBjb25zdHJhaW50ICJ0YjFfcGtleSIKREVUQUlMOiAgS2V5IChpZCk9KDEp
IGFscmVhZHkgZXhpc3RzLgpST0xMQkFDSwouLi4KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tCgoKCgpFdmVuICJFWFBMSUNJVExZIiBhZGQgInNlbGVjdCAuLi4gZm9yIHVw
ZGF0ZSIgYXQgZmlyc3QsICJkdXBsaWNhdGUga2V5ICIgZXJyb3Igc3RpbGwgb2N1cnJzLgoKCnRl
c3QxMC5zcWw6Ci0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQpiZWdpbjsK
c2VsZWN0ICogZnJvbSB0YjEgd2hlcmUgaWQ9MSBmb3IgdXBkYXRlOwp1cGRhdGUgdGIxIHNldCBj
PTIgd2hlcmUgaWQ9MTsKZGVsZXRlIGZyb20gdGIxIHdoZXJlIGlkPTE7Cmluc2VydCBpbnRvIHRi
MSB2YWx1ZXMoMSwyKTsKY29tbWl0OwpiZWdpbjsKc2VsZWN0ICogZnJvbSB0YjEgd2hlcmUgaWQ9
MSBmb3IgdXBkYXRlOwp1cGRhdGUgdGIxIHNldCBjPTIgd2hlcmUgaWQ9MTsKZGVsZXRlIGZyb20g
dGIxIHdoZXJlIGlkPTE7Cmluc2VydCBpbnRvIHRiMSB2YWx1ZXMoMSwyKTsKY29tbWl0OwouLi4K
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCgoKW3Bvc3RncmVzQGxvY2Fs
aG9zdCB+XSQgcHNxbCAtZiB0ZXN0MTAuc3FsID5iMS5sb2cgMj4mMSAmCltwb3N0Z3Jlc0Bsb2Nh
bGhvc3Qgfl0kIHBzcWwgLWYgdGVzdDEwLnNxbCA+YjIubG9nICAyPiYxICYKCgpiMS5sb2c6Ci0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQouLi4KQkVHSU4KIGlkIHwgYwot
LS0tKy0tLQooMCByb3dzKQoKClVQREFURSAwCkRFTEVURSAwCnBzcWw6dGVzdDEwLnNxbDoyOTog
RVJST1I6ICBkdXBsaWNhdGUga2V5IHZhbHVlIHZpb2xhdGVzIHVuaXF1ZSBjb25zdHJhaW50ICJ0
YjFfcGtleSIKREVUQUlMOiAgS2V5IChpZCk9KDEpIGFscmVhZHkgZXhpc3RzLgpST0xMQkFDSwou
Li4KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCgoKU28sICJzZWxlY3Qg
KiBmcm9tIHRiMSB3aGVyZSBpZD0xIGZvciB1cGRhdGUiIGFsc28gY291bGQgc2VlIGFuIGluY29u
c2lzdGVudCBzbmFwc2hvdCAoc29ydCBvZiAiRGlydHkgUmVhZCIpLgoKCgoKQmVzdCBSZWdhcmQs
CkNoZW4gSHVhanVu