ASA SQL User's Guide
Using Transactions and Isolation Levels
Introduction to transactions
Adaptive Server Anywhere expects you to group your commands into transactions. Knowing which commands or actions signify the start or end of a transaction lets you take full advantage of this feature.
Transactions start with one of the following events:
The first statement following a connection to a database
The first statement following the end of a transaction
Transactions complete with one of the following events:
A COMMIT statement makes the changes to the database permanent.
A ROLLBACK statement undoes all the changes made by the transaction.
A statement with a side effect of an automatic commit is executed: data definition commands, such as ALTER, CREATE, COMMENT, and DROP all have the side effect of an automatic commit.
A disconnection from a database performs an implicit rollback.
ODBC and JDBC have an autocommit setting that enforces a COMMIT after each statement. By default, ODBC and JDBC require autocommit to be on, and each statement is a single transaction. If you want to take advantage of transaction design possibilities, then you should turn autocommit off.
For more information on autocommit, see Setting autocommit or manual commit mode.
Setting the database option CHAINED to OFF is similar to enforcing an autocommit after each statement. By default, connections that use jConnect or Open Client applications have CHAINED set to OFF.
For more information, see Setting autocommit or manual commit mode, and CHAINED option [compatibility].
Interactive SQL lets you control when and how transactions from your application terminate:
If you set the option AUTO_COMMIT to ON, Interactive SQL automatically commits your results following every successful statement and automatically perform a ROLLBACK after each failed statement.
The setting of the option COMMIT_ON_EXIT controls what happens to uncommitted changes when you exit Interactive SQL. If this option is set to ON (the default), Interactive SQL does a COMMIT; otherwise it undoes your uncommitted changes with a ROLLBACK statement.
Adaptive Server Anywhere also supports Transact-SQL commands such as BEGIN TRANSACTION, for compatibility with Sybase Adaptive Server Enterprise. For further information, see Transact-SQL Compatibility.