Coding Effective Transactions

Poorly written or inefficient transactions can have a detrimental effect on concurrency of access to data and overall application performance. SQL Server can hold locks on a number of resources while the transaction is open ; modified rows will acquire exclusive locks, and other locks might also be held depending on the isolation level used. To reduce locking contention for resources, transactions should be kept as short and efficient as possible. During development, you might not even notice that a problem exists; the problem might become noticeable only after the system load is increased and multiple users are executing transactions simultaneously . Following are some guidelines to consider when coding transactions to minimize locking contention and improve application performance:

Do not return result sets within a transaction. Doing so prolongs the transaction unnecessarily. Perform all data retrieval and analysis outside the transaction.
Never prompt for user input during a transaction. If you do, you lose all control over the duration of the transaction. (I have seen even the best programmers miss this one on occasion.) On the failure of a transaction, be sure to issue the rollback before putting up a message box telling the user that a problem occurred.
Keep the start and end of a transaction together in the same batch, or better yet, use a stored procedure for the operation.
Keep the transaction short. Start the transaction at the point you need to do the modifications. Do any preliminary work beforehand.
Make careful use of different locking schemes and transaction isolation levels.
If user input is unavoidable between data retrieval and modification, and you need to handle the possibility of another user modifying the data values read, use optimistic locking strategies rather than actual locks by using HOLDLOCK or other locking options. Optimistic locking makes use of the WHERE clause to update the data rather than holding onto locks. Chapters 26, "Using Transact -SQL in SQL Server 2000," and 38 both cover optimistic locking methods .
Collect multiple transactions into one transaction, or batch transactions together, if appropriate. This might seem to go against some of the other suggestions, but it reduces the amount of overhead that SQL Server will encounter to start, finish, and log the transactions.

Long-Running Transactions

As you have already seen, transaction information is recorded in each database's transaction log. However, long-running transactions can be a cause of consternation to the system administrator who is attempting to back up and prune the transaction log. Only the inactive portion of the log can be truncated during this operation. The inactive portion of the log is the pages containing log records for all completed transactions prior to the first log record of the oldest still-active transaction (see Figure 31.5). Even if completed transactions follow the first record of the oldest active transaction, they cannot be removed from the log until the oldest active transaction completes. This is because the log is pruned by clearing out entire pages of information prior to the oldest active transaction. Pages after this point cannot be cleared because they might contain records for the active transaction that would be needed in the event of a rollback or database recovery.

Figure 31.5. The inactive portion of the log is the pages in the log prior to the oldest active transaction.

graphics/31fig05.gif

In addition to preventing the log from being pruned, long-running transactions can degrade concurrency by holding locks for an extended period of time, preventing other users from accessing the locked data.

To get information about the oldest active transaction in a database, you can use the DBCC OPENTRAN command. The syntax is as follows :

DBCC OPENTRAN [('DatabaseName'  DatabaseId)] 
[WITH TABLERESULTS [, NO_INFOMSGS]]

The following example examines the oldest active transaction for the pubs database:

DBCC OPENTRAN (pubs) 
go

Transaction information for database 'pubs'.
Oldest active transaction:
    SPID (server process ID) : 51
    UID (user ID) : 1
    Name          : t1
    LSN           : (62:207:1)
    Start time    : Jun 13 2001 11:58:16:820PM
DBCC execution completed. If DBCC printed error messages, contact your
 system administrator.

DBCC OPENTRAN returns the spid of the process that initiated the transaction, the user ID, the name of the transaction (this is when naming transactions are helpful because they might help you identify the SQL code that initiated the transaction), the Log Sequence Number (LSN) of the page containing the initial BEGIN TRAN statement for the transaction, and finally, the time the transaction was started.

If you specify the TABLERESULTS option, this information is returned in two columns that you can load into a table for logging or comparison purposes. The NO_INFOMSGS option suppresses the display of the 'DBCC execution completed...' message. The following example runs DBCC OPENTRAN and inserts the results into a temp table:

CREATE TABLE #opentran_results 
( result_label VARCHAR(30), result_value VARCHAR(46))

insert #opentran_results exec ('dbcc opentran (pubs) WITH TABLERESULTS, no_infomsgs')

select * from #opentran_results
go

result_label                   result_value
------------------------------ ----------------------------------------------
OLDACT_SPID                    51
OLDACT_UID                     1
OLDACT_NAME                    t1
OLDACT_LSN                     (62:211:11)
OLDACT_STARTTIME               Jun 14 2001 12:50:28:233AM

If no open transactions exist for the database, you will receive the following message from DBCC OPENTRAN :

No active open transactions. 
DBCC execution completed. If DBCC printed error messages, contact your
 system administrator.

DBCC OPENTRAN provides a means for you to identify which transactions are potential problems based on their longevity. If you capture the process information at the same time using sp_who , you can identify who or what application is causing the longest-running transaction(s). Using this information, you can terminate the process if necessary, or you can just have a quiet word with the user if the query is ad hoc or with the application developers if it is SQL code generated by a custom application.