Scripts and Transactions in Transact-SQL

Last week, we discussed batches in relation to Transact-SQL statements. This week, we touch on scripting and begin our examination of transactions. This article, the second of a three-part series, is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281).

Contributed by McGraw-Hill/Osborne
Rating:

/ 6
August 17, 2006

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Transactions

Even from the very name of the Transact-SQL language, you can conclude that transactions play a major role in SQL Server. They are an important mechanism for enforcing the consistency and integrity of the database.

A transaction is the smallest unit of work in SQL Server. To qualify a unit of work as a transaction, it must satisfy the following four criteria, often referred to as the ACID test:

Atomicity All data changes must be completed successfully, or none of them will be written permanently to the database.
Consistency After a transaction, the database must be left in a consistent state. All rules must be applied during processing to ensure data integrity. All constraints must be satisﬁed. All internal data structures must be left in an acceptable state.
Isolation Changes to the database made by a transaction must not be visible to other transactions until the transaction is complete. Before the transaction is committed, other transactions should see the data only in the state it was in before the transaction.
Durability Once a transaction is completed, changes must not revert even in the case of a system failure.

Autocommit Transactions

In fact, every Transact-SQL statement is a transaction. When it is executed, it either finishes successfully or is completely abandoned. To illustrate this, let’s try to delete all records from the EqType table. Take a look at the following diagram:

A foreign key relationship exists between the EqType and Equipment tables. The foreign key prevents the deletion of records in the EqType table that are referenced by records in the Equipment table.

Let’s try to delete them anyway. You can see the result of such an attempt in Figure 5-5.

Two Select statements that will count the number of records in EqType are placed around the Delete statement. As expected, the Delete statement is aborted because of the foreign key. The count of records before and after the Delete statement is the same, which confirms that all changes made by the Delete statement were canceled. So the database remains in the state that it was in before the change was initiated.

If there were no errors, SQL Server would automatically commit the transaction (that is, it would record all changes) to the database. This kind of behavior is called autocommit.

Figure 5-5. Complete failure of attempt to delete records

In this case, SQL Server deleted records one after the other from the EqType table until it encountered a record that could not be deleted because of the foreign key relationship, at which point the operation was canceled.

Explicit and Implicit Transactions

Explicit Transactions

The most popular and obvious way to use transactions is to give explicit commands to start or finish the transaction. Transactions started in this way are called explicit transactions. You can group Transact-SQL statements into a single transaction using the following statements:

Begin Transaction
Rollback Transaction
Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, these changes are committed to the database.

I will demonstrate the use of these processes on the ap_LeaseSchedule_Clear stored procedure. Its main purpose is to set monthly lease amounts to zero for each asset associated with an expired lease schedule. It also sets the total of the lease amounts to zero. These two operations must be performed simultaneously to preserve the integrity of the database.

Create Procedure ap_LeaseShedule_Clear
-- Set value of Lease of all equipment
-- associated with expired Lease Schedule to 0.
-- Set total amount of Lease Schedule to 0.

@intLeaseScheduleId int
As

Begin Transaction

-- Set value of Lease of all equipment
-- associated with expired Lease Schedule to 0
Update dbo.Inventory
Set Lease = 0
Where LeaseScheduleId = @intLeaseScheduleId If @@Error <> 0 goto PROBLEM

-- Set total amount of Lease Schedule to 0 Update dbo.LeaseSchedule
Set PeriodicTotalAmount = 0
Where ScheduleId = @intLeaseScheduleId
If @@Error <> 0 goto PROBLEM

Commit Transaction
Return 0

PROBLEM:
Print ' Unable to eliminate lease amounts from the database!'
Rollback Transaction
Return 1

Before the real processing starts, the Begin Transaction statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two Update statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the Commit Transaction statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by If statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing.

We will review more complex transactions (including nested transactions) and ways to process errors in the next chapter.

Implicit Transactions

