Thread: NT + deadlock intended behaviour ?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi all, I'm doing some experiments with NT, I din't expect this behaviuor: create table test ( a integer ); insert into test values (3); insert into test values (4); insert into test values (5); insert into test values (6); 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 ? 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> Why that commit unblock the SESSION 1? Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA+bD+7UpzwH2SGd4RAq0VAJ9rZQ3aJmsJM6WSlLqIERJzDDS9iQCeL5rT rF7PkCaJ59PWNQw4CK6uvug= =Rb3s -----END PGP SIGNATURE-----
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."
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> 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.
Who agreed to that? Your example is entirely unconvincing --- deadlock
is very far from being the only failure that will recur indefinitely,
if an app writer is so foolish as to code an indefinite retry loop.
Any simple illegal-data-value error will act the same.
I do not think declaring by fiat that certain types of errors abort the
whole tree is acceptable from the user end or reasonable from the
implementation end.
regards, tom lane
On Sun, Jul 18, 2004 at 01:16:17AM -0400, Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > > 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. > > Who agreed to that? Huh? I showed this example to Bruce on IRC several days ago, while you were away -- he said (or at least I understood) that he talked to you and you agreed to this behavior. Maybe I was confused about what he said. This is a small change from the implementation POV anyway (two lines patch). -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El número de instalaciones de UNIX se ha elevado a 10, y se espera que este número aumente" (UPM, 1972)
Gaetano Mendola <mendola@bigfoot.com> writes:
> why SESSION 1 was unblocked ?
> ...
> Why that commit unblock the SESSION 1?
IMHO session 1 should have been unblocked in both cases as soon as
session 2's subtransaction failed. We have always made a practice
of releasing a transaction's locks immediately upon failure.
The reason it does not seem to act that way is that Alvaro's taken a
shortcut in WaitForTransaction: subtransactions do not take out a
separate transaction lock and so WaitForTransaction has to wait for the
top-level transaction. Your sub-COMMIT fails the outer transaction
(because the inner one is failed) and so the top transaction lock
releases at that point. In the sub-ABORT case the outer transaction
stays good and continues to hold its lock.
I've already suggested that this shortcut is no good, and now I'm
pretty sure of it ...
regards, tom lane
On Sun, Jul 18, 2004 at 01:38:57AM -0400, Tom Lane wrote: > Gaetano Mendola <mendola@bigfoot.com> writes: > > why SESSION 1 was unblocked ? > > ... > > Why that commit unblock the SESSION 1? > > IMHO session 1 should have been unblocked in both cases as soon as > session 2's subtransaction failed. We have always made a practice > of releasing a transaction's locks immediately upon failure. > > The reason it does not seem to act that way is that Alvaro's taken a > shortcut in WaitForTransaction: subtransactions do not take out a > separate transaction lock and so WaitForTransaction has to wait for the > top-level transaction. Your sub-COMMIT fails the outer transaction > (because the inner one is failed) and so the top transaction lock > releases at that point. In the sub-ABORT case the outer transaction > stays good and continues to hold its lock. > > I've already suggested that this shortcut is no good, and now I'm > pretty sure of it ... FYI, this is no longer the case with the savepoints patch I just posted. It's too late here to check if this solves Gaetano concerns. Gaetano, please apply the latest savepoints patch (savepoint-5.patch) and let me know how it goes ... -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Ni aun el genio muy grande llegaría muy lejos si tuviera que sacarlo todo de su propio interior" (Goethe)
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
Alvaro Herrera wrote: > Gaetano, please apply the latest savepoints patch (savepoint-5.patch) > and let me know how it goes ... where is it ? Regards Gaetano Mendola
On Sun, Jul 18, 2004 at 11:00:25AM +0200, Gaetano Mendola wrote: > Alvaro Herrera wrote: > >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 ). FYI, this is all moot with savepoints because we don't allow to create a savepoint in an aborted transaction block. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El sudor es la mejor cura para un pensamiento enfermo" (Bardia)
On Sun, Jul 18, 2004 at 11:06:19AM +0200, Gaetano Mendola wrote: > Alvaro Herrera wrote: > > >Gaetano, please apply the latest savepoints patch (savepoint-5.patch) > >and let me know how it goes ... > > where is it ? I just sent it by private mail to you (11kb). I don't see it in the archives ... -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Oh, oh, las chicas galacianas, lo harán por las perlas, ¡Y las de Arrakis por el agua! Pero si buscas damas Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)