Strange result using transactions

I am executing the following queries (id has a unique key):

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

If you do these side by side in interactive psql sessions, you'll see that the
process 2) delete from forum where id=20; waits and waits and doesn't start
until you commiut process 1). So from the point of view of Process 2,
it sees the committed forum table and it has no entries in the forum table with
id 20, hence DELETE 0 is its output.

So the effective sequence of events is:
1) begin;
1) delete from forum where id = 20;
1) insert into forum (id, name) values (20, 'test');
1) commit;
2) delete from forum where id = 20;

Hope this helps,
Stuart.

"Matthijs Melissen" <melissen@phil.uu.nl> writes:

I am executing the following queries (id has a unique key):
1) begin;
1) delete from forum where id = 20;
1) insert into forum (id, name) values (20, 'test');
2) delete from forum where id = 20;
1) commit;

The problem is that process 2 gets the message 'DELETE 0'. I would expect
him to get the message 'DELETE 1'.

Why do you find that strange? Process 1 hasn't committed its insert yet.

regards, tom lane

On 3/27/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Matthijs Melissen" <melissen@phil.uu.nl> writes:

I am executing the following queries (id has a unique key):
1) begin;
1) delete from forum where id = 20;
1) insert into forum (id, name) values (20, 'test');
2) delete from forum where id = 20;
1) commit;

The problem is that process 2 gets the message 'DELETE 0'. I would

expect

him to get the message 'DELETE 1'.

Why do you find that strange? Process 1 hasn't committed its insert yet.

I think what he is saying that *after* txn 1 commits, txn 2 does not see the
record inserted by txn1. Isn't that a fair point ? I mean txn 2 can see the
DELETE operation of txn 1, but can not see the INSERT operation of the
same transaction.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com

Pavan Deolasee wrote:

On 3/27/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Matthijs Melissen" <melissen@phil.uu.nl> writes:

I am executing the following queries (id has a unique key):
1) begin;
1) delete from forum where id = 20;
1) insert into forum (id, name) values (20, 'test');
2) delete from forum where id = 20;
1) commit;

The problem is that process 2 gets the message 'DELETE 0'. I would

expect

him to get the message 'DELETE 1'.

Why do you find that strange? Process 1 hasn't committed its insert yet.

I think what he is saying that *after* txn 1 commits, txn 2 does not see
the
record inserted by txn1. Isn't that a fair point ? I mean txn 2 can see the
DELETE operation of txn 1, but can not see the INSERT operation of the
same transaction.

Not necessarily so. I'd expect only to see a successful delete in txn 2
if a record with id = 20 existed prior to the beginning of txn 1. If
that wasn't the case, then there's nothing to delete in txn 2.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

Alban Hertroys wrote:

Pavan Deolasee wrote:

On 3/27/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Matthijs Melissen" <melissen@phil.uu.nl> writes:

I am executing the following queries (id has a unique key):
1) begin;
1) delete from forum where id = 20;
1) insert into forum (id, name) values (20, 'test');
2) delete from forum where id = 20;
1) commit;

The problem is that process 2 gets the message 'DELETE 0'. I would
expect him to get the message 'DELETE 1'.

Not necessarily so. I'd expect only to see a successful delete in txn
2 if a record with id = 20 existed prior to the beginning of txn 1.

That is exactly the problem. Sorry for not being clear about that.
I get DELETE 0 even if a record with id=20 already exists before both
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