Thread: 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. +