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

From: tim(dot)channell(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #10315: Transactions seem to be releasing locks early?
Date: 2014-05-13 20:51:37
Message-ID: 20140513205137.11846.86900@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 10315
Logged by: Tim Channell
Email address: tim(dot)channell(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G Johnston 2014-05-13 21:36:55 Re: BUG #10315: Transactions seem to be releasing locks early?
Previous Message Tom Lane 2014-05-13 14:33:11 Re: hstore dump/restore bug in 9.3