Databases Reference
In-Depth Information
9 ROLLBACK TRAN
10 —More processing statements here
11 —Commit or rollback transaction
The BEGIN TRAN in statement 1 is the start of the outer transaction. You add
a row to the CUSTOMER table. The BEGIN TRAN statement in line 3 starts the
inner transaction, which adds a row to the CUSTOMEREMPLOYEE table.
Depending on the value of @@ERROR, which identifies whether or not an error
occurred when the most recent statement executed, you either commit or roll-
back the inner transaction. This does not commit the statement in line 2. The
ROLLBACK TRAN statement acts differently however. If you execute the ROLL-
BACK TRAN in line 9, everything rolls back to the initial BEGIN TRAN statement.
Even though you run the COMMIT TRAN statement in line 7, it doesn't
mean that the change will write to disk. This change is still controlled in the
context of the outer transaction. If, after the statements shown here, you run
COMMIT TRAN, both added rows would write to their respective tables. If you
run ROLLBACK TRAN, all changes, including those in the inner transaction, are
rolled back.
You should try to avoid the use of explicit nested transactions like the pre-
vious example. That's because a transaction should represent a single unit of
work, that is, it should accomplish a single task. Also, as you can see in that
example, there really isn't much benefit to including a nested transaction. So why
even bring them up? Because you might end up using a nested transaction with-
out ever realizing it. You can end up with a nested transaction when calling pro-
cedures that include transactions. For example:
BEGIN TRAN
DECLARE @TestVal int
EXEC p_GenSummary @TestVal OUTPUT
IF @TestVal = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
If the procedure, p_GenSummary, includes a transaction as part of its steps,
that transaction runs as a nested transaction. Even if you commit the changes
inside the procedure, you still need to commit them in the calling batch. If you
roll the changes back in the calling batch, it rolls back the changes made inside
the procedure.
With nested transactions, it's possible to lose track of whether or not you have
any
open transactions,
those that you need to either commit or roll back, at any
point. In SQL Server, you can use @@TRANCOUNT to get a count of open, or
pending, transactions. If @@TRANCOUNT returns zero, then you don't have any
open transactions. If you do have open transactions, it returns a count of trans-
actions. It's a good idea to check before closing an open connection, when using
