Recipe 6.12 Using Transaction Isolation Levels to Protect Data

Problem

You want to effectively use transaction isolation levels to ensure data consistency for a range of data rows.

Solution

Set and use isolation levels as shown in the following example.

The sample code contains three event handlers:

Start Tran Button.Click

Opens a Connection and starts a transaction with the specified isolation level: Chaos, ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, or Unspecified. Within the transaction, a DataTable is filled with the Orders table from the Northwind database. The default view of the table is bound to the data grid on the form.

Cancel Button.Click

Rolls back the transaction, closes the connection, and clears the data grid.

Form.Closing

Rolls back the transaction and closes the connection.

The C# code is shown in Example 6-30.

Example 6-30. File: TransactionIsolationLevelsForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private SqlConnection conn;
private SqlTransaction tran;

//  . . . 

private void startButton_Click(object sender, System.EventArgs e)
{
    startButton.Enabled = false;

    // Get the user-defined isolation level.
    IsolationLevel il = IsolationLevel.Unspecified;
    if(chaosRadioButton.Checked)
        il = IsolationLevel.Chaos;
    else if(readCommittedRadioButton.Checked)
        il = IsolationLevel.ReadCommitted;
    else if(readUncommittedRadioButton.Checked)
        il = IsolationLevel.ReadUncommitted;
    else if(repeatableReadRadioButton.Checked)
        il = IsolationLevel.RepeatableRead;
    else if(serializableRadioButton.Checked)
        il = IsolationLevel.Serializable;
    else if(unspecifiedRadioButton.Checked)
        il = IsolationLevel.Unspecified;
    
    // Open a connection.
    conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    conn.Open( );
    try
    {
        // Start a transaction.
        tran = conn.BeginTransaction(il);
    }
    catch(Exception ex)
    {
        // Could not start the transaction. Close the connection.
        conn.Close( );

        MessageBox.Show(ex.Message,"Transaction Isolation Levels",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
        startButton.Enabled = true;
        return;
    }

    String sqlText = "SELECT * FROM Orders";
    // Create a command using the transaction.
    SqlCommand cmd = new SqlCommand(sqlText, conn, tran);
    // Create a DataAdapter to retrieve all Orders.
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    // Define a CommandBuilder for the DataAdapter.
    SqlCommandBuilder cb = new SqlCommandBuilder(da);
    // Fill table with Orders.
    DataTable dt = new DataTable( );
    da.Fill(dt);

    // Bind the default view of the table to the grid.
    dataGrid.DataSource = dt.DefaultView;
                
    cancelButton.Enabled = true;
    dataGrid.ReadOnly = false;
}

private void cancelButton_Click(object sender, System.EventArgs e)
{
    cancelButton.Enabled = false;
    dataGrid.ReadOnly = true;

    // Roll back the transaction and close the connection.
    tran.Rollback( );
    conn.Close( );

    // Unbind the grid.
    dataGrid.DataSource = null;

    startButton.Enabled = true;
}

private void UsingLockingHintsForPessimisticLockingForm_Closing(
    object sender, System.ComponentModel.CancelEventArgs e)
{
    // Roll back the transaction and close the connection.
    tran.Rollback( );
    conn.Close( );
}

Discussion

The isolation level specifies the transaction locking behavior for a connection. It determines what changes made to data within a transaction are visible outside of the transaction while the transaction is uncommitted.

Concurrency violations occur when multiple users or processes attempt to modify the same data in a database at the same time without locking. Table 6-16 describes concurrency problems.

Table 6-16. Concurrency problems

Condition	Description
Lost Update	Two or more transactions select the same row and subsequently update that row. Data is lost because the transactions are unaware of each other and overwrite each other's updates.
Uncommitted Dependency (Dirty Read)	A second transaction selects a row that has been updated, but not committed, by another transaction. The first transaction makes more changes to the data or rolls back the changes already made resulting in the second transaction having invalid data.
Inconsistent Analysis (Nonrepeatable Read)	A second transaction reads different data each time that the same row is read. Another transaction has changed and committed the data 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 by the transaction are missing the inserted rows and still contain the 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 indicating that a user has a dependency on a resource. It prevents other users from performing operations that would adversely affect the locked resources. Locks are acquired and released by user actions; they are managed internally by database software. Table 6-17 lists and describes resource lock modes used by ADO.NET.

Table 6-17. Resource lock modes

Lock mode	Description
Shared	Concurrent transactions can read the locked resource. Concurrent transactions cannot modify the locked resource while the lock is held.
Exclusive	Prevents both read and modify access to a resource by concurrent transactions.

Isolation level defines the degree to which one transaction must be isolated from other transactions. A higher isolation level increases data correctness but decreases concurrent access to data. Table 6-18 describes the different isolations levels supported by ADO.NET. The first four levels are listed in order of increasing isolation.

Table 6-18. IsolationLevel enumeration

Name	Description
ReadUncommitted	No shared locks are issued. Exclusive locks are not honored.A dirty read is possible.
ReadCommitted	Shared locks are held while data is being read by the transaction.Dirty reads are not possible. Nonrepeatable reads or phantom rows can still occur because data can be changed prior to being committed.
RepeatableRead	Shared locks are placed on all data used by the query. Other users are prevented from updating the data.Nonrepeatable reads are prevented, but phantom reads are still possible.
Serializable	A range lock—covering individual records and the ranges between them—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 cannot be overwritten.This isolation level is not supported by SQL Server.
Unspecified	A different isolation level than the one specified is being used, but that level cannot be determined.

In ADO.NET, the isolation level can be set by creating the transaction using an overload of the BeginTransaction( ) method of the Command or by setting the IsolationLevel property of an existing Transaction object. The default isolation level is ReadCommitted.

Parallel transactions are not supported, so the isolation level applies to the entire transaction. It can be changed programmatically at any time. If the isolation level is changed within a transaction, the new level applies to all statements remaining in the transaction.