Using Manual Transactions
With manual transactions, you write code that uses the transaction support features of either ADO.NET or Transact-SQL directly in your component code or stored procedures, respectively. In most cases, you should opt for controlling transactions in your stored procedures because this approach offers superior encapsulation, and from a performance perspective is comparable to performing transactions with ADO.NET code.
Performing Manual Transactions with ADO.NET
ADO.NET supports a transaction object that you can use to begin a new transaction and then explicitly control whether it should be committed or rolled back. The transaction object is associated with a single database connection and is obtained by the BeginTransaction method of the connection object. Calling this method does not implicitly mean that subsequent commands are issued in the context of this transaction. You must explicitly associate each command with the transaction, by setting the Transaction property of the command. You can associate multiple command objects with the transaction object, thereby grouping multiple operations against the single database in a single transaction.
- The default isolation level for an ADO.NET manual transaction is Read Committed, which means that the database holds shared locks while data is being read, but data can be changed before the end of the transaction. This can potentially result in non-repeatable reads, or phantom data. You can change the isolation level by setting the transaction object's IsolationLevel property to one of the enumerated values defined by the IsolationLevel enumerated type.
- You must give careful consideration to choosing an appropriate isolation level for your transactions. The tradeoff is one of data consistency versus performance. The highest isolation level (Serialized) offers absolute data consistency, but at the price of overall system throughput. Lower isolation levels can make an application more scalable, but at the same time, increase the possibility of errors resulting from data inconsistency. For systems that read data most of the time, and write data rarely, lower isolation levels might be appropriate.
Performing Manual Transactions with Stored Procedures
You can also control manual transactions directly by using Transact-SQL statements in your stored procedures. For example, you could perform transactional operations by using a single stored procedure that employs Transact-SQL transaction statements such as BEGIN TRANSACTION, END TRANSACTION, and ROLLBACK TRANSACTION.
- If required, you can control the transaction isolation level by using the SET TRANSACTION ISOLATION LEVEL statement in the stored procedure. Read Committed is the SQL Server default
- For a code sample that shows how to perform transactional updates using Transact-SQL transaction statement.