There is an interesting dichotomy of transaction types:

Chained transactions

This is the ANSI standard way and conforms to the SQL 92 behavior of implicit transactions. There is no need for an explicit 'begin transaction' statement. You are always in a transaction. When the client connects to the server an invisible BEGIN TRAN is issued. When the client issues a COMMIT TRAN or ROLLBACK TRAN again, an invisible BEGIN TRAN is also immediately issued afterwards.

This means that in chained mode, you never need to issue BEGIN TRAN. Effectively, you close transactions but not open them. Another way to put it is that a transaction automatically begins with the first statement, and no work is committed until COMMIT is explicitly called.

NB: the ANSI standard is explicit in that the chained transaction mode is the default and that a transaction is only closed either with a COMMIT or with a ROLLBACK. However the standard does not specify how to start a transaction leaving the syntax to the vendors (a vendor might also take the view that a new transaction is always implicitly and immediately started following a COMMIT or a ROLLBACK — in such a case no syntax to start a transaction is needed at all).

Unchained transactions (a.k.a. autocommit)

This is the PostgreSQL and Sybase ASE standard/default. Any individual command is atomic (so an update of 1 million rows will update all million or none) just like the case of chained transactions (atomicity for single statements is always maintained). However, each command is automatically commited on execute so atomicity for a group of commands is only possible. if you explicitly issue a BEGIN TRAN. Only then are you in a multi-statement transaction. In PostgreSQL you use BEGIN [WORK|TRANSACTION] and COMMIT [WORK|TRANSACTION] for that. In much the same way, Sybase extends standard AutoCommit with multi-statement transaction support if BEGIN TRANSACTION is explicitly called.

Transaction mode	Default in:	JDBC
Chained	ANSI SQL	Connection#setAutoCommit(false)
Unchained	PostgreSQL, Sybase ASE	Connection#setAutoCommit(true)

The following excrept from a rather old PostgreSQL book (PostgreSQL: Introduction and Concepts, Bruce Momjian, Addisson-Wesley 2001) further corroborates the above:

By default, Postgres executes transactions in unchained mode (also known as autocommit in other database systems). In other words, each user statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done). BEGIN initiates a user transaction in chained mode, i.e., all user statements after BEGIN command will be executed in a single transaction until an explicit COMMIT, ROLLBACK, or execution abort. Statements in chained mode are executed much faster, because transaction start/commit requires significant CPU and disk activity. Execution of multiple statements inside a transaction is also required for consistency when changing several related tables.

When it comes to stored procedures, the precise wording to use is execution mode of a stored procedure. This can be set using the sp_procxmode system stored procedure to one of the following three main values:

• unchained
• chained
• anymode

To see all possible modes, mistype a mode's name in an invocation of sp_procxmode and see also my stack exchange question on this matter.

See my cfa/playground/000-stored-procedure-commit-tests project where I believe I've gotten to the bottom of the chained / unchained / anymode thing.

See also the following stack exchange (database administrators) questions of mine on the matter:

• transaction chaining, isolation levels and atomicity
• relationship between transaction chaining and autocommit

Finally, and unrelated to the above, the concept of transaction chaining is orthogonal to that of the transaction isolation levels.

TODO: add mnemonic for chained / unchained transactions

The Sybase ASE abomination is not a true RDBMS as it lacks support for MVCC. Under MVCC:

• readers never block writers
• writers never block readers

Moreover, in some RDBMSs (e.g. Oracle) it is also possible to ensure that different row writers never block writers (only same-row writers block writers but that is normal). Each user is guaranteed to see a consistent view (snapshot) of the data that was commited at the time the query started.