Keep transactions as short as possible. The shorter transactions are, the shorter that locks have to be held, which results in both greater performance and scalability. Here are some ways to help keep transactions as short as possible:
It is especially important that transactions aren’t kept open while waiting for user input. User input should be collected as a whole, and then submitted at one time. In other words, don’t collect user input in the middle of a transaction.
Use stored procedures for your code.
Only return the exact data needed, no extra columns or rows should be returned.
Try to reduce the number of round trips between the client and SQL Server.
In large units of work, consider breaking it down into smaller units, so that there are many, very quick transactions rather than a single, large transaction. A large transaction hold locks for longer periods, contributing to performance problems.
From another perspective, if there are many, many transactions going on, contributing to excess round trips and network traffic, consider batching them together so that there are fewer round trips between the client and SQL Server.
Do not use a transaction to view and browse data.
Consider using a lower transaction isolation level, if appropriate.
Always manage your transaction by beginning and ending your transaction explicitly. Do not use implicit transactions.