Alvaro Herrera wrote:
On Sun, Jul 18, 2004 at 01:06:39AM +0200, Gaetano Mendola wrote:
I'm doing some experiments with NT, I din't expect this behaviuor:
First of all, let me point that the behavior on deadlock has been agreed
to change. Instead of only aborting the innermost transaction, it will
abort the whole transaction tree.
The reason is simple. Consider this case:
create table foo (a int);
insert into test values (1);
insert into test values (2);
begin;
update foo set a=20 where a=1;
begin;
update foo set a=21 where a=2;
begin;
update foo set a=22 where a=2;
<LOCKED> begin;
update foo set a=23 where a=1;
<DEADLOCK DETECTED>
If I abort only the innermost transaction on session 2, the application
writer can have a retry loop on it, so it will issue the "begin" again
and the same update. Since session 1 is still locked, session 2 will
see a deadlock again. The user could cope with detecting a deadlock
condition and do something else, but frankly I don't think we can leave
this as is.
I understand your point but I don't like the solution of invalidate the whole
transaction tree ( I don't know the good one ).
See also my comment at the end of this reply.
SESSION 1; SESSION 2;
begin; begin;
update test set a = 300 where a = 3; update test set a = 40 where a = 4;
~ begin;
update test set a = 400 where a = 4;
<BLOCKED>
~ update test set a = 30 where a = 3;
~ <DEAD LOCK DETECTED>
~ commit;
<UNBLOCKED> <-- !?!?!
~ <here I'm able to do another commit>
why SESSION 1 was unblocked?
Because when you COMMIT a subtransaction that was in aborted state, the
parent is aborted too. So when you COMMIT you are not really
committing, you are aborting. That gives session 1 green light to
continue, because session 2 has released all locks.
So why the second commit on SESSION 2 works without complain about the fact
that there is no transaction active to commit ?
I think the first commit have to fail because the transaction is aborted
( I know this was discussed before ).
If I repeat again but I do an abort:
SESSION 1; SESSION 2;
begin; begin;
update test set a = 300 where a = 3; update test set a = 40 where a = 4;
~ begin;
update test set a = 400 where a = 4;
<BLOCKED>
~ update test set a = 30 where a = 3;
~ <DEAD LOCK DETECTED>
~ abort;
<STILL BLOCKED>
This is what you expected, wasn't it? When you ABORTed the
subtransaction, the parent did not abort, so it held it locks. So
session 1 does not have the lock it needs.
This is what I was expecting; here we are in the same situation of your example,
what happen if the application open another transaction and try to update the
same row ?
Regards
Gaetano Mendola
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
