Re: Sybase CHAINED v. UNCHAINED solution

[Brandon Goodin <br...@gmail.com>]

I'll start a thread on this on the developers list and see what we can
get setup.

Brandon


On Tue, 28 Dec 2004 14:58:17 -0600, Severtson, Scott (Associate)
<Sc...@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 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@qg.com.
> 
> --Scott Severtson
> 
> 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 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
> 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.
>

Re: Sybase CHAINED v. UNCHAINED solution

[Clinton Begin <cl...@gmail.com>]

I've already put this up on the wiki (feel free to attach code to the page).

Clinton


On Sat, 1 Jan 2005 12:38:27 -0700, Brandon Goodin
<br...@gmail.com> wrote:
> actually, you could attache it to a support request in JIRA and ask
> that it be added to the "contibutor" section of the ibatis source.
> 
> On Sat, 1 Jan 2005 12:36:25 -0700, Brandon Goodin
> <br...@gmail.com> wrote:
> > I'll start a thread on this on the developers list and see what we can
> > get setup.
> >
> > Brandon
> >
> >
> > On Tue, 28 Dec 2004 14:58:17 -0600, Severtson, Scott (Associate)
> > <Sc...@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 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@qg.com.
> > >
> > > --Scott Severtson
> > >
> > > 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 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
> > > 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.
> > >
> >
>

Re: Sybase CHAINED v. UNCHAINED solution

[Brandon Goodin <br...@gmail.com>]

actually, you could attache it to a support request in JIRA and ask
that it be added to the "contibutor" section of the ibatis source.


On Sat, 1 Jan 2005 12:36:25 -0700, Brandon Goodin
<br...@gmail.com> wrote:
> I'll start a thread on this on the developers list and see what we can
> get setup.
> 
> Brandon
> 
> 
> On Tue, 28 Dec 2004 14:58:17 -0600, Severtson, Scott (Associate)
> <Sc...@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 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@qg.com.
> >
> > --Scott Severtson
> >
> > 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 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
> > 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.
> >
>