3

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:

  1. these concepts are simply aliases of one another (and what is the correspondence)

or

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

conn.setAutoCommit(true);

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

  • Your assumption about the Sybase info is correct. Individual statements are unchained. To start a chained set of transactions, you have to explicit use a BEGIN TRANSACTION statement; then, statements are chained until a commit or rollback is issued (or the connection is terminated). – RDFozz Mar 1 '17 at 21:46
0

AutoCommit true is identical to chained off

AutoCommit false is identical to chained on

In chained mode your first statement will start an implicit transaction, which will stay active till you manually commit or rollback.

Even though the manual says the default mode is unchained this is not true for client libraries. The JDBC and Python drivers use a default mode of chained unless you overwrite that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.