Re: NT + deadlock intended behaviour ?

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.

>
> 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.

> 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.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Now I have my system running, not a byte was off the shelf;
It rarely breaks and when it does I fix the code myself.
It's stable, clean and elegant, and lightning fast as well,
And it doesn't cost a nickel, so Bill Gates can go to hell."