9 Replies Latest reply on Mar 6, 2007 1:49 PM by weston.price

    JDBC/XA changes to better support MSSQL Server

    weston.price

      Continued discussion

        • 1. Re: JDBC/XA changes to better support MSSQL Server
          weston.price

          In looking at this there are a few things to consider:

          1)We would have to do this for all handles registered with the XA/Local MC

          2) The only way this would really work is if they are using the track statements, being that this is the only condition where we register and keep a *memory* of currently executing statements.

          • 2. Re: JDBC/XA changes to better support MSSQL Server

            Original discussion:

            Problem: Need to invoke Statement.cancel() otherwise XAResource.rollback()
            blocks.


            So basically we need to add something to the jdbc resource adapter
            such that when XAResource.prepare(), commit(onePhase) or rollback()
            or for local tx Connection.commit()/rollback()
            is invoked, we check whether there are any currently "open"
            statements and issue a cancel().

            We already keep track of unclosed statements in WrappedConnection
            so this should be a fairly trivial change?

            I guess also, that if a close() is going to block,
            we should also do the same cancel() when we close statements
            (or return the statement to the prepared statement cache)
            in response to a close() on the connection wrapper.


            and


            I'm saying the JCA layer should do it
            (in cases where the jdbc adapter doesn't do it itself).

            The stack would look something like this?

            org.jboss.resource.adapter.jdbc.WrappedConnection.cancelStatements();
            org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkComplete();
            org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.rollback();
            tm.handleTimeout();

            Before doing the real delegation:

            com.acme.xadatasource.XAResourceImpl.rollback();
            org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.rollback();
            tm.handleTimeout();

            The same feature would work for local datasources as well
            since we give you a LocalXAResource that invokes the
            ManagedConnection's local commit/rollback, i.e.

            org.jboss.resource.adapter.jdbc.WrappedConnection.cancelStatements();
            org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkComplete();
            org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.rollback();
            org.jboss.resource.cm.TxConnectionManager$LocalXAResource.rollback();
            tm.handleTimeout();


            • 3. Re: JDBC/XA changes to better support MSSQL Server
              weston.price

              You beat me to the punch in posting it in :-(

              • 4. Re: JDBC/XA changes to better support MSSQL Server

                Obviously, you can only do it if statements are being tracked.
                Otherwise, the statement is not recorded or wrapped.

                The other part is whether this should be optional, i.e. you need to add a

                <cancel-statements/>

                since not all drivers need this check?

                The most optimal way of doing it (and also the most generic)
                would be store an "inExecute" reference count inside the WrappedStatement
                for the number of times the execute method is being invoked concurrently.
                This way we know whether the statement should be canceled.
                I think cancelling statements is a good idea in general anyway.

                e.g. If we are returning prepared statements to the pool we want to make
                absolutely certain it is not currently invoking on the db.

                But the latter part is probably just a debug feature for strange multi-threaded
                uses? e.g. servlets.

                • 5. Re: JDBC/XA changes to better support MSSQL Server

                  Another issue is whether we should force a rollback of the transaction
                  when somebody tries to commit but there are statements still being executed.

                  Although the db driver should handle the concurrent usage correctly
                  this probably represents a logic error by the user (again in multithreaded uses).

                  • 6. Re: JDBC/XA changes to better support MSSQL Server
                    weston.price

                     


                    The other part is whether this should be optional


                    I think it definitely should be. Note, this is the only provider that is exhibiting this behavior. I follow the rule of 'Always be able to shut it off' in JCA.


                    The most optimal way of doing it (and also the most generic)
                    would be store an "inExecute" reference count inside the WrappedStatement
                    for the number of times the execute method is being invoked concurrently.


                    Yep. I agree.


                    Another issue is whether we should force a rollback of the transaction
                    when somebody tries to commit but there are statements still being executed.


                    Tricky because we would effectively be asserting that we *know* how to handle this situation better than the RDBMS. Again, perhaps a config option?



                    • 7. Re: JDBC/XA changes to better support MSSQL Server
                      weston.price

                      Just so we are on the same page. The new options will allow us to terminate any outstanding statement work prior to prepare/commit/rollback. Anything else is config options to support edge cases.

                      You know, this is where aspects would be really nice.

                      • 8. Re: JDBC/XA changes to better support MSSQL Server

                         

                        "weston.price@jboss.com" wrote:

                        Tricky because we would effectively be asserting that we *know* how to handle this situation better than the RDBMS. Again, perhaps a config option?


                        In the old transaction manager this type of thing was an optional plugin
                        see org.jboss.tm.integrity.*
                        except here we doing the integrity check for the resource usage
                        rather than transaction thread association.

                        But this is really a 1% usecase. You do still see people that
                        put a connection in a HTTP Session and then end up using it concurrently
                        when the client opens mulitple browser windows.

                        Obviously, this is probably broken if the servlet does setAutoCommit(false)
                        and it is something we could detect in the resource adapter.

                        Maybe this is a debug feature we could leave to the new JCA implementation?
                        Since it will be easier to plugin as an optional interceptor to do the extra tracking
                        (it requires tracking all connection, statement, result set invocations not just executeXXX).

                        • 9. Re: JDBC/XA changes to better support MSSQL Server
                          weston.price

                           


                          Maybe this is a debug feature we could leave to the new JCA implementation?
                          Since it will be easier to plugin as an optional interceptor to do the extra tracking


                          Makes sense from a sheer refactoring/testing standpoint. So again, the rules for the new 'workaround' are

                          1) Add incrementing counter to WrappedStatement as an easy way to determine outstanding work

                          2) During the 2PC/Local lifecycle cancel statements that currently working and may interfefere with the transaction protocol.