A transaction is a single unit of work. If a transaction is
successful, all of the data modifications made during the transaction are
committed and become a permanent part of the database. If a transaction
encounters errors and must be cancelled or rolled back, then all of the data
modifications are erased. As a thumb rule, use a transaction only when the application
requires one. For example, if we are simply selecting records from a database
or firing a single query, we will not need a transaction. In general, a
transaction never requires for single statement commands such as Insert, Update
or Delete. On the other hand, for executing batch/multiple statements we need
transaction to preserve the database integrity and data consistency. However,
it should be noted that transactions hold locks and may harm the overall
scalability of the application; they should be as short as it is possible. It
should be avoided to return data using a select query in the middle of
transaction. Ideally, it should be returned the data before the transaction
starts. If we do receive records, fetch only the rows that are required. That
means not to lock too many resources to keep performance as good as possible.
Whenever possible, write transactions within stored procedures instead of using
an ADO.NET transaction.