Handling Long-Running Transactions

Long-running transactions are transactions that create and hold locks for a long time. They include large-scale updates, deletes, and massive inserts. Monthly maintenance programs that move data into an archive directory are a typical example. For instance, Listing 13.7 would move the Orders and Order Details for a month from active to (make-believe) archive tables.

Code Listing 13.7. An Example of a Long-Running Transaction

 1: set xact_abort on
 2: begin transaction
 3: declare @OrderList table (OrderID int)
 4: insert @OrderList (OrderID)
 5: select OrderID
 6:   from Orders with (readpast)
 7:  where OrderDate >= '8/1/2000'and
 8:        OrderDate < '9/1/2000'
 9: insert into Orders_Archive (
10:        OrderID, CustomerID, EmployeeID, OrderDate,
11:        RequiredDate, ShippedDate, ShipVia, Freight,
12:        ShipName, ShipAddress, ShipCity, ShipRegion,
13:        ShipPostalCode, ShipCountry, OrderClosed
14: )
15: select
16:        OrderID, CustomerID, EmployeeID, OrderDate,
17:        RequiredDate, ShippedDate, ShipVia, Freight,
18:        ShipName, ShipAddress, ShipCity, ShipRegion,
19:        ShipPostalCode, ShipCountry, OrderClosed
20:   from Orders
21:  where OrderID in (select OrderID from @OrderList)
22: insert [Order Details_Archive]
23:       (OrderID, ProductID, UnitPrice, Quantity, Discount)
24: select OrderID, ProductID, UnitPrice, Quantity, Discount
25:   from [Order Details]
26:  where OrderID in (select OrderID from @OrderList)
27: delete [Order Details]
28:  where OrderID in (select OrderID from @OrderList)
29: delete Orders
30:  where OrderID in (select OrderID from @OrderList)
31: commit transaction
					

Line 3 declares a table variable. Lines 4 through 8 write a list of OrderIDs to the table variable. We'll use that list of IDs as a reference in the rest of the batch. Notice the use of the readpast hint. If an OrderID is locked, the query will ignore the lock and continue to find orders. You might also want to use the xlock table hint to acquire persistent, exclusive locks.

The INSERT statement in lines 9 through 21 moves the Orders rows to the Orders_ Archive. Lines 22 through 26 moves Order details to the Order Details_Archive. Lines 27 through 30 deletes the rows, first from Order Details, then from Orders (to preserve DRI). The batch is written as a transaction to avoid performing partial work.

Long-running transactions are a significant performance concern for system designers and application programmers. Here's why:

• These processes typically modify a large percentage of the data in a table. When that happens, the server often promotes row and page locks to a table lock to minimize lock maintenance overhead. Live, exclusive table locks on active tables force all other processes to wait.

• When you update a large amount of data, you also write a significant number of entries to the transaction log. A large active log takes up more space. The log segment will expand until the transaction is complete, or the disk is full.

  Note

  When you create a database, you decide whether to restrict the growth of the log file(s). If you choose unrestricted file growth, the log file(s) can grow until the logical volume is full. At that point, you get a log-full error message and possibly an out of disk space error as well.

  
  

• If you are running on a single-CPU system, long-running transactions tend to monopolize the processor.

To avoid long-running transactions, break up work into smaller chunks. For instance, when you perform a monthly archive, write the work one day at a time in a loop. Commit the transactions after each day's work, and consider adding a statement to trun cate the log in the loop. Listing 13.8 contains the skeleton of a revised statement, with the inserts and deletes omitted.

Code Listing 13.8. Example of Breaking Up a Long-Running Transaction

 1: set xact_abort on
  2: declare @StartDate datetime
  3: declare @OrderList table (OrderID int)
  4: set @StartDate = '8/1/2000'
  5: while @StartDate < '9/1/2000'
  6: begin
  7:     insert @OrderList (OrderID)
  8:     select OrderID
  9:       from Orders with (readpast)
 10:      where OrderDate >= @StartDate and
 11:            OrderDate < dateadd(dd, 1, @StartDate)
 12:     begin transaction
 13: /*
 14:    inserts and deletes performed here for one day
 15:  */
 16:     commit transaction
 17:     backup transaction Northwind with truncate_only
 18:     set @StartDate = dateadd(dd, 1, @StartDate)
 19: end
 20: backup database Northwind [ ... ]
					

The while loop breaks down each of the large queries into a smaller set of queries. The BACKUP TRANSACTION statement (line 17) truncates the transaction log without performing a backup. This will keep the size of the log under control by freeing up log space between transactions. The BACKUP DATABASE command (line 20) would perform a full backup when the process is complete. Truncating the transaction log disables transactional disaster recovery from that point forward, so you will need to make a new database backup as soon as the process is complete.