8 Replies Latest reply on Mar 26, 2006 2:47 AM by Tim Fox

    HQSL transaction isolation

    Tim Fox Master

      JBoss Messaging requires tx isolation level of READ_COMMITTED, to ensure proper tx isolation, and anything stricter than READ_COMMITTED is likely to result in deadlocks.

      Since HSQL only supports transaction isolation level of READ_UNCOMMITTED, we cannot guarantee any kind of data consistency when using HQSQL. (Without doing lots of nasty hacks to get isolation through some other means)

      In practice we would really only see issues when paging messages to and from storage. (E.g. tx1 commits sending messages to storage, then they are partially loaded by another thread before the tx commit completes), although I am not 100% sure wouldn't see effects of this in normal "non paging" use. Perhaps we should disable paging functionality if an isolation level of looser than READ_COMMITTED is detected?

      I know we don't recommend HSQLDB in production but I think we need to be clear about this to potential new users of JBossMessaging, and get people to use MySQL for example if they don't have access to a "big" database. (I guess mysql IS a big db these days.....)

      On a related point, MySQL has a default tx isolaton of REPEATABLE_READ which is actually unnecessarily strict for us (and leads to deadlock) so any mysql datasource used needs to explicitly set the isolation level.

      This is currently detected in the code.

      Getting it to work with REPEATABLE_READ is probably possible, but could have a performance impact since ordering write locks (which I'm currently doing) would not be sufficient to prevent deadlock. I'd probably need to detect and retry the tx.

        • 1. Re: HQSL transaction isolation
          Adrian Brock Master

          You should be doing the isolation yourself.

          For < READ_COMMITTED
          You should know when you lazy load from a queue
          which messages are in a committed state (from the JMS point of view).

          I can see why > READ_COMMITTED would cause a problem
          Thread 1: in tx1 insert Queue1 Message1
          Thread 2: load Queue1
          Thread 1: in tx1 insert Queue1 Message2

          Depending on the quality of db implementation this will either:
          1) Timeout
          2) Cause a deadlock exception
          3) Cause a optimistic lock failure

          Which you would have to cater for. i.e. rerun the transactions
          that fail.
          You should also recommend not using > READ_COMMITTED
          if the DB doesn't support deadlock detection or optimistic failures
          for > REPEATABLE_COMMITTED isolation
          and for performance reasons anyway,

          • 2. Re: HQSL transaction isolation
            Adrian Brock Master

            On a related issue, you should try
            connection.setReadOnly(true) when you are only querying.

            This is a hint to the DB to do extra optimizations, including
            improving the commit time when there were only reads.

            I keep meaning to test this for JBossMQ.

            • 3. Re: HQSL transaction isolation
              Tim Fox Master

               

              "adrian@jboss.org" wrote:
              You should be doing the isolation yourself.

              For < READ_COMMITTED
              You should know when you lazy load from a queue
              which messages are in a committed state (from the JMS point of view).


              Not sure how I would know that. If I attempt to load and another tx is concurrently inserting a bunch of messages then I may only see some of that other transactions messages.



              I can see why > READ_COMMITTED would cause a problem
              Thread 1: in tx1 insert Queue1 Message1
              Thread 2: load Queue1
              Thread 1: in tx1 insert Queue1 Message2

              Depending on the quality of db implementation this will either:
              1) Timeout
              2) Cause a deadlock exception
              3) Cause a optimistic lock failure

              Which you would have to cater for. i.e. rerun the transactions
              that fail.
              You should also recommend not using > READ_COMMITTED
              if the DB doesn't support deadlock detection or optimistic failures
              for > REPEATABLE_COMMITTED isolation
              and for performance reasons anyway,


              I can add transaction rerunning, I just wanted to avoid doing this if we can mandate READ_COMMITTED.

              Probably laziness on my part :)

              • 4. Re: HQSL transaction isolation
                Adrian Brock Master

                 

                "timfox" wrote:
                "adrian@jboss.org" wrote:
                You should be doing the isolation yourself.

                For < READ_COMMITTED
                You should know when you lazy load from a queue
                which messages are in a committed state (from the JMS point of view).


                Not sure how I would know that. If I attempt to load and another tx is concurrently inserting a bunch of messages then I may only see some of that other transactions messages.


                Why would you care if you see only some? You want to "see" none
                of them until the commit is confirmed.

                This is even more important for 2PC where the prepare writes to disk
                to confirm the commit will work, but it doesn't exist for anybody else
                until you get the commit()

                e.g. In JBossMQ JDBC2 this would be something like:
                select * from jms_messages where destination="Queue1"
                and txid not in (select txid from jms_transactions)

                But even that wouldn't be enough, it would have to weed out
                messages that are in the unacknowledged state using the internal
                data structure. i.e. it should NOT reload messages from the database
                that are currently being received by a client.

                • 5. Re: HQSL transaction isolation
                  Tim Fox Master

                  For 2PC we do something similar to JBossMQ, i.e. we insert messages with state "+", and on jms commit, we update them.

                  For 1PC we just insert them in "committed" state.

                  The entire commit (or prepare) happens in one jdbc tx

                  Even if we inserted them with state = "+" and then updated on commit, then with transaction isolation level of READ_UNCOMITTED another tx could see the results of the update when it's only partially complete, resulting in only some of the tx being visible.

                  For loading this should be ok as long the messages are updated in insertion order (i.e. i wouldn't see "holes" in the ordering if I saw the tx part way throught), but can I guarantee this?

                  • 6. Re: HQSL transaction isolation
                    Tim Fox Master

                     

                    "adrian@jboss.org" wrote:
                    On a related issue, you should try
                    connection.setReadOnly(true) when you are only querying.


                    Interesting. Is this ok to set when using a managed connection?

                    • 7. Re: HQSL transaction isolation
                      Adrian Brock Master

                      Yes. The ManagedConnection will reset the read-only mode
                      as a part of the pooling.
                      It even does this "lazily" like the auto-commit.
                      http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJCALazyAutoCommit

                      • 8. Re: HQSL transaction isolation
                        Tim Fox Master

                        I get the following when I attempt this:

                        1) test_Simple_TP_LX(org.jboss.test.messaging.jms.stress.StressTopicSameConnecti
                        onTest)java.sql.SQLException: You cannot set read only during a managed transact
                        ion!
                         at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.setJdbcR
                        eadOnly(BaseWrapperManagedConnection.java:519)
                         at org.jboss.resource.adapter.jdbc.WrappedConnection.setReadOnly(Wrapped
                        Connection.java:88)
                         at org.jboss.messaging.core.plugin.JDBCPersistenceManager.retrievePrepar
                        edTransactions(JDBCPersistenceManager.java:1787)
                         at org.jboss.messaging.core.tx.TransactionRepository.loadPreparedTransac