I am trying to understand the relationship between the concepts of transaction chaining and autocommit. Autocommit is usually exposed in a client-side programmatic interface. E.g. in JDBC with the Connection#setAutoCommit method.
Namely I would like to know whether:
- these concepts are simply aliases of one another (and what is the correspondence)
- these concepts are orthogonal and all 4 combinations make sense and yield different results (and what these results are)
There's a number of resources that suggest the case is 1. The same resources seem to suggest that
unchained corresponds to
autocommit on. E.g.
this post says that:
set chained off (default in Sybase, = set autocommit on)
This more extensive post similarly says:
Some database programming interfaces have an autocommit mode, also called unchained mode. In this mode, each statement is a transaction, and is committed after execution. If you wish to use transactions in your applications, you need to be using manual commit mode, or chained mode.
This has been my own understanding so far. For example, whenever I try to execute (using JDBC) a stored procedure and I run into the following message:
java.sql.SQLException: Stored procedure 'whatever' may be run only in unchained transaction mode.
… then setting in my code:
… fixes the problem which seems to confirm that indeed unchained is the same as autocommit.
However looking at the Sybase ASE documentation one reads:
The default mode, called unchained mode or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.
This seems to be the exact opposite of autocommit - unless the above text is understood as having the following meaning:
The default mode, called unchained mode or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction when one wants to place more than a single statement in a transaction.
Finally, I've come across this post which suggests that chaining and autocommit are mostly orthogonal.