Setting the Transaction Isolation Level | Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver

The transaction isolation level is the degree to which the changes made by one transaction are separated from other concurrent transactions. Before I get into the details of the various transaction isolation levels, you need to understand the types of problems that might occur when current transactions attempt to access the same rows in a table. In the following list, I'll use examples of two concurrent transactions that are accessing the same rows to illustrate the three types of potential transaction processing problems:

Phantoms Transaction 1 reads a set of rows returned by a specified WHERE clause. Transaction 2 then inserts a new row, which also happens to satisfy the WHERE clause of the query previously used byTransaction 1. Transaction 1 then reads the rows again using the same query but now sees the additional row just inserted by Transaction 2. This new row is known as a "phantom," because to Transaction 1, this row seems to have magically appeared.
Nonrepeatable reads Transaction 1 reads a row, and Transaction 2 updates the same row just read by Transaction 1. Transaction 1 then reads the same row again and discovers that the row it read earlier is now different. This is known as a "nonrepeatable read," because the row originally read by Transaction 1 has been changed.
Dirty reads Transaction 1 updates a row but doesn't commit the update. Transaction 2 reads the updated row. Transaction 1 then performs a rollback, undoing the previous update. Now the row just read by Transaction 2 is no longer valid (or it's "dirty") because the update made by Transaction 1 wasn't committed when the row was read by Transaction 2.

To deal with these potential problems, databases implement various levels of transaction isolation to prevent concurrent transactions from interfering with each other. The SQL standard defines four isolation levels, which are shown in Table 14.3. These levels are shown in order of increasing isolation.

Table 14.3: SQL Standard Isolation Levels
ISOLATION LEVEL	DESCRIPTION
READ UNCOMMITTED	Phantoms, nonrepeatable reads, and dirty reads are permitted.
READ COMMITTED	Phantoms and nonrepeatable reads are permitted, but dirty reads are not. This is the default for SQL Server.
REPEATABLE READ	Phantoms are permitted, but nonrepeatable and dirty reads are not.
SERIALIZABLE	Phantoms, nonrepeatable reads, and dirty reads are not permitted. This is the default for the SQL standard.

SQL Server supports all of these transaction isolation levels. The default transaction isolation level defined by the SQL standard is SERIALIZABLE, but the default used by SQL Server is READ COMMITTED, which is acceptable for most applications.

Warning

When you set the transaction isolation level to SERIALIZABLE, any rows you access within a subsequent transaction will be "locked," meaning that no other transaction can modify those rows. Even rows you retrieve using a SELECT statement will be locked. You must commit or roll back the transaction to release the locks and allow other transactions to access the same rows. Use SERIALIZABLE only when you must ensure that your transaction is isolated from other transactions. You'll learn more about this later in the section "Understanding SQL Server Locks."

In addition, ADO.NET supports a number of transaction isolation levels, which are defined in the System.Data.IsolationLevel enumeration. Table 14.4 shows the members of this enumeration.

Table 14.4: IsolationLevel Enumeration Members
ISOLATION LEVEL	DESCRIPTION
Chaos	Pending changes from more isolated transactions cannot be overwritten. SQL Server doesn't support this isolation level.
ReadCommitted	Phantoms and nonrepeatable reads are permitted, but dirty reads are not. This is the default.
ReadUncommitted	Phantoms, nonrepeatable reads, and dirty reads are permitted.
RepeatableRead	Phantoms are permitted, but nonrepeatable and dirty reads are not.
Serializable	Phantoms, nonrepeatable reads, and dirty reads are not permitted.
Unspecified	A different isolation level than the one specified is being used, but the level cannot be determined. SQL Server doesn't support this isolation level.

In the next two sections, you'll learn how to set the transaction isolation level using T-SQL and a SqlTransaction object.

Setting the Transaction Isolation Level Using T-SQL

As well as learning to set the transaction isolation level using T-SQL, you'll see an example that shows the effect of setting different transaction isolation levels in SQL Server using the Query Analyzer tool.

To set the transaction isolation level in T-SQL, you use the SET TRANSACTION ISOLATION LEVEL command. The syntax for this command is as follows:

 SET TRANSACTION ISOLATION LEVEL {   READ COMMITTED |   READ UNCOMMITTED |   REPEATABLE READ |   SERIALIZABLE  }

As you can see from the previous syntax, you can set the transaction isolation to any of the levels shown earlier in Table 14.3.

The following example sets the transaction isolation level to SERIALIZABLE:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Note

The transaction isolation level is set for your session. Therefore, if you perform multiple transactions in a session, all your transactions will use the same level. If you want to change the level in your session, you simply execute another SET TRANSACTION ISOLATION LEVEL command with your new level. All subsequent transactions in your session will use the new level.

The following example sets the transaction isolation level to READ COMMITTED:

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Let's look at a complete example that sets the transaction isolation level using T-SQL. Listing 14.3 shows an example T-SQL script that sets the transaction isolation level first to SERIALIZABLE and executes a transaction, and then sets the level to READ COMMITTED and executes another transaction.

