Controlling Transactions (Database Engine)
Applications control transactions mainly by specifying when a transaction starts and ends. This can be specified using either Transact-SQL statements or database application programming interface (API) functions. The system must also be able to correctly handle errors that terminate a transaction before it completes.
By default, transactions are managed at the connection level. When a transaction is started on a connection, all Transact-SQL statements executed on that connection are part of the transaction until the transaction ends. However, under a multiple active result set (MARS) session, a Transact-SQL explicit or implicit transaction becomes a batch-scoped transaction that is managed at the batch level. When the batch completes, if the batch-scoped transaction is not committed or rolled back, it is automatically rolled back by SQL Server.
Starting Transactions
Using API functions and Transact-SQL statements, you can start transactions in an instance of the SQL Server Database Engine as explicit, autocommit, or implicit transactions. Under a MARS session, Transact-SQL explicit and implicit transactions become batch-scoped transactions.
- Explicit Transactions
Explicitly start a transaction through an API function or by issuing the Transact-SQL BEGIN TRANSACTION statement.
- Autocommit Transactions
The default mode for the Database Engine. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.
- Implicit Transactions
Set implicit transaction mode on through either an API function or the Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement automatically starts a new transaction. When that transaction is completed, the next Transact-SQL statement starts a new transaction.
- Batch-scoped Transactions
Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server.
Transaction modes are managed at the connection level. If one connection changes from one transaction mode to another, it has no effect on the transaction modes of any other connection.
Ending Transactions
You can end transactions with either a COMMIT or ROLLBACK statement, or through an API function.
- COMMIT
If a transaction is successful, commit it. A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. A COMMIT also frees resources, such as locks, used by the transaction.
- ROLLBACK
If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. A ROLLBACK also frees resources held by the transaction.
Note
Under connections enabled to support multiple active result sets (MARS), an explicit transaction started through an API function cannot be committed while there are pending requests for execution. Any attempt to commit this type of transaction while there are outstanding operations running will result in an error.
Specifying Transaction Boundaries
You can identify when Database Engine transactions start and end with Transact-SQL statements or API functions and methods.
- Transact-SQL statements
Use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements to delineate transactions. These are primarily used in DB-Library applications and in Transact-SQL scripts, such as the scripts that are run using the osql command prompt utility.
- API functions and methods
Database APIs such as ODBC, OLE DB, ADO, and the .NET Framework SQLClient namespace contain functions or methods used to delineate transactions. These are the primary mechanisms used to control transactions in a Database Engine application.
Each transaction must be managed by only one of these methods. Using both methods on the same transaction can lead to undefined results. For example, you should not start a transaction using the ODBC API functions, and then use the Transact-SQL COMMIT statement to complete the transaction. This would not notify the SQL Server ODBC driver that the transaction was committed. In this case, use the ODBC SQLEndTran function to end the transaction.
Errors During Transaction Processing
If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. For more information, see SET XACT_ABORT (Transact-SQL).
When errors occur, corrective action (COMMIT or ROLLBACK) should be included in application code. One effective tool for handling errors, including those in transactions, is the Transact-SQL TRY…CATCH construct. For more information with examples that include transactions, see Using TRY...CATCH in Transact-SQL and TRY...CATCH (Transact-SQL).
See Also
Concepts
Transactions (Database Engine)
Other Resources
BEGIN TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
COMMIT WORK (Transact-SQL)
SET IMPLICIT_TRANSACTIONS (Transact-SQL)
Performing Transactions (ODBC)