[Apache iBATIS Wiki] New: Scott's Sybase Proc Solution

[ib...@incubator.apache.org]

   Date: 2004-12-31T22:33:01
   Editor: ClintonBegin
   Wiki: Apache iBATIS Wiki
   Page: Scott's Sybase Proc Solution
   URL: http://wiki.apache.org/ibatis/Scott's Sybase Proc Solution

   no comment

New Page:

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

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