Starts a user-defined transaction.
BEGIN TRAN[SACTION] [ transaction-name ]
This example illustrates the effect of a BEGIN TRANSACTION statement on the snapshot version of a table.
In the first case, assume that table t1 contains no data. Two connections, Conn1 and Conn2, are made at the same time. The following is a timeline of the commands executed within the two connections:
Conn1 |
Conn2 |
|---|---|
|
|
|
… |
|
… |
… |
Data returned from table t1: 1 |
In the first case, user Conn2 issues a SELECT statement after user Conn1 issues a COMMIT. Since the SELECT of Conn2 is the first command executed following the connect, a transaction begins at this time and a snapshot is taken of table t1 after t1 contains data. User Conn2 can see the updated table.
In the second case, assume again that table t1 contains no data. Two connections, Conn1 and Conn2, are made at the same time. The commands executed by the two users are in the following timeline:
Conn1 |
Conn2 |
|---|---|
|
|
… |
|
|
… |
|
… |
… |
No data returned from table t1 |
In the second case, user Conn2 issues a BEGIN TRANSACTION statement after connecting and IQ takes a snapshot of table t1 before user Conn1 inserts any data. Even though Conn2 issues a SELECT after Conn1 has committed the inserted data, Conn2 still has a snapshot of t1before the data was inserted. In this case, Conn2 cannot see the updated table and the SELECT returns no data. Until the current transaction of user Conn2 ends, the image of table t1 remains unchanged to user Conn2.
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 TRANSACTION PRINT @@trancount COMMIT TRANSACTION PRINT @@trancount
See the “Usage” section for more information on the @@trancount global variable.
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.
BEGIN TRANSACTION creates a transaction for the current connection, if the connection does not currently have a transaction. When a transaction starts, it selects the snapshot version that is used until the next commit or rollback.
Note that a transaction automatically starts at the start of the first command following a connect, commit, or rollback, if there is no explicit BEGIN TRANSACTION.
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.
Adaptive Server Enterprise and Sybase IQ 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 connections in Sybase IQ is ON, in which case IQ runs in chained mode. (ODBC users should also check the AutoCommit ODBC setting.) The default for TDS connections is OFF.
You cannot alter the CHAINED option within a transaction.
WARNING! 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 [TSQL]”.
The current nesting level is held in the global variable @@trancount. The @@trancount variable has a value of zero before a 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.
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. Sybase IQ does not set the @@trancount value to 1 when a transaction is started implicitly. Consequently, the IQ @@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) @@trancount has a value of 1.
For transactions starting with a BEGIN TRANSACTION statement, @@trancount has a value of 1 in both Sybase IQ and Adaptive Server Enterprise after the BEGIN TRANSACTION statement. If a transaction is started implicitly with a different statement, and a BEGIN TRANSACTION statement is then executed, @@trancount has a value of 2 in both Sybase IQ and Adaptive Server Enterprise after the BEGIN TRANSACTION statement.
None.
None.
