Setting the Transaction Isolation Level
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.
Table 14.3: SQL Standard Isolation Levels
ISOLATION
LEVEL
DESCRIPTION
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
N
ote 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.
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