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

Can you use PreparedStatements together with connection pooling?

Expand Messages
  • phil_hhn
    Hi, in our Java app we have some general-purpose helper classes which hold on to some regularly-used PreparedStatements. This all works well. However we have a
    Message 1 of 10 , Mar 21, 2010
    • 0 Attachment
      Hi, in our Java app we have some general-purpose helper classes which hold on to some regularly-used PreparedStatements. This all works well.

      However we have a java-based web app in which we use connection pooling. What happens to these PreparedStatements in this scenario? Are PreparedStatements 'bound' in any way to the connection that created them? I.e after the PreparedStatement is created, can it be used as its own entity, completely independent of the connection on which it was created? If you close the original connection it'll break the PreparedStatement, won't it?

      E.g we create PreparedStatement1 with thread1/connection1, then connection1 gets released back to the pool. If connection1 then gets allocated to thread2 (and that is busy using the connection) then thread3 starts using the PreparedStatement1, is there going to be any problem?

      I ask this not because I've experienced any issues yet, but I want to avoid any corruptions (these sorts can be hard to track down) and follow 'best practice'. I've seen what can go wrong if two threads simultaneously try to execute queries on the same connection...

      Thanks in advance, Phil
    • phil_hhn
      PS. Having thought more about this since posting the message, logic suggests to me that PreparedStatements ARE bound to a particular connection.... so I guess
      Message 2 of 10 , Mar 21, 2010
      • 0 Attachment
        PS. Having thought more about this since posting the message, logic suggests to me that PreparedStatements ARE bound to a particular connection.... so I guess that's what I'm asking about here.

        Phil
      • Roman Rokytskyy
        ... In theory it should work to some extent. JDBC specification requires every driver to support multithreaded access to one connection. This is also
        Message 3 of 10 , Mar 21, 2010
        • 0 Attachment
          > However we have a java-based web app in which we use connection pooling. What happens to these PreparedStatements in this scenario? Are PreparedStatements 'bound' in any way to the connection that created them? I.e after the PreparedStatement is created, can it be used as its own entity, completely independent of the connection on which it was created? If you close the original connection it'll break the PreparedStatement, won't it?
          >
          > E.g we create PreparedStatement1 with thread1/connection1, then connection1 gets released back to the pool. If connection1 then gets allocated to thread2 (and that is busy using the connection) then thread3 starts using the PreparedStatement1, is there going to be any problem?
          >
          > I ask this not because I've experienced any issues yet, but I want to avoid any corruptions (these sorts can be hard to track down) and follow 'best practice'. I've seen what can go wrong if two threads simultaneously try to execute queries on the same connection...

          In theory it should work to some extent. JDBC specification requires
          every driver to support multithreaded access to one connection. This is
          also implemented in Jaybird, but there were also reports of some
          NullPointerExceptions under heavy load, which might be related to the
          multithreaded access (though none of these issues can be reproduced
          under normal load, so most likely many factors are responsible for that).

          But you need to consider following issues:

          1. Transactions are bound to the connections. So, if you use prepared
          statement in one thread, and commit/rollback are issued in another
          thread, you will definitely see "funny" (i.e. non-deterministical)
          behavior. If you use auto-commit, things get even worser, since prepared
          statement (and underlying result set, if any) will be closed before
          another statement is executed.

          2. Lifetime of the prepared statements is bound by the lifetime of the
          connection, which created that statement. So, any pool, that respects
          the JDBC specification, will close prepared statement when the
          connection is closed (i.e. returned back to the pool).

          3. Jaybird will synchronize on a connection object in case of
          multithreaded access, because the wire protocol needs to get reply from
          the server for the first packet before it sends next one. So using two
          prepared statements from one connection will be slower that using one
          prepared statement per connection in case of high load, when each
          prepared statement was created by a separated connection object.

          So, the best practice looks like this:

          Connection c = pool.getConnection();
          try {
          PreparedStatement stmt =
          c.prepareStatement(sql);
          ...
          } finally {
          c.close();
          }

          If you have clever connection pool, it will also pool the prepared
          statements, so you will save also time on parsing the sql statements on
          the server.

          Roman
        • phil_hhn
          Thanks very much for the speedy reply Roman, that s answered/confirmed a lot of my concerns. The pooling of prepared statements is an interesting idea... but
          Message 4 of 10 , Mar 21, 2010
          • 0 Attachment
            Thanks very much for the speedy reply Roman, that's answered/confirmed a lot of my concerns. The pooling of prepared statements is an interesting idea... but looks like we need to have a big re-think of our prepared statements code in our web app.

            Cheers
          • Mark Rotteveel
            ... A Statement (and PreparedStatement) is always bound to the connection that created it. If the connection is closed all associated statements are closed and
            Message 5 of 10 , Mar 22, 2010
            • 0 Attachment
              > Hi, in our Java app we have some general-purpose helper classes which hold
              > on to some regularly-used PreparedStatements. This all works well.
              >
              > However we have a java-based web app in which we use connection pooling.
              > What happens to these PreparedStatements in this scenario? Are
              > PreparedStatements 'bound' in any way to the connection that created them? I.e after the
              > PreparedStatement is created, can it be used as its own entity, completely
              > independent of the connection on which it was created? If you close the
              > original connection it'll break the PreparedStatement, won't it?

              A Statement (and PreparedStatement) is always bound to the connection that created it. If the connection is closed all associated statements are closed and can no longer be used.
              --
              Sicherer, schneller und einfacher. Die aktuellen Internet-Browser -
              jetzt kostenlos herunterladen! http://portal.gmx.net/de/go/atbrowser
            • Mark Rotteveel
              ... I recently looked at that piece of code to see if I could reproduce it. I haven t been able yet, but the synchronisation there looks iffy (especially
              Message 6 of 10 , Mar 22, 2010
              • 0 Attachment
                > In theory it should work to some extent. JDBC specification requires
                > every driver to support multithreaded access to one connection. This is
                > also implemented in Jaybird, but there were also reports of some
                > NullPointerExceptions under heavy load, which might be related to the
                > multithreaded access (though none of these issues can be reproduced
                > under normal load, so most likely many factors are responsible for that).

                I recently looked at that piece of code to see if I could reproduce it. I haven't been able yet, but the synchronisation there looks iffy (especially because for some classes the getSynchronisationObject() returns a new object on every call (if the connection is autocommit I believe).
                --
                GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern
                E-Mail, SMS & mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail
              • Mark Rotteveel
                ... Pooling of prepared statements in the way you want to should be implemented in the driver and/or the server. I believe Jaybird/Firebird currently has
                Message 7 of 10 , Mar 22, 2010
                • 0 Attachment
                  > Thanks very much for the speedy reply Roman, that's answered/confirmed a
                  > lot of my concerns. The pooling of prepared statements is an interesting
                  > idea... but looks like we need to have a big re-think of our prepared
                  > statements code in our web app.

                  Pooling of prepared statements in the way you want to should be implemented in the driver and/or the server. I believe Jaybird/Firebird currently has neither.

                  Mark
                  --
                  GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern
                  E-Mail, SMS & mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail
                • Roman Rokytskyy
                  ... It is implemented in the connection pool classes of Jaybird. Optional, so to say. Roman
                  Message 8 of 10 , Mar 22, 2010
                  • 0 Attachment
                    >> Thanks very much for the speedy reply Roman, that's answered/confirmed a
                    >> lot of my concerns. The pooling of prepared statements is an interesting
                    >> idea... but looks like we need to have a big re-think of our prepared
                    >> statements code in our web app.
                    >
                    > Pooling of prepared statements in the way you want to should be implemented in the driver and/or the server. I believe Jaybird/Firebird currently has neither.

                    It is implemented in the connection pool classes of Jaybird. Optional,
                    so to say.

                    Roman
                  • Roman Rokytskyy
                    ... In general the getSynchronizationObject() was supposed to return Connection object in auto-commit case and something else for non-autocommit case ( this ,
                    Message 9 of 10 , Mar 22, 2010
                    • 0 Attachment
                      >> In theory it should work to some extent. JDBC specification requires
                      >> every driver to support multithreaded access to one connection. This is
                      >> also implemented in Jaybird, but there were also reports of some
                      >> NullPointerExceptions under heavy load, which might be related to the
                      >> multithreaded access (though none of these issues can be reproduced
                      >> under normal load, so most likely many factors are responsible for that).
                      >
                      > I recently looked at that piece of code to see if I could reproduce it. I haven't been able yet, but the synchronisation there looks iffy (especially because for some classes the getSynchronisationObject() returns a new object on every call (if the connection is autocommit I believe).

                      In general the getSynchronizationObject() was supposed to return
                      Connection object in auto-commit case and something else for
                      non-autocommit case ("this", usually). The access to the GDSHelper (and
                      thus underlying implementation of GDS API) should be always synchronized
                      anyway.

                      Roman
                    • Mark Rotteveel
                      ... I do think that can cause problems when switching from autocommit to non-autocommit. But I will see if I can find some time to think about it in more
                      Message 10 of 10 , Mar 22, 2010
                      • 0 Attachment
                        > In general the getSynchronizationObject() was supposed to return
                        > Connection object in auto-commit case and something else for
                        > non-autocommit case ("this", usually). The access to the GDSHelper (and
                        > thus underlying implementation of GDS API) should be always synchronized
                        > anyway.

                        I do think that can cause problems when switching from autocommit to non-autocommit. But I will see if I can find some time to think about it in more depth.

                        Mark
                        --
                        GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
                        Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
                      Your message has been successfully submitted and would be delivered to recipients shortly.