ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Severtson, Scott \(Associate\)" <Scott.Severt...@qg.com>
Subject Sybase CHAINED v. UNCHAINED solution
Date Tue, 28 Dec 2004 20:58:17 GMT
Many months ago, moonpool posted a question regarding Sybase's lack of
support for DDL (including temporary tables) when inside a transaction
within iBATIS (http://sourceforge.net/forum/message.php?msg_id=2507161).
I solved this problem for a client transitioning to iBATIS, and posted a
whitepaper about the solution, pasted below. I have finally received
written permission from the client to open source the code.

So, where should I post the code? Anyone want to host it?

For the time being, anyone in desperate need can email me at

--Scott Severtson

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 Dao/SqlMaps, 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 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
* 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
User user = userDao.getUser("fflintstone"); 
// Transaction.commit() is automatically called after calling getUser 
// ***Explicit transaction*** 
// A Transaction object is explicitly requested 
// Transaction.commit() is explicitly called 
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. 
JDBC's Connection.setAutoCommit(false) behavior can be simulated in
Unchained mode.  
1. Call BEGIN TRANSACTION prior to executing any SQL on a connection. 
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
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.

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

View raw message