Re: [HACKERS] NT + deadlock intended behaviour ?
On Sun, Jul 18, 2004 at 01:38:57AM -0400, Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] 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) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 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 ---(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
Re: [HACKERS] NT + deadlock intended behaviour ?
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] NT + deadlock intended behaviour ?
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) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] NT + deadlock intended behaviour ?
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) ---(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
Re: [HACKERS] 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] NT + deadlock intended behaviour ?
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] NT + deadlock intended behaviour ?
On Sun, Jul 18, 2004 at 01:16:17AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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) ---(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
Re: [HACKERS] NT + deadlock intended behaviour ?
Gaetano Mendola [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
