Re: NT + deadlock intended behaviour ?

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