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.