FAQ
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

Search Discussions

  • Alvaro Herrera at Jul 18, 2004 at 3:04 am

    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."
  • Tom Lane at Jul 18, 2004 at 5:16 am

    Alvaro Herrera 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
  • Alvaro Herrera at Jul 18, 2004 at 5:33 am

    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 at Jul 18, 2004 at 9:00 am

    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 at Jul 18, 2004 at 5:24 pm

    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)
  • Tom Lane at Jul 18, 2004 at 5:38 am

    Gaetano Mendola 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
  • Alvaro Herrera at Jul 18, 2004 at 5:51 am

    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)
  • Gaetano Mendola at Jul 18, 2004 at 9:06 am

    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
  • Alvaro Herrera at Jul 18, 2004 at 5:45 pm

    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)
  • Gaetano Mendola at Jul 21, 2004 at 3:15 am
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 17, '04 at 11:06p
activeJul 21, '04 at 3:15a
posts11
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase