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 RE: Sybase CHAINED v. UNCHAINED solution
Date Thu, 30 Dec 2004 18:34:27 GMT
Clinton,

The biggest help you could give the Sybase code would be to expose the
StandardDaoManager.isExplicitTransaction method through the DaoManager
interface, and to expose a similar method through the SqlMapClient
interface (looks to be a little more difficult). Knowing the
explicit/implicit transaction state is the key to avoiding BEGIN
TRANSACTION statements, which prevents DDL execution. 

Right now, I'm proxying the DaoManager and SqlMapClient interfaces in
order to capture explicit/implicit transaction state, a
less-than-optimal solution.

----
Scott Severtson
Centare Group, LLC


> -----Original Message-----
> From: Clinton Begin [mailto:clinton.begin@gmail.com] 
> Sent: Thursday, December 30, 2004 12:12 PM
> To: ibatis-user-java@incubator.apache.org
> Subject: Re: Sybase CHAINED v. UNCHAINED solution
> 
> Hi Scott,
> 
> Thanks for this.  We have two or three possibilities for hosting this.
>  None are currently set up fully, but we're getting there.
> 
> 1) CVS:  We do have a contributor section in CVS right now. 
> Unfortunately, that's about as far as it goes.  We'll have to find a
> way to manage releases of contributed code.
> 
> 2) Wiki:  A wiki was recently set up, and I've just suggested that we
> have a contributor section where we can post stuff like this for you.
> 
> 3)  Website:  I'd like to eventually see a 3rd party resource section
> of the website that's kept up-to-date.  We've already started
> something like that on the draft Apache homepage.
> 
> One way or another, we'll get this up!
> 
> Clinton
> 
> 
> On Wed, 29 Dec 2004 12:09:07 -0600, Severtson, Scott (Associate)
> <Scott.Severtson@qg.com> wrote:
> > All,
> > 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 release the code to the public
> > domain.
> > 
> > However, they don't want to host it; any suggestions on 
> where to post
> > the code?
> > 
> > For the time being, anyone in desperate need can email me at
> > scott.severtson@qg.com.
> > 
> > ----
> > 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.
> >
> 

Mime
View raw message