Re: NT + deadlock intended behaviour ? - Mailing list pgsql-hackers

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