It is important to keep transactions as short as possible. When a transaction is started, a DBMS must hold many resources to the end of the transaction to protect the 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.
These are guidelines for coding efficient transactions:
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 for causing blocking problems. If users do not respond, the transaction remains active and locking critical resources until they respond, which may not happen for several minutes, or even hours.
Transactions should not be started until all preliminary data analysis has been completed.
After you know the modifications that have to be made, start a transaction, execute the modification statements, then immediately commit or roll back. Do not open the transaction before it is required.
Many applications can be readily coded to use a read-committed transaction isolation level. Not all transactions require the serializable transaction isolation level.
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.
This lessens the number of locked rows, thereby reducing contention between transactions.
To prevent concurrency 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 Microsoft® SQL Server™ use autocommit mode while the application is browsing data and getting input from the user.