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

read only deadlocks

Expand Messages
  • imoffskating
    Hello, We are running Firebird 1.5.2 SS on linux with jaybird jdbc. We are writing a program that incorporates a money transfer system. The money is
    Message 1 of 5 , Sep 2, 2005
    • 0 Attachment
      Hello,

      We are running Firebird 1.5.2 SS on linux with jaybird jdbc.

      We are writing a program that incorporates a money transfer system.
      The money is "transferred" between tables by the main program. The
      transfer is within a transaction (i.e. 2 updates - deduct some money
      from one table, increase money in other table).

      Our transaction isolation is set to read_committed.


      Another program needs to take a snapshot of all committed transactions
      at one time to make sure all the money "adds up" and no money has gone
      "missing" by our faulty code etc.

      As far as we understand this checking program should be in
      serializable mode, read only and function correctly.

      What we've found is the check program gets a GDS deadlock exception
      which we thought would not happen as serializable uses a snapshot of
      the db.

      Does anyone have any advice on how to properly set up our isolation
      level correctly for the check program to work without deadlocks and
      only see data committed at its start time?

      Many thanks,

      alex.
    • David Jencks
      I think you want your auditing transaction to be what results from setting repeatable read via jdbc. In firebird that will give you a permanently consistent
      Message 2 of 5 , Sep 2, 2005
      • 0 Attachment
        I think you want your auditing transaction to be what results from
        setting repeatable read via jdbc. In firebird that will give you a
        permanently consistent view of the data. I believe the serializable tx
        mode introduces some locks to actually serialize the transactions.

        Why aren't your transfer transactions also repeatable read? I would
        think that using read committed could easily introduce inconsistencies
        if there is any overlap between the accounts in two transactions. IIUC
        you will not lose any speed by using repeatable read instead of read
        committed.

        thanks
        david jencks

        On Sep 2, 2005, at 8:56 AM, imoffskating wrote:

        > Hello,
        >
        > We are running Firebird 1.5.2 SS on linux with jaybird jdbc.
        >
        > We are writing a program that incorporates a money transfer system.
        > The money is "transferred" between tables by the main program. The
        > transfer is within a transaction (i.e. 2 updates - deduct some money
        > from one table, increase money in other table).
        >
        > Our transaction isolation is set to read_committed.
        >
        >
        > Another program needs to take a snapshot of all committed transactions
        > at one time to make sure all the money "adds up" and no money has gone
        > "missing" by our faulty code etc.
        >
        > As far as we understand this checking program should be in
        > serializable mode, read only and function correctly.
        >
        > What we've found is the check program gets a GDS deadlock exception
        > which we thought would not happen as serializable uses a snapshot of
        > the db.
        >
        > Does anyone have any advice on how to properly set up our isolation
        > level correctly for the check program to work without deadlocks and
        > only see data committed at its start time?
        >
        > Many thanks,
        >
        > alex.
        >
        >
        >
        >
        >
        >
        >
        >
        >
        >
        > SPONSORED LINKS
        > Basic programming language
        > Computer programming languages
        > Programming languages
        > Java programming language
        > The history of computer programming language
        >
        > YAHOO! GROUPS LINKS
        >
        > ▪  Visit your group "Firebird-Java" on the web.
        >  
        > ▪  To unsubscribe from this group, send an email to:
        >  Firebird-Java-unsubscribe@yahoogroups.com
        >  
        > ▪  Your use of Yahoo! Groups is subject to the Yahoo! Terms of
        > Service.
        >
        >
      • imoffskating
        Thank you for reply. We have tried this and repeatable read allows phantom reads which means that money that has been moved is counted twice because we are
        Message 3 of 5 , Sep 5, 2005
        • 0 Attachment
          Thank you for reply.

          We have tried this and repeatable read allows phantom reads which
          means that money that has been moved is counted twice because we are
          summing a log of transfers.

          we are happy with read committed on the transfer transactions as all
          the updates are inline. Strangely though repeatable read caused
          deadlocks whereas read committed does not.

          As far as we see a read only serializable connection should not
          deadlock for the auditing program. But we have found this to be the case.

          Many thanks again,
          Alex.



          --- In Firebird-Java@yahoogroups.com, David Jencks <david_jencks@y...>
          wrote:
          > I think you want your auditing transaction to be what results from
          > setting repeatable read via jdbc. In firebird that will give you a
          > permanently consistent view of the data. I believe the serializable tx
          > mode introduces some locks to actually serialize the transactions.
          >
          > Why aren't your transfer transactions also repeatable read? I would
          > think that using read committed could easily introduce inconsistencies
          > if there is any overlap between the accounts in two transactions. IIUC
          > you will not lose any speed by using repeatable read instead of read
          > committed.
          >
          > thanks
          > david jencks
          >
          > On Sep 2, 2005, at 8:56 AM, imoffskating wrote:
          >
          > > Hello,
          > >
          > > We are running Firebird 1.5.2 SS on linux with jaybird jdbc.
          > >
          > > We are writing a program that incorporates a money transfer system.
          > > The money is "transferred" between tables by the main program. The
          > > transfer is within a transaction (i.e. 2 updates - deduct some money
          > > from one table, increase money in other table).
          > >
          > > Our transaction isolation is set to read_committed.
          > >
          > >
          > > Another program needs to take a snapshot of all committed
          transactions
          > > at one time to make sure all the money "adds up" and no money has
          gone
          > > "missing" by our faulty code etc.
          > >
          > > As far as we understand this checking program should be in
          > > serializable mode, read only and function correctly.
          > >
          > > What we've found is the check program gets a GDS deadlock exception
          > > which we thought would not happen as serializable uses a snapshot of
          > > the db.
          > >
          > > Does anyone have any advice on how to properly set up our isolation
          > > level correctly for the check program to work without deadlocks and
          > > only see data committed at its start time?
          > >
          > > Many thanks,
          > >
          > > alex.
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > >
          > > SPONSORED LINKS
          > > Basic programming language
          > > Computer programming languages
          > > Programming languages
          > > Java programming language
          > > The history of computer programming language
          > >
          > > YAHOO! GROUPS LINKS
          > >
          > > ▪  Visit your group "Firebird-Java" on the web.
          > > Â
          > > ▪  To unsubscribe from this group, send an email to:
          > > Â Firebird-Java-unsubscribe@yahoogroups.com
          > > Â
          > > ▪  Your use of Yahoo! Groups is subject to the Yahoo! Terms of
          > > Service.
          > >
          > >
        • imoffskating
          We think we have managed to get a snapshot view in the auditing program by using a custom Serializable isolation level of FirebirdConnection.TPB_CONCURRENCY,
          Message 4 of 5 , Sep 5, 2005
          • 0 Attachment
            We think we have managed to get a snapshot view in the auditing
            program by using a custom Serializable isolation level of
            FirebirdConnection.TPB_CONCURRENCY, FirebirdConnection.TPB_WAIT,
            FirebirdConnection.TPB_READ

            ie Concurrency instead of consistency.


            This now seems to temporarily crash the database and cause a "Resource
            Exception. Unable to complete network request" on the auditing and the
            transaction program.

            Does anyone know of any reason why this should crash the database?

            Also, we are finding unusual behaviour in our transactions. We have a
            transaction in the transaction program thusly:

            Set autoCommit false;
            UPDATE member SET balance=balance-purchaseValue;
            INSERT INTO purchases (id, username, pruchaseValue);
            commit;

            But our snapshot in the auditing program shows the old balance but
            with the new purchase. We thought surely this should be an all or
            nothing transaction. Any ideas?

            Many thanks again,

            Alex.





            --- In Firebird-Java@yahoogroups.com, "imoffskating" <alexn@x> wrote:
            > Thank you for reply.
            >
            > We have tried this and repeatable read allows phantom reads which
            > means that money that has been moved is counted twice because we are
            > summing a log of transfers.
            >
            > we are happy with read committed on the transfer transactions as all
            > the updates are inline. Strangely though repeatable read caused
            > deadlocks whereas read committed does not.
            >
            > As far as we see a read only serializable connection should not
            > deadlock for the auditing program. But we have found this to be the
            case.
            >
            > Many thanks again,
            > Alex.
            >
            >
            >
            > --- In Firebird-Java@yahoogroups.com, David Jencks <david_jencks@y...>
            > wrote:
            > > I think you want your auditing transaction to be what results from
            > > setting repeatable read via jdbc. In firebird that will give you a
            > > permanently consistent view of the data. I believe the
            serializable tx
            > > mode introduces some locks to actually serialize the transactions.
            > >
            > > Why aren't your transfer transactions also repeatable read? I would
            > > think that using read committed could easily introduce
            inconsistencies
            > > if there is any overlap between the accounts in two transactions.
            IIUC
            > > you will not lose any speed by using repeatable read instead of read
            > > committed.
            > >
            > > thanks
            > > david jencks
            > >
            > > On Sep 2, 2005, at 8:56 AM, imoffskating wrote:
            > >
            > > > Hello,
            > > >
            > > > We are running Firebird 1.5.2 SS on linux with jaybird jdbc.
            > > >
            > > > We are writing a program that incorporates a money transfer system.
            > > > The money is "transferred" between tables by the main program. The
            > > > transfer is within a transaction (i.e. 2 updates - deduct some
            money
            > > > from one table, increase money in other table).
            > > >
            > > > Our transaction isolation is set to read_committed.
            > > >
            > > >
            > > > Another program needs to take a snapshot of all committed
            > transactions
            > > > at one time to make sure all the money "adds up" and no money has
            > gone
            > > > "missing" by our faulty code etc.
            > > >
            > > > As far as we understand this checking program should be in
            > > > serializable mode, read only and function correctly.
            > > >
            > > > What we've found is the check program gets a GDS deadlock exception
            > > > which we thought would not happen as serializable uses a
            snapshot of
            > > > the db.
            > > >
            > > > Does anyone have any advice on how to properly set up our isolation
            > > > level correctly for the check program to work without deadlocks and
            > > > only see data committed at its start time?
            > > >
            > > > Many thanks,
            > > >
            > > > alex.
            > > >
            > > >
            > > >
            > > >
            > > >
            > > >
            > > >
            > > >
            > > >
            > > >
            > > > SPONSORED LINKS
            > > > Basic programming language
            > > > Computer programming languages
            > > > Programming languages
            > > > Java programming language
            > > > The history of computer programming language
            > > >
            > > > YAHOO! GROUPS LINKS
            > > >
            > > > ▪  Visit your group "Firebird-Java" on the web.
            > > > Â
            > > > ▪  To unsubscribe from this group, send an email to:
            > > > Â Firebird-Java-unsubscribe@yahoogroups.com
            > > > Â
            > > > ▪  Your use of Yahoo! Groups is subject to the Yahoo! Terms of
            > > > Service.
            > > >
            > > >
          • Roman Rokytskyy
            ... There was a long discussion between the mapping of the Firebird isolation modes into JDBC ones. There were no agreement, but concurrency is enough for
            Message 5 of 5 , Sep 6, 2005
            • 0 Attachment
              > We think we have managed to get a snapshot view in the auditing
              > program by using a custom Serializable isolation level of
              > FirebirdConnection.TPB_CONCURRENCY, FirebirdConnection.TPB_WAIT,
              > FirebirdConnection.TPB_READ
              > ie Concurrency instead of consistency.

              There was a long discussion between the mapping of the Firebird isolation
              modes into JDBC ones. There were no agreement, but concurrency is enough for
              serializable execution defined in the JDBC specification.

              > This now seems to temporarily crash the database and cause a "Resource
              > Exception. Unable to complete network request" on the auditing and the
              > transaction program.
              >
              > Does anyone know of any reason why this should crash the database?

              If you have reproducable test case, please share it with us.

              > Also, we are finding unusual behaviour in our transactions. We have a
              > transaction in the transaction program thusly:
              >
              > Set autoCommit false;
              > UPDATE member SET balance=balance-purchaseValue;
              > INSERT INTO purchases (id, username, pruchaseValue);
              > commit;
              >
              > But our snapshot in the auditing program shows the old balance but
              > with the new purchase. We thought surely this should be an all or
              > nothing transaction. Any ideas?

              Should not be this way, please create a test case to reproduce the issue.

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