JDBC/XA changes to better support MSSQL Server

[weston.price]

Continued discussion

[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.

[adrian.brock]

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();

[weston.price]

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

[adrian.brock]

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.

[adrian.brock]

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).

[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?

[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.

[adrian.brock]

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).

[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.