Database Reference
In-Depth Information
Transaction State
In the absence of failures, all transactions complete successfully. However, a transaction may not always
complete its execution successfully. Such a transaction is termed
aborted.
A
transaction that completes its execution successfully is said to be
committed.
Figure 8-1 shows
that if a transaction has been partially committed, it will be committed but only if it has not failed; and if
the transaction has failed, it will be aborted.
Figure 8-1.
States of a transaction
Specifying Transaction Boundaries
SQL Server transaction boundaries help you identify when SQL Server transactions start and end by
using 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 T-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
SOLClient
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 T-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
SOLEndTran
function to end the transaction.
T-SQL Statements Allowed in a Transaction
You can use all T-SQL statements in a transaction, except for the following statements:
ALTER DATABASE
,
RECONFIGURE
,
BACKUP
,
RESTORE
,
CREATE DATABASE
,
UPDATE STATISTICS
, and
DROP DATABASE
.