Listing 14.3: TransactionIsolation.sql

 /*   TransactionIsolation.sql illustrates how to set the   transaction isolation level */ USE Northwind SET TRANSACTION ISOLATION LEVEL SERIALIZABLE   BEGIN TRANSACTION     SELECT CustomerID, CompanyName     FROM Customers     WHERE CustomerID IN ('ALFKI', 'J8COM')     INSERT INTO Customers (       CustomerID, CompanyName     ) VALUES (       'J8COM', 'J8 Company'     )     UPDATE Customers     SET CompanyName = 'Widgets Inc.'     WHERE CustomerID = 'ALFKI'     SELECT CustomerID, CompanyName     FROM Customers     WHERE CustomerID IN ('ALFKI', 'J8COM')   COMMIT TRANSACTION SET TRANSACTION ISOLATION LEVEL READ COMMITTED   BEGIN TRANSACTION     UPDATE Customers     SET CompanyName = 'Alfreds Futterkiste'     WHERE CustomerID = 'ALFKI'     DELETE FROM Customers     WHERE CustomerID = 'J8COM'     SELECT CustomerID, CompanyName     FROM Customers     WHERE CustomerID IN ('ALFKI', 'J8COM')   COMMIT TRANSACTION

Figure 14.2 shows the TransactionIsolation.sql script being run in Query Analyzer. In the results pane in the lower half of Query Analyzer, the first two sets of rows are generated by the first transaction, and the final single row is generated by the second transaction.

click to expand
Figure 14.2: Running the TransactionIsolation.sql script in Query Analyzer

Setting the Transaction Isolation Level of a SqlTransaction Object

Along with setting the transaction isolation level of a SqlTransaction object, you'll see an example that shows the effect of setting different levels in a C# program.

You create a SqlTransaction object by calling the BeginTransaction() method of the SqlConnection object. This method is overloaded as follows:

 SqlTransaction BeginTransaction() SqlTransaction BeginTransaction(IsolationLevel myIsolationLevel) SqlTransaction BeginTransaction(string transactionName) SqlTransaction BeginTransaction(IsolationLevel myIsolationLevel, string transactionName)

where

myIsolationLevel specifies the isolation level of your transaction. This is a constant from the System.Data.IsolationLevel enumeration, for which members were shown earlier in Table 14.4.
transactionName specifies a string containing the name you want to assign to your transaction.

In the examples in this section, assume you have an open SqlConnection named mySqlConnection that is connected to the SQL Server Northwind database. The following example creates a SqlTransaction named serializableTrans by calling the BeginTransaction() method of mySqlConnection; notice that the IsolationLevel of Serializable is passed to BeginTransaction():

 SqlTransaction serializableTrans =   mySqlConnection.BeginTransaction(IsolationLevel.Serializable);

The next example creates a SqlCommand named serializableCommand, and sets its Transaction property to serializableTrans:

 SqlCommand serializableCommand =   mySqlConnection.CreateCommand(); serializableCommand.Transaction = serializableTrans;

Any SQL statements performed using serializableCommand will now use serializableTrans, and will therefore be performed in a serializable transaction. The following example performs an INSERT statement that adds a row to the Customers table:

 serializableCommand.CommandText =   "INSERT INTO Customers ("+   "CustomerID, CompanyName "+   ") VALUES ("+   "'J8COM', 'J8 Company' "+   ")"; int numberOfRows = serializableCommand.ExecuteNonQuery();

The next example performs an UPDATE statement:

 serializableCommand.CommandText =   "UPDATE Customers "+   "SET CompanyName = 'Widgets Inc.' "+   "WHERE CustomerID = 'ALFKI'"; numberOfRows = serializableCommand.ExecuteNonQuery();

Finally, the following example commits the INSERT and UPDATE statements by calling the Commit() method of serializableTrans:

 serializableTrans.Commit();

Listing 14.4 shows a program that contains the following methods:

DisplayRows() Selects and displays any rows from the Customers table with a CustomerID of ALFKI or J8COM.
PerformSerializableTransaction() Performs the code shown earlier in this section to create a SqlTransaction object with an isolation level of Serializable, and uses it to perform an INSERT and UPDATE statement.
PerformReadCommittedTransaction() Creates a SqlTransaction object with an isolation level of ReadCommitted, and uses it to perform UPDATE and DELETE statements.

