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.
