Re: Strange result using transactions

On Tue, Mar 27, 2007 at 12:41:53PM +0200, Matthijs Melissen wrote:
> I get DELETE 0 even if a record with id=20 already exists before both
> transactions.

Transaction 2 (T2) is deleting the version of the row with id = 20
that was visible to T2 when it executed its DELETE. Since T1 deleted
that version of the row first, T2 finds no row to delete after T1
commits and releases its locks. T2 doesn't know about the row that
T1 inserted because T1 hadn't committed yet when T2 executed its
DELETE.

Run T2 as a Serializable transaction and you'll see different
behavior:

1) begin;
1) delete from forum where id = 20;
1) insert into forum (id, name) values (20, 'test');
2) begin isolation level serializable;
2) delete from forum where id = 20;
1) commit;

When T1 commits T2 should fail with SQLSTATE 40001 SERIALIZATION
FAILURE ("could not serialize access due to concurrent update").
T2 still doesn't know about the row that T1 inserted but now T2
knows that something happened to the version of the row it was
trying to delete.

--
Michael Fuhr