Обсуждение: BUG #10315: Transactions seem to be releasing locks early?

The following bug has been logged on the website:

Bug reference:      10315
Logged by:          Tim Channell
Email address:      tim.channell@gmail.com
PostgreSQL version: 9.3.4
Operating system:   Archlinux
Description:

It seems that sometimes transactions (tested in READ COMMITTED mode, no
manual locks) are releasing locks prematurely. Or something else wonky is
happening. Here's my test

1. Create a table. I just did

    CREATE TABLE test (id int);
    INSERT INTO test (id) values(1),(2);

2. Open two psql terminals. Issue BEGIN TRANSACTION in both.

3. In the first psql, issue

    DELETE FROM test WHERE id = 2;

that should show "DELETE 1" as the result.

4. Repeat #3 in the second psql terminal. It should hang (waiting for lock
to release). This is expected.

5. Now, in the first psql, issue

    INSERT INTO test (id) VALUES(2);

6. In the first psql, issue COMMIT;

7. Back in the second transaction, our DELETE has executed because the lock
was released. It would be expected to show "DELETE 1", because the first
transaction re-inserted the deleted record. But, it shows DELETE 0.


This all leads me to believe that the DELETE exclusive lock lifts BEFORE the
insert statement in the first transaction actually succeeds.

tim.channell wrote
> The following bug has been logged on the website:
>
> Bug reference:      10315
> Logged by:          Tim Channell
> Email address:

> tim.channell@

> PostgreSQL version: 9.3.4
> Operating system:   Archlinux
> Description:
>
> It seems that sometimes transactions (tested in READ COMMITTED mode, no
> manual locks) are releasing locks prematurely. Or something else wonky is
> happening. Here's my test
>
> 1. Create a table. I just did
>
>     CREATE TABLE test (id int);
>     INSERT INTO test (id) values(1),(2);
>
> 2. Open two psql terminals. Issue BEGIN TRANSACTION in both.
>
> 3. In the first psql, issue
>
>     DELETE FROM test WHERE id = 2;
>
> that should show "DELETE 1" as the result.
>
> 4. Repeat #3 in the second psql terminal. It should hang (waiting for lock
> to release). This is expected.
>
> 5. Now, in the first psql, issue
>
>     INSERT INTO test (id) VALUES(2);
>
> 6. In the first psql, issue COMMIT;
>
> 7. Back in the second transaction, our DELETE has executed because the
> lock
> was released. It would be expected to show "DELETE 1", because the first
> transaction re-inserted the deleted record. But, it shows DELETE 0.
>
> This all leads me to believe that the DELETE exclusive lock lifts BEFORE
> the
> insert statement in the first transaction actually succeeds.

From:

http://www.postgresql.org/docs/9.3/interactive/transaction-iso.html#XACT-READ-COMMITTED

"When a transaction uses this isolation level, a SELECT query (without a FOR
UPDATE/SHARE clause) sees only data committed before the query began; it
never sees either uncommitted data or changes committed during query
execution by concurrent transactions."

As soon as you execute "DELETE" in psql-2 only physical records that exist
at that moment are visible to that statement.  Due to the DELETE in psql-1
that physical record with ID of 2 that existed before no longer exists.  The
locking makes it so psql-2 can see the effects of the delete as soon as
psql-1 commits.  However, the record you inserted in step 5 comes into
existence after the psql-2 DELETE and so cannot be seen by it - this is a
consequence of "...changes committed during query execution by concurrent
transaction."

Maybe someone else can clarify and confirm but basically even though you
had, and have again, a record record with the same ID they exist in
different times and the concurrent psql-2 can only see one of them - in this
case the one that was subsequently deleted in psql-1.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-10315-Transactions-seem-to-be-releasing-locks-early-tp5803812p5803816.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On Tue, May 13, 2014 at 9:51 PM,  <tim.channell@gmail.com> wrote:
> 7. Back in the second transaction, our DELETE has executed because the lock
> was released. It would be expected to show "DELETE 1", because the first
> transaction re-inserted the deleted record. But, it shows DELETE 0.

It can't see the second row because it was inserted after the delete
query started.

I think in SERIALIZABLE mode it would be flagged as a serialization
failure but I'm not sure.

--
greg

On Wed, May 14, 2014 at 12:21 PM, Greg Stark <stark@mit.edu> wrote:
> On Tue, May 13, 2014 at 9:51 PM,  <tim.channell@gmail.com> wrote:
>> 7. Back in the second transaction, our DELETE has executed because the lock
>> was released. It would be expected to show "DELETE 1", because the first
>> transaction re-inserted the deleted record. But, it shows DELETE 0.
>
> It can't see the second row because it was inserted after the delete
> query started.
>
> I think in SERIALIZABLE mode it would be flagged as a serialization
> failure but I'm not sure.
Yep because session 1 has done a concurrent update on the row session
2 is trying to delete. You would actually get something like that:
=# DELETE FROM test WHERE id = 2;
ERROR:  40001: could not serialize access due to concurrent update
LOCATION:  ExecDelete, nodeModifyTable.c:447
--
Michael

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 to
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 occurred
after the entire first transaction was complete, not immediately after the
particular operation that locked the row

Here's the relevant documentation:

 However, such a target row might have already been updated (or deleted or
locked) by another concurrent transaction by the time it is found. In this
case, the would-be updater will wait for the first updating transaction to
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 updated
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 the
search condition. If so, the second updater proceeds with its operation
using the updated version of the row

On Wed, May 14, 2014 at 8:06 AM, Michael Paquier
<michael.paquier@gmail.com>wrote:

>
> Yep because session 1 has done a concurrent update on the row session
> 2 is trying to delete. You would actually get something like that:
> =# DELETE FROM test WHERE id = 2;
> ERROR:  40001: could not serialize access due to concurrent update
> LOCATION:  ExecDelete, nodeModifyTable.c:447
> --
> Michael
>

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.

On Wed, May 14, 2014 at 12:15:13PM -0400, Tim Channell wrote:
> Here's the relevant documentation: 
>
>  However, such a target row might have already been updated (or deleted or
> locked) by another concurrent transaction by the time it is found. In this
> case, the would-be updater will wait for the first updating transaction to
> 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 updated 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 the search condition. If
> so, the second updater proceeds with its operation using the updated version of
> the row

I think you want to read about this example:

    http://www.postgresql.org/docs/9.3/static/transaction-iso.html#XACT-READ-COMMITTED

    BEGIN;
    UPDATE website SET hits = hits + 1;
    -- run from another session:  DELETE FROM website WHERE hits = 10;
    COMMIT;

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +