Transaction Control

Transactions are an integral part of a relational database system and they help define a single unit of work. This unit of work can include one or more Transact-SQL statements, which are either committed or rolled-back as a group. This all-or-none functionality helps prevent partial updates or inconsistent data states. A partial update occurs when one part of an interrelated process is rolled  back or cancelled without rolling back or reversing all of the other parts of the interrelated processes.

A transaction is bound by the ACID test. ACID stands for Atomicity, Consistency, Isolation (or Independence), and Durability:

• Atomicity means that the transactions are an all-or-nothing entity—carrying out all steps or none at all

• Consistency ensures that the data is valid both before and after the transaction. Data integrity must be          maintained (foreign key references, for example) and internal data structures need to be in a valid state.

• Isolation is a requirement that transactions not be dependent on other transactions that may be taking place concurrently (either at the same time or overlapping). One transaction can’t see another transaction’s data that is in an intermediate state, but instead sees the data as it was either before the transaction began or after.

• Durability means that the transaction’s effects are permanent after the transaction has committed,

and any changes will survive system failures.

In this chapter, I demonstrate and review the SQL Server 2005 mechanisms and functionality that are used to ensure ACID test compliance, namely locking and transactions.

There are three possible transactions types in SQL Server 2005: autocommit, explicit, or implicit.

Autocommit is the default behavior for SQL Server 2005, where each separate Transact-SQL statement you execute is automatically committed after it is finished. For example, if you have two INSERT statements, with the first one failing and the second one succeeding, the second change is maintained because each INSERT is automatically contained in its own transaction. Although this mode frees the developer from having to worry about explicit transactions, depending on this mode for transactional activity can be a mistake. For example if you have two transactions, one that credits an account, and another that debits it, and the first transaction failed, you'll have a debit without the credit. This may make the bank happy, but not necessarily the customer, who had their account debited! Autocommit is even a bit dangerous for ad hoc administrative changes—for example if you accidentally delete all rows from a table, you don’t have the option of rolling back the transaction after you’ve realized the mistake. Implicit transactions occur when the SQL Server session is in implicit transaction mode, and when one of the following statements is first executed:

--------------------------------------------------------------------------------

ALTER TABLE            FETCH           REVOKE

CREATE                      GRANT          SELECT

DELETE                       INSERT         TRUNCATE TABLE

DROP                          OPEN             UPDATE

--------------------------------------------------------------------------------

A new transaction is automatically created (opened) once any of the aforementioned statements are executed, and remains open until either a ROLLBACK or COMMIT statement is issued. The initiating command is included in the open transaction.

Implicit mode is activated by executing the following command in your query session:

SET IMPLICIT_TRANSACTIONS ON

To turn this off (back to explicit mode), execute:

SET IMPLICIT_TRANSACTIONS OFF

Implicit mode can be very troublesome in a production environment, as application designers and end-users could forget to commit transactions, leaving them open to block other connections (more on blocking later in the chapter).

Explicit transactions are those that you define yourself. This is by far the recommended mode of operation when performing data modifications for your database application. This is because you explicitly control which modifications belong to a single transaction, as well as the actions that are performed if an error occurs. Modifications which must be grouped together are done using your own instruction.

Explicit transactions use the following Transact-SQL commands and keywords described in

Command	Description
BEGIN TRANSACTION	Sets the starting point of an explicit transaction
ROLLBACK TRANSACTION	Restores original data modified by a transaction, and brings data back to the state it was in at the start of the transaction.Resources held by the transaction are freed.
COMMIT TRANSACTION	Ends the transaction if no errors were encountered and makes changes permanent. Resources held by the transaction are freed.
BEGIN DISTRIBUTED TRANSACTION	Allows you to define the beginning of a distributed transaction to be managed by Microsoft Distributed Transaction Coordinator (MS DTC). MS DTC must be running locally and remotely.
SAVE TRANSACTION	SAVE TRANSACTION issues a savepoint within a transaction,which allows one to define a location to which a transactioncan return if part of the transaction is cancelled. A transaction must be rolled back or committed immediately after rolling back to a savepoint.
@@TRANCOUNT	Returns the number of active transactions for the connection. BEGIN TRANSACTION increments @@TRANCOUNT by 1, and ROLLBACK TRANSACTION and COMMIT TRANSACTION decrements @@TRANCOUNT by 1. ROLLBACK TRANSACTION to a savepoint has no impact.

 Using Explicit Transactions
