Coding Efficient Transactions
It is important to keep transactions as short as possible. When a transaction is started, a database management system (DBMS) must hold many resources until the end of the transaction to protect the atomicity, consistency, isolation, and durability (ACID) properties of the transaction. If data is modified, the modified rows must be protected with exclusive locks that prevent any other transaction from reading the rows, and exclusive locks must be held until the transaction is committed or rolled back. Depending on transaction isolation level settings, SELECT statements may acquire locks that must be held until the transaction is committed or rolled back. Especially in systems with many users, transactions must be kept as short as possible to reduce locking contention for resources between concurrent connections. Long-running, inefficient transactions may not be a problem with small numbers of users, but they are intolerable in a system with thousands of users.
Coding Guidelines
These are guidelines for coding efficient transactions:
Do not require input from users during a transaction.
Get all required input from users before a transaction is started. If additional user input is required during a transaction, roll back the current transaction and restart the transaction after the user input is supplied. Even if users respond immediately, human reaction times are vastly slower than computer speeds. All resources held by the transaction are held for an extremely long time, which has the potential to cause blocking problems. If users do not respond, the transaction remains active, locking critical resources until they respond, which may not happen for several minutes or even hours.
Do not open a transaction while browsing through data, if at all possible.
Transactions should not be started until all preliminary data analysis has been completed.
Keep the transaction as short as possible.
After you know the modifications that have to be made, start a transaction, execute the modification statements, and then immediately commit or roll back. Do not open the transaction before it is required.
To reduce blocking, consider using a row versioning-based isolation level for read-only queries. For more information, see Using Row Versioning-based Isolation Levels.
Make intelligent use of lower transaction isolation levels.
Many applications can be readily coded to use a read-committed transaction isolation level. Not all transactions require the serializable transaction isolation level.
Make intelligent use of lower cursor concurrency options, such as optimistic concurrency options.
In a system with a low probability of concurrent updates, the overhead of dealing with an occasional "somebody else changed your data after you read it" error can be much lower than the overhead of always locking rows as they are read.
Access the least amount of data possible while in a transaction.
This lessens the number of locked rows, thereby reducing contention between transactions.
Avoiding Concurrency and Resource Problems
To prevent concurrency and resource problems, manage implicit transactions carefully. When using implicit transactions, the next Transact-SQL statement after COMMIT or ROLLBACK automatically starts a new transaction. This can cause a new transaction to be opened while the application browses through data, or even when it requires input from the user. After completing the last transaction required to protect data modifications, turn off implicit transactions until a transaction is once again required to protect data modifications. This process lets the SQL Server Database Engine use autocommit mode while the application is browsing data and getting input from the user.
In addition, when the snapshot isolation level is enabled, although a new transaction will not hold locks, a long-running transaction will prevent the old versions from being removed from tempdb.