Loading ...
Sorry, an error occurred while loading the content.

Apache::Session::Store::Postgres FOR UPDATE problems

Expand Messages
  • Kjetil Kjernsmo
    Honored MPs! I ve taken over maintenance of AxKit::XSP::BasicSession from Mike Nachbaur, but Mike still contributes and we ve been discussing some problems
    Message 1 of 6 , Apr 29, 2005
    • 0 Attachment
      Honored MPs!

      I've taken over maintenance of AxKit::XSP::BasicSession from Mike
      Nachbaur, but Mike still contributes and we've been discussing some
      problems with it over the past few days.

      A:X:BS lets Apache::Session do most of its hard work. However, it seems
      like no users have ever gotten it to work with a PostgreSQL backend.
      I'm trying to address that now.

      On the surface, the symptom is that for certain pages, it will just sit
      and spin at the $self->{materialize_sth}->execute; call in
      Apache::Session::Store::Postgres for exactly two minutes, then time
      out, and that makes the rest of the app confused, so most things go
      boom...

      We've been banging our heads against this problem in #axkit-dahut for a
      couple of days now, and I created the following Pg log:
      http://paste.husk.org/3133
      Then, I realized at had to read up on what "FOR UPDATE" means. It means
      that it'll lock and wait around for a subsequent UPDATE.

      So, I'm guessing that what happens is that it sits and waits for a
      UPDATE that never happens. I tried to remove the FOR UPDATE from the
      SQL statement, and it solves the immediate problem. But FOR UPDATE is
      something that is used for transactional integrity, and so, it is
      probably a Good Thing and there is probably a good reason why it is
      there... So, I'm turning to you with the hope that you can clue me in
      to how I can live with it. :-)

      A:X:BS is now on CPAN, the latest version is 0.23_3 and previous
      versions has had some problems, so this is the one I'm working on. I
      realized the Pg store would need different defaults than the others,
      and I have also been experimenting with the Commit stuff, in case that
      meant something. I've been testing with stuff like

      # When using Postgres, a different default is needed.
      if ($flex_options{'Store'} eq 'Postgres') {
      $flex_options{'Commit'} = 1;
      $flex_options{'AutoCommit'} = 1;
      $flex_options{'Serialize'} = $r->dir_config( $prefix . 'Serialize' ) ||
      'Base64'
      }

      with no effect....

      I guess it could be that the use we make of Apache::Session is flawed,
      that we should call an update somewhere, but it works (apparently) with
      the other data stores. It feels like there is something in the
      direction of transactions but I can't figure out what... Or, if it
      could be somebody else's bug, relieve me of the feeling of being
      stupid... :-)

      Clues are most welcome!

      Cheers,

      Kjetil
      --
      Kjetil Kjernsmo
      Astrophysicist/IT Consultant/Skeptic/Ski-orienteer/Orienteer/Mountaineer
      kjetil@... webmaster@... editor@...
      Homepage: http://www.kjetil.kjernsmo.net/ OpenPGP KeyID: 6A6A0BBC
    • Perrin Harkins
      ... This probably means that in a previous request you never let the session object go out of scope so it kept the exclusive lock open. ... It creates an
      Message 2 of 6 , Apr 29, 2005
      • 0 Attachment
        Kjetil Kjernsmo wrote:
        > On the surface, the symptom is that for certain pages, it will just sit
        > and spin at the $self->{materialize_sth}->execute; call in
        > Apache::Session::Store::Postgres for exactly two minutes, then time
        > out, and that makes the rest of the app confused, so most things go
        > boom...

        This probably means that in a previous request you never let the session
        object go out of scope so it kept the exclusive lock open.

        > So, I'm guessing that what happens is that it sits and waits for a
        > UPDATE that never happens. I tried to remove the FOR UPDATE from the
        > SQL statement, and it solves the immediate problem. But FOR UPDATE is
        > something that is used for transactional integrity, and so, it is
        > probably a Good Thing and there is probably a good reason why it is
        > there...

        It creates an exclusive lock on that row. This may or may not be
        necessary, depending on how you use sessions in your application.

        > I guess it could be that the use we make of Apache::Session is flawed,
        > that we should call an update somewhere

        You just have to let the object get destroyed so it can release the lock.

        - Perrin
      • Jeffrey W. Baker
        ... It is possible that the FOR UPDATE is spurious. It signals to the database system that this transaction intends to write that row. With PostgreSQL s MVCC
        Message 3 of 6 , May 2, 2005
        • 0 Attachment
          On Fri, 2005-04-29 at 16:50 +0200, Kjetil Kjernsmo wrote:

          > I guess it could be that the use we make of Apache::Session is flawed,
          > that we should call an update somewhere, but it works (apparently) with
          > the other data stores. It feels like there is something in the
          > direction of transactions but I can't figure out what... Or, if it
          > could be somebody else's bug, relieve me of the feeling of being
          > stupid... :-)

          It is possible that the FOR UPDATE is spurious. It signals to the
          database system that this transaction intends to write that row. With
          PostgreSQL's MVCC transaction isolation system, it's probably not
          necessary and may be causing problems.

          Note that I handed the pumpkin over to Casey West several months ago.
          Any issues should be forwarded thereto.

          -jwb
        • Perrin Harkins
          ... I d say it s necessary if you want mutual exclusion, since MVCC will not prevent lost updates. Not everyone needs mutual exclusion, so it could be made
          Message 4 of 6 , May 2, 2005
          • 0 Attachment
            On Mon, 2005-05-02 at 15:08 -0700, Jeffrey W. Baker wrote:
            > It is possible that the FOR UPDATE is spurious. It signals to the
            > database system that this transaction intends to write that row. With
            > PostgreSQL's MVCC transaction isolation system, it's probably not
            > necessary and may be causing problems.

            I'd say it's necessary if you want mutual exclusion, since MVCC will not
            prevent lost updates. Not everyone needs mutual exclusion, so it could
            be made optional, or tied to a particular locking module selection.

            - Perrin
          • Michael Schout
            ... It definately *is* necessary if you want to ensure that only one process has access to your session data at a time. MVCC will prevent a second client from
            Message 5 of 6 , May 2, 2005
            • 0 Attachment
              Jeffrey W. Baker wrote:

              > It is possible that the FOR UPDATE is spurious. It signals to the
              > database system that this transaction intends to write that row. With
              > PostgreSQL's MVCC transaction isolation system, it's probably not
              > necessary and may be causing problems.

              It definately *is* necessary if you want to ensure that only one process
              has access to your session data at a time. MVCC will prevent a second
              client from WRITING to the same row, but it will not block it from
              reading the row.

              In other words, MVCC will not prevent the following scenario:

              client 1: SELECT * FROM sessions WHERE id='1';
              client 2: SELECT * FROM sessions WHERE id='1';

              # at this point, client 1 and client 2 both have copies of the session.
              suppose client 1 makes changes to the session data and saves it:

              client 1: UPDATE sessions SET data='...' WHERE id='1';

              now suppose client 2 makes changes:

              client 2: UPDATE sessions SET data='...' WHERE id='1';

              Whoops, you just wiped out the changes that client 1 made!

              FOR UPDATE prevents this because it tells the database that you intend
              to change the row. The database will not let anyone else select that
              row FOR UPDATE until you either issue a COMMIT or ROLLBACK. So in
              otherwords:

              client 1: SELECT * FROM sessions WHERE id='1' FOR UPDATE;
              client 2: SELECT * FROM sessions WHERE id='1' FOR UPDATE;

              at this point, client 2 will block until client 1 either does COMMIT or
              ROLLBACK.

              So if you want to ensure that only one client has the session data at a
              time, you need FOR UPDATE.

              Regards,
              Michael Schout
            • Kjetil Kjernsmo
              ... Thank you all for the follow-up! Indeed the problem here is that we re not destroying the object properly. It is the classical untie trap that we re in,
              Message 6 of 6 , May 2, 2005
              • 0 Attachment
                On tirsdag 03 mai 2005, 00:40, Michael Schout wrote:
                > Jeffrey W. Baker wrote:
                > > It is possible that the FOR UPDATE is spurious.  It signals to the
                > > database system that this transaction intends to write that row.
                > >  With PostgreSQL's MVCC transaction isolation system, it's probably
                > > not necessary and may be causing problems.
                >
                > It definately *is* necessary if you want to ensure that only one
                > process has access to your session data at a time.  MVCC will prevent
                > a second client from WRITING to the same row, but it will not block
                > it from reading the row.

                Thank you all for the follow-up!

                Indeed the problem here is that we're not destroying the object
                properly. It is the classical untie trap that we're in, described in
                the Camel Book. However, neither of us has sufficient understanding of
                the code, and of tied hashes, I suppose, to really track down the
                issue. There really isn't any $foo = \%session in there, but we're
                calling the handler from the invalidate method, which is most likely
                the source of the problem.

                It sort of works, with a few quirks, with DB_File as backend store, but
                unsurprisingly, it doesn't with Pg. So, for now, I have filed a bug
                against my own package, so that at least my users will have a fair
                chance to understand what's going on:
                http://rt.cpan.org/NoAuth/Bug.html?id=12473

                If anyone wants to have a look, it is certainly appreciated, otherwise,
                we'll just have to return to it $later.

                Cheers,

                Kjetil
                --
                Kjetil Kjernsmo
                Programmer/Astrophysicist/Skeptic/Ski-orienteer/Orienteer/Mountaineer
                kjetil@... webmaster@... editor@...
                Homepage: http://www.kjetil.kjernsmo.net/ OpenPGP KeyID: 6A6A0BBC
              Your message has been successfully submitted and would be delivered to recipients shortly.