Controlling transactions in applications

User's Guide
PART 1. Working with Databases
CHAPTER 9. Using SQL in Applications

Controlling transactions in applications

Transactions are sets of SQL statements that are atomic. Either all the statements in the transaction are executed, or none are.

For information about transactions, see Using Transactions and Locks.

This section describes a few aspects of transactions in applications.

Setting autocommit or manual commit mode

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.

The performance and behavior of your application may change, depending on whether you are running in an autocommit mode. Autocommit is not recommended for most purposes.

You can control autocommit behavior in the database using the CHAINED database option. You can also control autocommit behavior in some database interfaces by setting an autocommit interface option.

Using the CHAINED database option

You can set the current connection to operate in autocommit mode by setting the CHAINED database option to OFF.

By default, CHAINED is set to ON in Adaptive Server Anywhere (manual commit mode).

Setting autocommit mode

ODBC By default, ODBC operates in autocommit mode. You can turn off this mode using the SQL_ATTR_AUTOCOMMIT connection attribute. ODBC autocommit is independent of the CHAINED option.
JDBC By default, JDBC operates in autocommit mode. You can turn off this mode by using the setAutoCommit method of the connection object:
```
conn.setAutoCommit( false );
```
Embedded SQL Embedded SQL uses the setting of the user's CHAINED option to govern the transaction behavior. By default, this option is set to ON (manual commit).
Open Client A connection made through Open Client sets the mode to autocommit by default. You can change this behavior by setting the CHAINED database option to ON.

Controlling the isolation level

The isolation level of a current connection can be set using the ISOLATION_LEVEL database option.

Some interfaces, such as ODBC, allow you to set the isolation level for a connection at connection time. This level can be reset later using the ISOLATION_LEVEL database option.

Cursors and transactions

In general, a cursor is closed when a COMMIT is performed. There are two exceptions to this behavior:

The CLOSE_ON_ENDTRANS database option is set to OFF.
A cursor is opened WITH HOLD.

If either of these two cases is true, the cursor is not closed on a COMMIT.

ROLLBACK and cursors

If a transaction is rolled back, then cursors are closed except for those cursors opened WITH HOLD. However, the contents of any cursor after a rollback should not be relied on.

The draft ISO SQL3 standard states that on a rollback, all cursors should be closed. You can obtain this behavior by setting the ANSI_CLOSE_CURSORS_AT_ROLLBACK option to ON.

Savepoints

If a transaction is rolled back to a savepoint, and if ANSI_CLOSE_CURSORS_AT_ROLLBACK option is set to ON, then all cursors opened after the SAVEPOINT are closed.

Cursors and isolation levels

You can change the isolation level of a connection during a transaction using the SET OPTION statement to alter the ISOLATION_LEVEL option. However, this change does not affect any cursor that is already opened.