The third transaction mode is called the implicit transaction. To use this mode, you must set the Set Implicit_Transactions On statement for the connection. Any of the following statements will serve as an implicit start to a transaction:

Alter Table Create Delete Drop

Fetch Grant Insert Open

Revoke Select Truncate Table Update

To finish the transaction, you must use the Commit Transaction or Rollback Transaction statement. After that, any of the preceding commands will start a new implicit transaction.

Transaction Processing Architecture

An explanation of how transactions are implemented in Microsoft SQL Server will give you some insight into many processes.

Every change to the database is recorded in a transaction log before it is written to the appropriate tables. Transaction logs are implemented in separate files (or sets of files) with the extension .ldf. All modifications are written to this file chronologically. The records in this transaction log can later be used to roll back the transaction (thus providing atomicity) or to commit the changes to the database (thus providing durability). Two types of records can be stored in transaction logs:

Logical operations performed (for instance, Insert, Update, Delete, and start of transaction)
Before and after images of the changed data (that is, copies of data before and after the change is made)

NOTE
The transaction log does not record queries that are executed against the database (since they do not modify its content).

The transaction log mechanism helps to resolve data integrity issues such as:

If a client application loses its connection before a transaction is ﬁnished, SQL Server will detect a problem and roll back changes to ensure consistency.
If the machine loses power during processing, SQL Server will recover the database when services are restored. All transactions that were recorded in the transaction log in an undivided manner (that is, as part of a complete transaction set) are rolled forward (written to data tables) as if nothing unusual has happened. All transactions that were not completed before the problem occurred are rolled back (deleted) from the database.

NOTE
The transaction log also plays an important role in the implementation of backups in SQL Server. When a user starts a full backup, SQL Server records a complete snapshot of the data tables in backup files. At that point, SQL Server marks the current position in the transaction log and continues to record all changes to the database in the transaction log. Transactions logged during the process are also recorded as part of the full backup. When the backup is complete, SQL Server makes another mark in the transaction log. At the time of the next backup, a transaction log backup will suffice. To restore the database, an administrator first uses the full backup and then one or more transaction log backups that have been run since the full backup. SQL Server runs through the transaction log and applies changes to the data tables.

Nested Transactions

SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete:

Begin transaction
...
    Begin transaction
    ...
    Commit transaction
...
Commit transaction

Usually this situation occurs when one stored procedure containing a transaction calls another stored procedure that also contains a transaction. In the following example, ap_Order_Complete_1 completes an order by setting its completion date and changing the status of the order, and then looping through associated order items and calling ap_OrderItem_Complete_1 to complete each of them. ap_OrderItem_Complete_1 sets the completion date of an order item to the last ChargeLog date associated with that OrderItem. Both of these procedures contain a transaction.

Create Procedure ap_Order_Complete_1
-- Complete all orderItems and then complete order
@intOrderId int,
@dtsCompletionDate smalldatetime
As
set nocount on

Declare @intErrorCode int,
        @i int,
        @intCountOrderItems int,
        @intOrderItemId int
Select @intErrorCode = @@Error

If @intErrorCode = 0
Begin Transaction

-- complete order
If @intErrorCode = 0
Begin
    Update dbo.[Order]
     Set CompletionDate = @dtsCompletionDate,
         OrderStatusId = 4 -- completed
     Where OrderId = @intOrderId

Select @intErrorCode = @@Error
End

-- loop through OrderItems and complete them
If @intErrorCode = 0
Begin
     Create Table #OrderItems(
          id int identity(1,1),
          OrderItemId int)

Select @intErrorCode = @@Error
End

-- collect orderItemIds
If @intErrorCode = 0
Begin
     Insert Into #OrderItems(OrderItemId)
          Select ItemId
          From dbo.OrderItem
          Where OrderId = @intOrderId
          Select @intErrorCode = @@Error End

If @intErrorCode = 0
Begin
     Select @intCountOrderItems = Max(Id),
            @i = 1
     From #OrderItems

Select @intErrorCode = @@Error
End

