Re: BUG #10315: Transactions seem to be releasing locks early?

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.