3.30 notes on using transaction in ASP. NET

Tip: The usage principle of transaction, which is used only when the program needs it. for example, if you simply retrieve data from a database or perform a simple query, you do not need to use transaction. On the other hand, if you insert data in several associated tables, you need to use transactions. generally, we do not use transactions for a simple operation, such as update, delete, or insert.

Even though ADO. NET provides good support for transactions, you shoshould not always use transactions. in fact, every time you use any kind of transaction, you automatically incur some overhead. also, transactions involve some kind of locking of table rows. thus, unnecessarily using transactions may harm the overall scalability of your application. when implementing a transaction, you can follow these practices to achieve the best results:
• 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.
• If you do retrieve records, fetch only the rows that are required so as not lock too into resources and so as to keep performance as good as possible.
• Wherever possible, write transactions within stored procedures instead of using ADO. net transactions.
• 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.