while @intErrorCode = 0 and @i <= @intCountOrderItems
Begin
     If @intErrorCode = 0
     Begin
          Select @intOrderItemId = OrderItemId
          From #OrderItems
          Where id = @i
          Select @intErrorCode = @@Error
     End

If @intErrorCode = 0
Exec @intErrorCode = dbo.ap_OrderItem_Complete_1 @intOrderItemId

If @intErrorCode = 0
Set @i = @i + 1
End

If @intErrorCode = 0 and @@trancount > 0
Commit Transaction
Else
Rollback Transaction
return @intErrorCode
Go

create Procedure dbo.ap_OrderItem_Complete_1
-- Set CompletionDate of OrderItem to date -- of last ChargeLog record associated with OrderItem.
@intOrderItemId int
As
set nocount on
Declare @intErrorCode int
Select @intErrorCode = @@Error

If @intErrorCode = 0
Begin Transaction

-- Set CompletionDate of OrderItem to date -- of last ChargeLog record associated with OrderItem.
If @intErrorCode = 0
Begin
     update dbo.OrderItem
     Set CompletionDate = (Select Max(ChargeDate)
                           from dbo.ChargeLog
                           where ItemId = @intOrderItemId)
    Where ItemId = @intOrderItemId

Select @intErrorCode = @@Error
End

If @intErrorCode = 0
Begin
exec @intErrorCode = dbo.ap_NotifyAccounting @intOrderItemId
End

If @intErrorCode = 0 and @@trancount > 0
Commit Transaction
Else

Rollback Transaction
Return @intErrorCode

In the case of nested transactions, no Commit statements except the outer one will save changes to the database. Only after the last transaction is committed will all changes to the database become permanent. Up to that point, it is still possible to roll back all changes.

The interesting question is how SQL Server knows which transaction is the last one. It keeps the number of opened transactions in the @@trancount global variable for each user connection. When SQL Server encounters a Begin Transaction statement, it increments the value of the @@trancount, and when SQL Server encounters a Commit Transaction statement, it decrements the value of the @@trancount. Therefore, the only effect of a nested (internal) Commit Transaction statement is a change to the @@trancount value. Only the outer Commit Transaction statement (when @@trancount = 1) stores changes in data tables rather than in the transaction log.

The following is a purely academic example that does not perform any real processing, but it demonstrates the effect of nested transactions on the @@trancount global variable:

print 'Trancount = ' + Convert(varchar(4), @@trancount)
BEGIN TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
    BEGIN TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
    COMMIT TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
COMMIT TRANSACTION
print 'Trancount = ' + Convert(varchar(4), @@trancount)

Each transactional statement will increment and decrement the @@trancount:

Trancount = 0
Trancount = 1
Trancount = 2
Trancount = 1
Trancount = 0

An interesting inconsistency to observe is in the behavior of the Rollback Transaction statement. No matter how many transaction levels deep execution extends, the Rollback Transaction statement will cancel all changes caused by all transactions (and bring the @@trancount value down to zero). In fact, if you execute an additional Rollback Transaction statement after the first one, SQL Server will report an error.

print 'Trancount = ' + Convert(varchar(4), @@trancount)
BEGIN TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
    BEGIN TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
    ROLLBACK TRANSACTION
    print 'Trancount = ' + Convert(varchar(4), @@trancount)
ROLLBACK TRANSACTION
print 'Trancount = ' + Convert(varchar(4), @@trancount)

The following is the result of this example:

Trancount = 0
Trancount = 1
Trancount = 2
Trancount = 0
Server: Msg 3903, Level 16, State 1, Line 8 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Trancount = 0

TIP
I have to admit that I had many problems with this issue at one time. Be careful.

To prevent this error, you need to test for the value of the @@trancount variable before you execute the Rollback Transaction statement. A simple way to test for this value works something like this:

if @@trancount > 0
Rollback Transaction

You will find a much better solution in Chapter 6.

Please check back next week for the conclusion of this article.