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 earlier versions of Microsoft® SQL Server™.

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.

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.

See Also