Collection Contents Index Describing result sets CHAPTER 10.  Using Procedures, Triggers, and Batches pdf/chap9.pdf

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 Info     For information about transactions, see Using Transactions and Locks.

This section describes a few aspects of transactions in applications.

Top of page  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 

Top of page  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.

Top of page  Cursors and transactions

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

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.


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.

Top of page  

Collection Contents Index Describing result sets CHAPTER 10.  Using Procedures, Triggers, and Batches pdf/chap9.pdf