Reference Manual
CHAPTER 9. SQL Statements
To begin a user-defined transaction.
BEGIN TRAN[SACTION] [ transaction-name ]
None.
None.
The optional parameter transaction-name is the name assigned to this transaction. It must be a valid identifier. Use transaction names only on the outermost pair of nested BEGIN/COMMIT or BEGIN/ROLLBACK statements.
When executed inside a transaction, the BEGIN TRANSACTION statement increases the nesting level of transactions by one. The nesting level is decreased by a COMMIT statement. When transactions are nested, only the outermost COMMIT makes the changes to the database permanent.
Both Adaptive Server Enterprise and Adaptive Server Anywhere have two transaction modes:
The default Adaptive Server Enterprise transaction mode, called unchained mode, commits each statement individually, unless an explicit BEGIN TRANSACTION statement is executed to start a transaction. In contrast, the ISO SQL/92 compatible chained mode only commits a transaction when an explicit COMMIT is executed or when a statement that carries out an autocommit (such as data definition statements ) is executed.
You can control the mode by setting the CHAINED database option. The default setting for ODBC and Embedded SQL connection in Adaptive Server Anywhere is ON, in which case Adaptive Server Anywhere runs in chained mode. The default for TDS connections is OFF.
In unchained mode, a transaction is implicitly started before any data retrieval or modification statement. These statements include: DELETE, INSERT, OPEN, FETCH, SELECT, and UPDATE. You must still explicitly end the transaction with a COMMIT or ROLLBACK statement.
You cannot alter the CHAINED option within a transaction.
Caution
When calling a stored procedure, you should ensure that it operates correctly under the required transaction mode.
For more information about the CHAINED option and the chained mode, see CHAINED option.
The current nesting level is held in the global variable @@trancount. The @@trancount variable has a value of zero before the first BEGIN TRANSACTION statement is executed, and only a COMMIT executed when @@trancount is equal to one makes changes to the database permanent.
A ROLLBACK statement without a transaction or savepoint name always rolls back statements to the outermost BEGIN TRANSACTION (explicit or implicit) statement, and cancels the entire transaction.
SQL/92 Vendor extension
Sybase Supported by Adaptive Server Enterprise.
The following batch reports successive values of @@trancount as 0, 1, 2, 1, 0. The values are printed on the server window.
PRINT @@trancount BEGIN TRANSACTION PRINT @@trancount BEGIN TRANSACTION PRINT @@trancount COMMIT PRINT @@trancount COMMIT PRINT @@trancount
You should not rely on the value of@@trancount for more than keeping track of the number of explicit BEGIN TRANSACTION statements that have been issued.
When Adaptive Server Enterprise starts a transaction implicitly, the @@trancount variable is set to 1. Adaptive Server Anywhere does not set the @@trancount value to 1 when a transaction is started implicitly. Consequently, the Adaptive Server Anywhere @@trancount variable has a value of zero before any BEGIN TRANSACTION statement (even though there is a current transaction), while in Adaptive Server Enterprise (in chained mode) it has a value of 1.
For transactions starting with a BEGIN TRANSACTION statement, @@trancount has a value of 1 in both Adaptive Server Anywhere and Adaptive Server Enterprise after the first BEGIN TRANSACTION statement. If a transaction is implicitly started with a different statement, and a BEGIN TRANSACTION statement is then executed, @@trancount has a value of 1 in Adaptive Server Anywhere, and a value of 2 in Adaptive Server Enterprise after the BEGIN TRANSACTION statement.