Scott Severtson Centare Group, LLC
Note:
As described in the white paper below, we must detect if iBATIS is using implicit or explicit transactions; three different detection methods are included for reference, supporting the DaoManager, the SqlMapClient, and one that supports both DaoManager SqlMapClient, but only without a JIT compilier (included because it requires no changes to *your code* to test, just changing the transaction manager; the other solutions require wrapping a proxy around the DaoManager SqlMapClient). None of these solutions are optimal; however, they work without changing any iBATIS code, which was critical for the client. The active detector implementation can be changed by editing com.qg.open.sybase.ibatis.TransactionTypeDetector.Factory's code to hold an instance of another detector, and usage details are included in the javadocs. A much better solution would be for iBATIS to expose implicit/explicit transaction state at the DaoManager SqlMapClient level.
Unchained and Chained Transaction Modes
Sybase supports two transaction modes: Chained and Unchained.
- Unchained mode is specified by JDBC's Connection.setAutoCommit(true):
insert/update/delete statements are automatically committed on execute. Sybase extends standard AutoCommit with multi-statement transaction support if BEGIN TRANSACTION is explicitly called. This mode is the default.
- Chained mode is specified by JDBC's Connection.setAutoCommit(false),
and conforms to the SQL 92 behavior of implicit transactions: a transaction automatically begins with the first statement, and no work is committed until COMMIT TRANSACTION is explicitly called.
Unchained and Chained modes are (mostly) mutually exclusive. Unless a stored procedure is written with care to support both modes, it will generally only work in one mode.
JDBC's transactional support requires Connection.setAutoCommit(false) to be set, which forces Sybase into Chained mode. However, nearly all existing stored procedures are written exclusively for Unchained mode.
Temporary Tables and Transactions
Sybase by default does not allow CREATE/DROP TABLE statements within transactions, including creating tables on the temporary database. While this can be enabled, Sybase strongly recommends against it: "doing so can slow performance to a halt". See http://tinyurl.com/5clrf for further information.
iBATIS expects Connection.setAutoCommit(false) mode: all calls exist in an implicit, automatic transaction, and must be committed/rolled back. However, two modes are supported, implicit and explicit transactions:
// ***Implicit transaction***
// A Transaction object is automatically retrieved prior to calling getUser
User user = userDao.getUser("fflintstone");
// Transaction.commit() is automatically called after calling getUser
// ***Explicit transaction***
// A Transaction object is explicitly requested daoManager.startTransaction(); userDao.updateUser(user); someOtherDao.updateSomethingElse(user);
// Transaction.commit() is explicitly called daoManager.commitTransaction();
Many existing procedures make use of temporary tables; these procedures cannot be run inside a transaction. Therefore, we must prevent iBATIS's implicit transaction support from actually beginning/committing/rolling back transactions.
Solutions
=======
ConnectionProxy StatementProxy/ProxyUtil
JDBC's Connection.setAutoCommit(false) behavior can be simulated in Unchained mode.
- Call BEGIN TRANSACTION prior to executing any SQL on a connection.
2. Call COMMIT/ROLLBACK TRANSACTION in place of Connection.commit/rollback() However, these manual calls would force knowledge of Sybase's limitations into the data access code, and would not work with iBATIS's transaction support. The Proxy pattern from Design Patterns by Gamma et. al. (page 207) allows us to wrap the real Connection object with our own implementation, which makes the desired BEGIN/COMMIT/ROLLBACK calls on our behalf. The proxy's setAutoCommit method enables/disables this behavior
Detecting implicit and explicit transactions is necessary, as implicit transactions must not actually call BEGIN TRANSACTION. The detector examines the stack trace to determine if startTransaction() was explicitly called, or if iBATIS is making the call on the user's behalf.
SybaseTransaction SybaseTransactionConfig
iBATIS defines Transaction and TransactionConfig interfaces, allowing third-party developers to build in their own transactional support.
public interface Transaction {
public Connection getConnection() throws ...; public void commit() throws ...; public void rollback() throws ...; public void close() throws ...;
}
public interface TransactionConfig {
public Transaction newTransaction() throws ...;
// ...Other methods...
}
Transaction initializes connections and manages transactions, and TransactionConfig builds Transaction objects. The SybaseTransaction implementation automatically wraps a Sybase connection in a ConnectionProxy, and SybaseTransactionConfig builds SybaseTransaction instances. Additionally, SybaseTransactionConfig uses TransactionTypeDetector to specify the setAutoCommit behavior of the ConnectionProxy.
Comments
========
This solution adequately solves the Unchained and Chained Modes problem; any code that uses the ConnectionProxy can use setAutoCommit as JDBC specifies, while the underlying Sybase Connection remains in setAutoCommit(true)/Unchained mode. Stored procedures written for Unchained mode can be can be called from JDBC seamlessly as part of a transaction.
This solution does not completely solve the Temporary Tables and Transactions problem. Stored procedures that use temporary tables must not be called in the ConnectionProxy's setAutoCommit(false) mode, such as inside an explicit iBATIS transaction. This limitation is entirely within the RDBMS, and as such, cannot be resolved by Java code.
Finally, the implementation could have been simplified had ConnectionProxy's transactional support code been placed inside SybaseTransaction. However, the Connection.close() method is not handled through the Transaction interface; cleanup requires us to listen for this call. Additionally, the ConnectionProxy implementation has no dependency on iBATIS, and can be used with other persistence frameworks or straight JDBC calls.