This recipe’s example demonstrates how to use explicit transactions to commit or rollback the data
modification depending on the return of an error in a batch of statements:
===================================================================
-- Before count
SELECT COUNT(*) BeforeCount FROM HumanResources.Department
-- Variable to hold the latest error integer value
DECLARE @Error int
BEGIN TRANSACTION
INSERT HumanResources.Department
(Name, GroupName)
VALUES ('Accounts Payable', 'Accounting')
SET @Error = @@ERROR
IF (@Error<> 0) GOTO Error_Handler
INSERT HumanResources.Department
(Name, GroupName)
VALUES ('Engineering', 'Research and Development')
SET @Error = @@ERROR
IF (@Error <> 0) GOTO Error_Handler
COMMIT TRAN
Error_Handler:
IF @Error <> 0

BEGIN
ROLLBACK TRANSACTION
END
-- After count
SELECT COUNT(*) AfterCount FROM HumanResources.Department
===================================================================


BeforeCount
-----------
18
(1 row(s) affected)
(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 14
Cannot insert duplicate key row in object 'HumanResources.Department'
➥ with unique index 'AK_Department_Name'.
The statement has been terminated.
AfterCount
-----------
18
(1 row(s) affected)


How It Works
The first statement in this example validated the count of rows in the HumanResources.Department
table, returning 18 rows:
-- Before count
SELECT COUNT(*) BeforeCount FROM HumanResources.Department
A local variable is created to hold the value of the @@ERROR function (which captures the latest
error state of a SQL statement):
-- Variable to hold the latest error integer value
DECLARE @Error int
Next, an explicit transaction was started:
BEGIN TRANSACTION
The next statement attempted an INSERT into the HumanResources.Department table. There was
a unique key on the department name, but because the department name didn’t already exist in the
table, the insert succeeded:
INSERT HumanResources.Department
(Name, GroupName)
VALUES ('Accounts Payable', 'Accounting')
Next was an error handler for the INSERT:
SET @Error = @@ERROR
IF (@Error <> 0) GOTO Error_Handler


This line of code evaluates the @@ERROR function. The @@ERROR system function returns the last
error number value for the last executed statement within the scope of the current connection. The
IF statement says that if an error occurs, the code should jump to (using GOTO) the Error_Handler
section of the code.

GOTO is a keyword that helps you control the flow of statement execution. The identifier after
GOTO, Error_Handler, is a user-defined code section.
Next, another insert is attempted, this time for a department that already exists in the table.
Because the table has a unique constraint on the name column, this insert will fail:
INSERT HumanResources.Department
(Name, GroupName)
VALUES ('Engineering', 'Research and Development')
The failure will cause the @@ERROR following this INSERT to be set to a non-zero value. The IF
statement will then evaluate to TRUE, which will invoke the GOTO, thus skipping over the COMMIT TRAN
to the Error_Handler section:
SET @Error = @@ERROR
IF (@Error <> 0) GOTO Error_Handler

COMMIT TRAN
Following the Error_Handler section is a ROLLBACK TRANSACTION:
Error_Handler:
IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION
END
Another count is performed after the rollback, and again, there are only 18 rows in the database.
This is because both INSERTs were in the same transaction, and one of the INSERTs failed. Since
a transaction is all-or-nothing, no rows were inserted:
-- After count
SELECT COUNT(*) AfterCount FROM HumanResources.Department
Some final thoughts and recommendations regarding how to handle transactions in your
Transact-SQL code or through your application:

• Keep transaction time short as possible for the business process at hand. Transactions that
remain open can hold locks on resources for an extended period of time, which can block
other users from performing work or reading data (see later on in the chapter for a review of
locking and blocking).

• Minimize resources locked by the transaction. For example, update only tables that are

related to the transaction at hand. If the data modifications are logically dependent on each

other, they belong in the same transaction. If not, the unrelated updates belong in their own

transaction.

• Add only relevant Transact-SQL statements to a transaction. Don’t add extra lookups or
updates that are not germane to the specific transaction. Executing SELECT statement within
a transaction can create locks on the referenced tables, which can in turn block other
users/sessions from performing work or reading data.
• Do not open new transactions that require user or external feedback within the transaction.
Open transactions can hold locks on resources, and user feedback can take an indefinite
amount of time to receive. Instead, gather user feedback before issuing an explicit transaction.