On Wed, May 14, 2014 at 1:54 PM, tim.channell [via PostgreSQL] <
ml-node+s1045698n5803941h95@n5.nabble.com> wrote:
> Yes, that's what happens in serializable. I guess my understanding was
> that by nature, transactions are atomic, so that the first transaction's
> DELETE and INSERT happened both before the second transaction gets back t=
o
> running. And according to documentation, if there's a conflict in READ
> COMMITTED, the waiting transaction will rescan for changes made by the
> transaction it was waiting for. So my assumption was that re-scan occurre=
d
> after the entire first transaction was complete, not immediately after th=
e
> particular operation that locked the row
>
> Here's the relevant documentation:
>
> However, such a target row might have already been updated (or deleted o=
r
> locked) by another concurrent transaction by the time it is found. In thi=
s
> case, the would-be updater will wait for the first updating transaction t=
o
> commit or roll back (if it is still in progress)... If the first updater
> commits, the second updater will ignore the row if the first updater
> deleted it, otherwise it will attempt to apply its operation to the updat=
ed
> version of the row. The search condition of the command (the WHERE clause=
)
> is re-evaluated to see if the updated version of the row still matches th=
e
> search condition. If so, the second updater proceeds with its operation
> using the updated version of the row
>
>
=E2=80=8Bpsql-1 is updating=E2=80=8B
=E2=80=8Bpsql-2 is the updater=E2=80=8B
=E2=80=8Bpsql-2 waits for psql-1 to finish and then re-scans and ignores th=
e row
psql-1 deleted.
This does bring up a good point, though:
If the original row is simply updated this reads like psql-2 would be able
to see the updated values. But if the row is deleted, then a new row added
separately, psql-2 is unable to recognize the fact that the two entries are
logically - though not physically - related. Thus even though MVCC affects
an UPDATE with a DELETE+INSERT pair there is additional logic (such at HOT
update) that links the two records together which an explicit DELETE &
INSERT =E2=80=8Bloses.
Each statement is provided a snapshot as soon as it is executed -
necessarily before locking happens - and can only ever see records present
at the time that snapshot was taken. Those actual records could be updated
or deleted, in which case those changes are visible, but any data INSERTed
after the statement begins are invisible because an explicit INSERT always
creates a new actual record.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-1=
0315-Transactions-seem-to-be-releasing-locks-early-tp5803812p5803945.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
