Some practices for working with transactions

• Keep transactions as short as possible.


• Avoid returning data with a SELECT query in the middle of a transaction. Ideally, you should return the data before the transaction starts. This reduces the amount of data your transaction will lock.


• If you do retrieve records, fetch only the rows that are required so as to reduce the number of locks.


• Wherever possible, write transactions within stored procedures instead of using ADO.NET transactions. This way, your transaction can be started and completed more quickly, because the database server doesn’t need to communicate with the client (the web application).


• Avoid transactions that combine multiple independent batches of work. Put separate batches into separate transactions.


• Avoid updates that affect a large range of records if at all possible.