Transaction Management

I l @ ve RuBoard

Most applications of any significance require you to provide transaction management. In this context, a transaction is any number of operations against a database that must all be performed together or not at all. This type of behavior is termed atomic , in that a transaction can group a set of database updates into one, atomic update. Just to be clear, let's take the example of a banking application. In this application, money can be transferred from A's account to B's account. In the normal course of operations, the money should be taken from A's account and then placed in B's account. However, if the money were taken from A's account and then the server were to crash, B would never receive the funds from A's account. Clearly, this is not the intended result. To get the required behavior, both the operations must be treated as if they were a single operation. Such an operation is called a transaction . By bundling the operations into one transaction, the transaction will not have succeeded until both of the basic operations are complete. If the server crashes before the second operation, the transaction will time out and the first update will not be committed to the database. Luckily, managing transactions for a situation such as this is simple using ADO.NET.

Beginning a database transaction with ADO.NET is a two-stage process. The first stage is to invoke the BeginTransaction method of the connection object. This method returns an instance of a class that implements the IDbTransaction interface, which you then assign to the Transaction property of a Command object:

 //Createtransactionobject SqlTransactiontrans=conn.BeginTransaction(); //AssignTransactiontoaCommand command.set_Transaction(trans); 

Any operations you now execute using the command object will be associated with the SqlTransaction . Once you complete the operations, you can call the SqlTransaction object's Commit or Rollback method to commit or roll back the transaction.

The connection's BeginTransaction method is overloaded, and as such it allows you to optionally pass a member of the IsolationLevel enumeration, which specifies an isolation level for the transaction. You can check the isolation level of a transaction object by calling its get_IsolationLevel accessor method. The isolation levels supported by ADO.NET are slightly different than the five that JDBC supports. Table 7-10 describes the isolation levels that ADO.NET supports; note that higher isolation levels yield poorer performance than the lower levels.

Table 7-12. System.Data.IsolationLevel Enumeration Members

Member	Description
Chaos	Any changes that are pending from transactions with higher isolation levels can not be overwritten.
ReadUncommitted	Dirty reads are allowed ”other transactions can read rows that contain uncommitted changes.
ReadCommitted	Dirty reads are not allowed ”other transactions are prevented from reading rows that contain uncommitted changes. This is the default value for a transaction.
RepeatableRead	Protects against dirty reads and prevents nonrepeatable reads.
Serializable	The same as a RepeatableRead except that a lock is placed on the affected DataSet so updates and inserts are prevented.
Unspecified	Represents any isolation level different than those listed above.

I l @ ve RuBoard