Microsoft® SQL Server 2008 R2 Unleashed

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 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 ...