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

cannot disconnect database with open transactions

Expand Messages
  • theghost_k8
    Try If fbcon.State = ConnectionState.Open Then fbcon.Close() fbcon.ClearPool(fbcon)
    Message 1 of 8 , Feb 10, 2007
    View Source
    • 0 Attachment
      Try
      If fbcon.State = ConnectionState.Open Then
      fbcon.Close()
      fbcon.ClearPool(fbcon) << this returns error "cannot
      disconnect database with open transactions (<number> open)"
      End If
      Catch ex As Exception
      If Show_Error Then
      MsgBox(ex.ToString)
      End If
      End Try

      IS THERE ANY WAY I CAN CLOSE THIS TRANSACTIONS ?
    • Helen Borrie
      ... Yes: commit or roll back transactions before you attempt to close the connection. ./heLen
      Message 2 of 8 , Feb 10, 2007
      View Source
      • 0 Attachment
        At 01:21 AM 11/02/2007, you wrote:
        >Try
        > If fbcon.State = ConnectionState.Open Then
        > fbcon.Close()
        > fbcon.ClearPool(fbcon) << this returns error "cannot
        >disconnect database with open transactions (<number> open)"
        > End If
        > Catch ex As Exception
        > If Show_Error Then
        > MsgBox(ex.ToString)
        > End If
        >End Try
        >
        >IS THERE ANY WAY I CAN CLOSE THIS TRANSACTIONS ?

        Yes: commit or roll back transactions before you attempt to close
        the connection.

        ./heLen
      • theghost_k8
        hi there, the problem here is i am not using the transaction in any ways !! its just a common connection and command object !! using same conn i fire 2 select
        Message 3 of 8 , Feb 10, 2007
        View Source
        • 0 Attachment
          hi there,
          the problem here is i am not using the transaction in any ways !!
          its just a common connection and command object !!
          using same conn i fire 2 select queries and a stored proc. nothing else.
          i don't know how this active transaction occurs in this scenario.
          can i get, by any way the transaction-handles so that i can handle
          them? is there any way i can get count of active-transactions?
        • Helen Borrie
          ... Oh yes you are! Firebird is a transactional database. If you have an active connection then you have at least one transaction. ... This sounds like
          Message 4 of 8 , Feb 11, 2007
          View Source
          • 0 Attachment
            At 06:30 PM 11/02/2007, you wrote:

            >hi there,
            >the problem here is i am not using the transaction in any ways !!

            Oh yes you are! Firebird is a transactional database. If you have
            an active connection then you have at least one transaction.

            >its just a common connection and command object !!

            This sounds like Visual basic and driver-speak - are you using the ODBC driver?

            >using same conn i fire 2 select queries and a stored proc. nothing else.
            >i don't know how this active transaction occurs in this scenario.

            On a "dumbed-down" interface like ODBC, the driver layer is creating
            the transaction (usually a single transaction) for you
            automatically. The Firebird driver will provide you with some
            specific mechanisms for testing whether your client application has
            uncommitted transactions. You will need to ask on the
            firebird-odbc-devel list about this.

            >can i get, by any way the transaction-handles so that i can handle
            >them?

            You probably have only one and the driver will have a property by
            which you can access it. Again, this is a driver-specific issue.

            >is there any way i can get count of active-transactions?

            If your driver layer supports multiple transactions then it will (or
            should) give you a way to enumerate them and operate on them as a group.

            You don't have any way to know about transactions owned by other
            connections/applications/application instances, etc., and you don't
            need to know. Your connection instance doesn't have access to
            another connection's transactions, by design. Your connection is
            concerned only with the transactions that pertain to itself.

            ./heLen
          • theghost_k8
            How much true is this?? The biggest problem with this pertains to open transactions - the database still thinks the transactions are active and the program
            Message 5 of 8 , Feb 12, 2007
            View Source
            • 0 Attachment
              How much true is this??

              The biggest problem with this pertains to open transactions - the
              database still thinks the transactions are active and the program
              thinks they aren't. As a result, there is no way to simply stop them.
              Actually, they need to be rolled back and there is no mechanism to do
              that.
              i read that from :
              http://mc-computing.com/Databases/Delphi/IBX_Connection.html


              --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
              >
              > At 06:30 PM 11/02/2007, you wrote:
              >
              > >hi there,
              > >the problem here is i am not using the transaction in any ways !!
              >
              > Oh yes you are! Firebird is a transactional database. If you have
              > an active connection then you have at least one transaction.
              >
              > >its just a common connection and command object !!
              >
              > This sounds like Visual basic and driver-speak - are you using the
              ODBC driver?
              >
              > >using same conn i fire 2 select queries and a stored proc. nothing
              else.
              > >i don't know how this active transaction occurs in this scenario.
              >
              > On a "dumbed-down" interface like ODBC, the driver layer is creating
              > the transaction (usually a single transaction) for you
              > automatically. The Firebird driver will provide you with some
              > specific mechanisms for testing whether your client application has
              > uncommitted transactions. You will need to ask on the
              > firebird-odbc-devel list about this.
              >
              > >can i get, by any way the transaction-handles so that i can handle
              > >them?
              >
              > You probably have only one and the driver will have a property by
              > which you can access it. Again, this is a driver-specific issue.
              >
              > >is there any way i can get count of active-transactions?
              >
              > If your driver layer supports multiple transactions then it will (or
              > should) give you a way to enumerate them and operate on them as a group.
              >
              > You don't have any way to know about transactions owned by other
              > connections/applications/application instances, etc., and you don't
              > need to know. Your connection instance doesn't have access to
              > another connection's transactions, by design. Your connection is
              > concerned only with the transactions that pertain to itself.
              >
              > ./heLen
              >
            • theghost_k8
              i am using firebird for vb.net version 1.5.4 and i am not siting any way to get that particular transaction atleast here in 1.5.4!! ... ODBC driver? ... else.
              Message 6 of 8 , Feb 12, 2007
              View Source
              • 0 Attachment
                i am using firebird for vb.net version 1.5.4
                and i am not siting any way to get that particular transaction atleast
                here in 1.5.4!!



                --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
                >
                > At 06:30 PM 11/02/2007, you wrote:
                >
                > >hi there,
                > >the problem here is i am not using the transaction in any ways !!
                >
                > Oh yes you are! Firebird is a transactional database. If you have
                > an active connection then you have at least one transaction.
                >
                > >its just a common connection and command object !!
                >
                > This sounds like Visual basic and driver-speak - are you using the
                ODBC driver?
                >
                > >using same conn i fire 2 select queries and a stored proc. nothing
                else.
                > >i don't know how this active transaction occurs in this scenario.
                >
                > On a "dumbed-down" interface like ODBC, the driver layer is creating
                > the transaction (usually a single transaction) for you
                > automatically. The Firebird driver will provide you with some
                > specific mechanisms for testing whether your client application has
                > uncommitted transactions. You will need to ask on the
                > firebird-odbc-devel list about this.
                >
                > >can i get, by any way the transaction-handles so that i can handle
                > >them?
                >
                > You probably have only one and the driver will have a property by
                > which you can access it. Again, this is a driver-specific issue.
                >
                > >is there any way i can get count of active-transactions?
                >
                > If your driver layer supports multiple transactions then it will (or
                > should) give you a way to enumerate them and operate on them as a group.
                >
                > You don't have any way to know about transactions owned by other
                > connections/applications/application instances, etc., and you don't
                > need to know. Your connection instance doesn't have access to
                > another connection's transactions, by design. Your connection is
                > concerned only with the transactions that pertain to itself.
                >
                > ./heLen
                >
              • Helen Borrie
                ... Well, it s true in a way. Only the client application can start or end a transaction. Between the client application (your program) and the database is
                Message 7 of 8 , Feb 12, 2007
                View Source
                • 0 Attachment
                  At 11:41 PM 12/02/2007, you wrote:
                  >How much true is this??
                  >
                  >The biggest problem with this pertains to open transactions - the
                  >database still thinks the transactions are active and the program
                  >thinks they aren't. As a result, there is no way to simply stop them.

                  Well, it's true in a way. Only the client application can start or
                  end a transaction. Between the client application (your program) and
                  the database is the client library (fbclient.dll if you are on
                  Windows). That library exposes the application programming interface
                  (API), which consists of various function calls in C that take some
                  complex C structures as arguments and return some others as results.

                  So, if you are programming in Delphi, there is another layer that
                  translates the API into Pascal and surfaces the function calls and
                  argument structures as methods and properties. I think you mentioned
                  you were using the Zeos components: well, that is what they do, at a
                  very light and generic level.

                  >Actually, they need to be rolled back and there is no mechanism to do
                  >that.

                  Perhaps your components don't surface a separate structure for a
                  transaction. Generally those lightweight layers merge the
                  transaction and the database connection into one class and you will
                  find Commit and Rollback methods there.

                  But the Commit or Rollback call doesn't mean that the work is
                  suddenly complete. It means only that the program has passed a
                  request to the server to Commit or Rollback the transaction. That
                  may take a little or a lot of time, depending on what your
                  application has been doing with the data in the transaction. It
                  could also be that some misconfiguration in the transaction
                  parameters has multiple transactions in WAIT mode, waiting forever
                  for someone else's transaction that is waiting for someone else's
                  transaction....and so on... or even two WAIT transactions that are
                  waiting on each other, the so-called "livelock" impasse.

                  I suggest studying the help for whatever component interface you are
                  using to learn what the various properties and methods do. Many of
                  the interface layer providers have support lists, so try to hook up
                  with the appropriate one.

                  >i read that from :
                  >http://mc-computing.com/Databases/Delphi/IBX_Connection.html

                  That's interesting reading if you are new to network computing, and
                  worth taking on board. Although the code problems and workarounds
                  are specific to IBX, some of what applies to IBX will also apply to
                  the Firebird driver for Zeos or whatever you are using, since both
                  are talking to APIs that are almost identical.

                  Helen
                • Helen Borrie
                  ... Then you need to subscribe to the firebird-net-provider list!! Subscribe from this page: http://firebirdsql.org/index.php?op=lists ./heLen
                  Message 8 of 8 , Feb 12, 2007
                  View Source
                  • 0 Attachment
                    At 12:05 AM 13/02/2007, you wrote:
                    >i am using firebird for vb.net version 1.5.4
                    >and i am not siting any way to get that particular transaction atleast
                    >here in 1.5.4!!

                    Then you need to subscribe to the firebird-net-provider list!!

                    Subscribe from this page: http://firebirdsql.org/index.php?op=lists

                    ./heLen
                  Your message has been successfully submitted and would be delivered to recipients shortly.