Explicit Transactions
An explicit transaction is one in which you explicitly define both the start and end of the transaction. Explicit transactions were also called user-defined or user-specified transactions in SQL Server 7.0 or earlier.
DB-Library applications and Transact-SQL scripts use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQL statements to define explicit transactions.
- BEGIN TRANSACTION
Marks the starting point of an explicit transaction for a connection.
- COMMIT TRANSACTION or COMMIT WORK
Used to end a transaction successfully if no errors were encountered. All data modifications made in the transaction become a permanent part of the database. Resources held by the transaction are freed.
- ROLLBACK TRANSACTION or ROLLBACK WORK
Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed.
You can also use explicit transactions in OLE DB. Call the ITransactionLocal::StartTransaction method to start a transaction. Call either the ITransaction::Commit or ITransaction::Abort method with fRetaining set to FALSE to end the transaction without automatically starting another transaction.
In ADO, use the BeginTrans method on a Connection object to start an explicit transaction. To end the transaction, call the Connection object's CommitTrans or RollbackTrans methods.
In the ADO.NET SqlClient managed provider, use the BeginTransaction method on a SqlConnection object to start an explicit transaction. To end the transaction, call the Commit() or Rollback() methods on the SqlTransaction object.
The ODBC API does not support explicit transactions, only autocommit and implicit transactions.
Explicit transaction mode lasts only for the duration of the transaction. When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started, either implicit or autocommit mode.
Note
Under a multiple active result sets (MARS) session, an explicit transaction started with the Transact-SQL BEGIN TRANSACTION statement becomes a batch-scoped transaction. If the batch-scoped transaction is not committed or rolled back when the batch completes, SQL Server automatically rolls back the transaction. For more information, see Controlling Transactions (Database Engine) and Transactions (Transact-SQL).
See Also
Other Resources
BEGIN TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
COMMIT WORK (Transact-SQL)
Performing Transactions (ODBC)
Supporting Local Transactions