[ Team LiB ]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;

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;
}

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

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.
[ Team LiB ]