BEGIN TRANSACTION Statement [T-SQL]

Use this statement to begin a user-defined transaction.

Note: BEGIN TRANSACTION is a T-SQL construct and must contain only valid T-SQL commands. You cannot mix T-SQL and non-T-SQL commands.

Quick Links:

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

BEGIN TRAN [SACTION] [ transaction-name ]

Examples

(back to top)

Usage

(back to top)

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 and SAP Sybase IQ have two transaction modes.

The default Adaptive Server 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/2003 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 connections in SAP Sybase IQ is On, in which case SAP Sybase IQ runs in chained mode. (ODBC users should also check the AutoCommit ODBC setting). 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.

Note: When calling a stored procedure, you should ensure that it operates correctly under the required transaction mode.

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.

Standards

(back to top)

  • SQL—Vendor extension to ISO/ANSI SQL grammar.
  • SAP Sybase Database product—Supported by Adaptive Server.

Permissions

(back to top)

None

Related reference
COMMIT Statement
ROLLBACK TRANSACTION Statement [T-SQL]
SAVE TRANSACTION Statement [T-SQL]
ISOLATION_LEVEL Option