16.1 Manual Transactions

16.1 Manual Transactions

Manual transactions use explicit statements to control the boundaries of a transaction. Transactions are started, and subsequently either committed or rolled back. The SQL .NET data provider allows savepoints to be defined that allow a transaction to be partially rolled back. The OLE DB .NET data provider allows new, or nested, transactions to be started within the boundaries of the parent transaction. If transactions are nested, the parent can't commit until all nested transactions have committed.

A Transaction is started by calling the BeginTransaction( ) method of a Connection object. You can set a Command object to run in a transaction by setting its Transaction property to a Transaction object connected to the same Connection as the Command object. An overloaded constructor for the Command object allows this to be done in a single statement.

Once running in a Transaction , commands can be executed on the Command object within a try/catch block. If an exception is raised, the Rollback( ) method can be called on the Transaction to roll back all changes; otherwise , the Commit( ) method persists the changes.

The following example demonstrates these concepts. Order and order detail records are inserted within a transaction, thereby ensuring that either both or neither record is added:

 String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";

SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();

//create command and enlist in transaction
SqlCommand cmdOrder = new SqlCommand("InsertOrder", conn, tran);
cmdOrder.CommandType = CommandType.StoredProcedure;
SqlCommand cmdOrderDetail = new SqlCommand("InsertOrderDetail",
    conn, tran);
cmdOrderDetail.CommandType = CommandType.StoredProcedure;

SqlParameterCollection orderParams = cmdOrder.Parameters;
orderParams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
orderParams["@OrderID"].Direction = ParameterDirection.InputOutput;
orderParams.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
// ... code to define remaining parameters

SqlParameterCollection orderDetailParams = cmdOrderDetail.Parameters;
orderDetailParams.Add("@OrderID", SqlDbType.Int, 0, "OrderID");
orderDetailParams.Add("@ProductID", SqlDbType.Int, 5, "ProductID");
// ... code to define remaining parameters

String result = "";
try
{
    // insert order
    cmdOrder.Parameters["@OrderID"].Value = -1;
    cmdOrder.Parameters["@CustomerID"].Value = "ALFKI";
    // ... set the other parameters
    cmdOrder.ExecuteNonQuery();

    // insert order detail with OrderID from the inserted order
    cmdOrderDetail.Parameters["@CustomerID"].Value =
        (Int32)cmdOrder.Parameters["@OrderID"].Value;
    cmdOrderDetail.Parameters["@ProductID"].Value = 20;
    //... set the other parameters
    cmdOrderDetail.ExecuteNonQuery();

    //if okay to here, commit the transaction
    tran.Commit();
    result = "Transaction commit.";
}
catch (SqlException ex)
{
    tran.Rollback();
    result = "ERROR: " + ex.Message + "; Transaction rollback.";
}
catch (FormatException ex)
{
    tran.Rollback();
    result = "ERROR: " + ex.Message + "; Transaction rollback.";
}
finally
{
    conn.Close();
} 

16.2 Isolation Levels

The transaction isolation level specifies the transaction locking level for a connection. It determines the extent to which changes to data within a transaction are visible outside that transaction while uncommitted.

Table 16-1 lists and describes problems that might occur if several users access data concurrently without locking.

Table 16-1. Concurrency problems

Condition

Description

Lost update

Two or more transactions select the same row and subsequently update the row. The transactions are unaware of each other and, as a result, updates overwrite one another, resulting in lost data.

Uncommitted dependency

(dirty read)

A second transaction selects a row that has been updated, but not committed, by another transaction. The data being read might be further updated or rolled back by the original transaction, resulting in invalid data in the second transaction.

Inconsistent analysis

(nonrepeatable read)

A second transaction reads different data each time the same row is read. The second transaction reads data that has been changed and committed by another transaction between the reads.

Phantom read

An insert or delete is performed for a row belonging to a range of rows being read by a transaction. The rows selected within the transaction are missing the newly inserted rows and contain deleted rows that no longer exist.

Locks ensure transactional integrity and maintain database consistency by controlling how resources can be accessed by concurrent transactions. A lock is an object that indicates a user has some dependency on a resource. Other users are prevented from performing operations that would adversely affect the dependency of the user with the lock. Locks are managed internally by system software and acquired and released as a result of actions taken by users. Table 16-2 lists and describes resource lock modes used by ADO.NET.

Table 16-2. Resource lock modes

Lock mode

Description

Shared

Allows concurrent transactions to read the locked resource. Another transaction can't modify the locked data while the lock is held.

Exclusive

Prevents access, both read and modify, to a resource by concurrent transactions.

Isolation level is the level at which a transaction is prepared to accept inconsistent data; it is the degree to which one transaction must be isolated from other transactions. As the isolation level increases, access to current data increases at the expense of data correctness. Table 16-3 lists and describes the different isolations supported by ADO.NET. The first four levels are listed in order of increasing isolation.

Table 16-3. IsolationLevelEnumeration

Name

Description

 ReadUncommitted 

No shared locks are issued, and exclusive locks aren't honored. A dirty read is possible.

 ReadCommitted 

Shared locks are held while data is read by the transaction. Dirty reads aren't possible, but nonrepeatable reads or phantom rows can occur because data can be changed before it is committed.

 RepeatableRead 

Shared locks are placed on all data used in a query preventing other users from updating the data. Nonrepeatable reads are prevented, but phantom reads are still possible.

 Serializable 

A range lock, where the individual records and the ranges between records are covered, is placed on the data preventing other users from updating or inserting rows until the transaction is complete. Phantom reads are prevented.

 Chaos 

Pending changes from more highly isolated transactions can't be overwritten. Not supported by SQL Server.

Unspecified

A different isolation level than the one specified is being used, but that level can't be determined.

The isolation level can be changed programmatically at any time. If it is changed within a transaction, the new locking level applies to all remaining statements within the transaction.

The following example demonstrates how to set the isolation level for a transaction:

 String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";

SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlTransaction tran =
    conn.BeginTransaction(IsolationLevel.RepeatableRead);

// returns IsolationLevel.RepeatableRead
IsolationLevel il = tran.IsolationLevel;