A
transaction is an atomic unit of work. All of the DML operations within a
transaction either succeed or fail as a group. The most important type of
transactions deal with changes from INSERT/UDPATE/DELETE. But even a SELECT
statement is part of a transaction, albeit a read only one.
There are 3
modes of transactions. The first is auto commit. This is the default. DML
statements get changes made permanent via an implied COMMIT. The second mode is
implicit. You issue a bunch of DML statements, following by a COMMIT statement.
The third mode is explicit. In this mode you start the transaction with BEGIN
TRAN, perform DML, and end it with a COMMIT TRAN.
If you do not wish to COMMIT the changes in a
transaction, you can issue a ROLLBACK command. It will undo any changes from
the transaction. Transactions can be nested. But a single ROLLBACK will undo
all transactions that are present in the session. You can check the
@@TRANSCOUNT to find out how far deep you are into nested transactions. A value
of 0 means you are not in a transaction. There is also a XACT_STATE() function
that will return 0 if there is no transaction, 1 if there is an uncommitted
transaction, and -1 on a fatal error.
