Chapter 16. Transactions

Transactions ensure that a set of related operations are all completed or are aborted, leaving the involved resources in the state that they were in when the transaction was started. Transactions are most commonly used when all related operations must be completed successfully (e.g., debiting and crediting respective bank accounts when funds are transferred from one to the other). The related operations are bound together into a transactional unit of work that must either completely succeed or completely fail. This is referred to as committing or aborting the transaction.

ADO.NET supports manual transactions whose bounds are defined by the application using explicit commands to begin and end the transaction. Manual transactions are easy to code and offer both performance and flexibility, but can't span multiple data resources. Instances of a .NET Framework class can also be registered with COM+ component services to enlist in and participate in automatic transactions. Automatic transactions are the only choice when a transaction spans multiple data resource.

When selecting a transaction model, DBMS transactions are another option. DBMS transactions contain all transaction logic within a stored procedure; they offer the best performance but are more difficult to code than ADO.NET manual transactions. The DBMS transaction model is also the most limited model. If more than one stored procedure needs to be called within the context of a single transaction, either the manual or automatic transaction model must be used. DBMS transactions can be used together with manual transactions, but code must be written to deal with transactions that are rolled back from within the stored procedures.

Finally, it's important to understand that transactions should be used only when required. Using transactions imposes a performance penalty due to the system overhead in managing the transaction. Transactions can also block the work of other users in the system, which causes performance problems. For that reason, if transactions are required, the isolation level of the transactions should be carefully considered.