Listing 14.4: TransactionIsolation.cs

 /*   TransactionIsolation.cs illustrates how to set the   transaction isolation level */ using System; using System.Data; using System.Data.SqlClient; class TransactionIsolation {   public static void DisplayRows(     SqlCommand mySqlCommand   )   {     mySqlCommand.CommandText =       "SELECT CustomerID, CompanyName "+       "FROM Customers "+       "WHERE CustomerID IN ('ALFKI', 'J8COM')";     SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();     while (mySqlDataReader.Read())     {       Console.WriteLine("mySqlDataReader[\" CustomerID\"] = "+         mySqlDataReader["CustomerID"]);       Console.WriteLine("mySqlDataReader[\" CompanyName\"] = "+         mySqlDataReader["CompanyName"]);     }     mySqlDataReader.Close();   }   public static void PerformSerializableTransaction(     SqlConnection mySqlConnection   )   {     Console.WriteLine("\nIn PerformSerializableTransaction()");     // create a SqlTransaction object and start the transaction     // by calling the BeginTransaction() method of the SqlConnection     // object, passing the IsolationLevel of Serializable to the method     SqlTransaction serializableTrans =       mySqlConnection.BeginTransaction(IsolationLevel.Serializable);     // create a SqlCommand and set its Transaction property     // to serializableTrans     SqlCommand serializableCommand =       mySqlConnection.CreateCommand();     serializableCommand.Transaction = serializableTrans;     // call the DisplayRows() method to display rows from     // the Customers table     DisplayRows(serializableCommand);     // insert a new row into the Customers table     Console.WriteLine("Inserting new row into Customers table "+       "with CustomerID of J8COM");     serializableCommand.CommandText =       "INSERT INTO Customers ("+       "CustomerID, CompanyName "+       ") VALUES ("+       "'J8COM', 'J8 Company' "+       ")";     int numberOfRows = serializableCommand.ExecuteNonQuery();     Console.WriteLine("Number of rows inserted = "+ numberOfRows);     // update a row in the Customers table     Console.WriteLine("Setting CompanyName to 'Widgets Inc.' for "+       "row with CustomerID of ALFKI");     serializableCommand.CommandText =       "UPDATE Customers "+       "SET CompanyName = 'Widgets Inc.' "+       "WHERE CustomerID = 'ALFKI'";     numberOfRows = serializableCommand.ExecuteNonQuery();     Console.WriteLine("Number of rows updated = "+ numberOfRows);     DisplayRows(serializableCommand);     // commit the transaction     serializableTrans.Commit();   }   public static void PerformReadCommittedTransaction(     SqlConnection mySqlConnection   )   {     Console.WriteLine("\nIn PerformReadCommittedTransaction()");     // create a SqlTransaction object and start the transaction     // by calling the BeginTransaction() method of the SqlConnection     // object, passing the IsolationLevel of ReadCommitted to the method     // (ReadCommitted is actually the default)     SqlTransaction readCommittedTrans =       mySqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);     // create a SqlCommand and set its Transaction property     // to readCommittedTrans     SqlCommand readCommittedCommand =       mySqlConnection.CreateCommand();     readCommittedCommand.Transaction = readCommittedTrans;     // update a row in the Customers table     Console.WriteLine("Setting CompanyName to 'Alfreds Futterkiste' "+       "for row with CustomerID of ALFKI");     readCommittedCommand.CommandText =       "UPDATE Customers "+       "SET CompanyName = 'Alfreds Futterkiste' "+       "WHERE CustomerID = 'ALFKI'";     int numberOfRows = readCommittedCommand.ExecuteNonQuery();     Console.WriteLine("Number of rows updated = "+ numberOfRows);     // delete the new row from the Customers table     Console.WriteLine("Deleting row with CustomerID of J8COM");     readCommittedCommand.CommandText =       "DELETE FROM Customers "+       "WHERE CustomerID = 'J8COM'";     numberOfRows = readCommittedCommand.ExecuteNonQuery();     Console.WriteLine("Number of rows deleted = "+ numberOfRows);     DisplayRows(readCommittedCommand);     // commit the transaction     readCommittedTrans.Commit();   }   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     mySqlConnection.Open();     PerformSerializableTransaction(mySqlConnection);     PerformReadCommittedTransaction(mySqlConnection);     mySqlConnection.Close();   } }

The output from this program is as follows:

 In PerformSerializableTransaction() mySqlDataReader["CustomerID"] = ALFKI mySqlDataReader["CompanyName"] = Alfreds Futterkiste Inserting new row into Customers table with CustomerID of J8COM Number of rows inserted = 1 Setting CompanyName to 'Widgets Inc.' for row with CustomerID of ALFKI Number of rows updated = 1 mySqlDataReader["CustomerID"] = ALFKI mySqlDataReader["CompanyName"] = Widgets Inc. mySqlDataReader["CustomerID"] = J8COM mySqlDataReader["CompanyName"] = J8 Company In PerformReadCommittedTransaction() Setting CompanyName to 'Alfreds Futterkiste' for row with CustomerID of ALFKI Number of rows updated = 1 Deleting row with CustomerID of J8COM Number of rows deleted = 1 mySqlDataReader["CustomerID"] = ALFKI mySqlDataReader["CompanyName"] = Alfreds Futterkiste