Isolation Levels

In previous sections of this chapter, you learned about the concurrency problems experienced in a multiuser environment.

It is important to decide how changes from other connections can affect your results, or when to allow other connections to apply changes to the data you use in your connection.

You can select the isolation level of your transactions in SQL Server 2000 either per transaction or per table. You can use the SET ISOLATION LEVEL statement to specify the isolation level at the transaction level. To select the isolation level per table, you can use locking hints.

SQL Server 2000 supports four isolation levels:

• READ COMMITTED

• READ UNCOMMITTED

• REPEATABLE READ

• SERIALIZABLE

Isolation levels are defined to solve specific concurrency problems as shown in Figure 13.5.

READ COMMITTED

SQL Server 2000 uses by default the READ COMMITTED isolation level. Using this setting, transactions cannot see changes from other connections while they are not committed or rolled back. SQL Server implements this isolation level, locking the modified data exclusively; therefore, other transactions cannot read the modified data.

Using this isolation level you can prevent dirty reads, because you cannot see data modifications from other connections that have not been committed; however, other connections can change some of the data you have read already during your transaction, producing nonrepeatable reads, or add new rows to the data set you are reading, producing phantoms.

Setting the READ COMMITTED locking hint for a table, in the FROM clause of a query, overrides the transaction isolation level. Listing 13.14 shows how to use the READ COMMITTED isolation level and READCOMMITTED locking hint.

Code Listing 13.14. Use READ COMMITTED to Prevent Dirty Reads

						
USE Northwind
GO

-- Use the READCOMMITTED optimizer hint for a table

SELECT *
FROM Products (READCOMMITTED)
GO

-- Use the READ COMMITTED ISOLATION LEVEL for a transaction

SET ISOLATION LEVEL READ COMMITTED
GO

SELECT *
FROM Products

READ UNCOMMITTED

In some cases, it might be interesting to read data without being affected by any potential lock held by other connections. A typical example is to search for some general information about the data contained in a table—such as the total number of rows—while the table is being modified by other connections. In these cases, you don't care about dirty reads, and reading provisional data is acceptable.

Using the READ UNCOMMITTED isolation level, SQL Server does not check for exclusive locks affecting the data, and does not lock the data being read with shared locks. You can use this isolation level by using the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement.

You can use the READUNCOMMITTED locking hint to specify the READ UNCOMMITTED isolation level for a specific table in a query, regardless of the current transaction isolation level. Listing 13.15 shows an example of this locking hint.

Code Listing 13.15. Using the READ UNCOMMITTED Isolation Level

						
USE Northwind
GO

-- Use the READUNCOMMITTED or the NOLOCK locking hint
-- to avoid lockings on specific tables

SELECT COUNT(*)
FROM [Order Details] (READUNCOMMITTED) –- or (NOLOCK)
GO

-- Use the READ UNCOMMITTED Isolation level
-- to specify this setting for the current connection

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

SELECT COUNT(*)
FROM [Order Details]

SELECT MAX(OrderID)
FROM Orders

REPEATABLE READ

Use the REPEATABLE READ isolation level to guarantee that the retrieved data will not change during the transaction. To achieve this isolation level, SQL Server must lock the data retrieved, preventing updates from other connections. This is important on long-running processes where the results must be consistent all during the process, such as during long-running reports.

This isolation level provides consistent analysis, but it does not prevent the appearance of phantom reads.

You can override the default transaction isolation level for one specific table in a query using the REPEATABLEREAD locking hint in the FROM clause of the query, as in Listing 13.16.

Code Listing 13.16. Use the REPEATABLE READ Isolation Level to Prevent Inconsistent Analysis

						
USE Northwind
GO

-- Use the REPEATABLEREAD to prevent inconsistent analysis
-- when reading from a table
-- that is being modified by other connections

SELECT COUNT(*)
FROM [Order Details] (REPEATABLEREAD)
GO

-- Set REPEATABLE READ isolation level at connection setting
-- to affect every table used in the connection

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

SELECT COUNT(*) AS NRows
FROM [Order Details]

SELECT SUM(Quantity * UnitPrice * (1-Discount)) AS Total
FROM [Order Details]

SERIALIZABLE

Use the SERIALIZABLE isolation level to prevent all the concurrency problems, including the appearance of phantom reads.

SQL Server must lock enough resources to prevent the possibility that other connections could insert or modify data covered by the range specified in your query. The way SQL Server enforces this isolation level depends on the existence of a suitable index, adequate to the WHERE clause of the query or queries to protect:

• If the table does not have a suitable index, SQL Server must lock the entire table.

• If there is an appropriate index, SQL Server locks the keys in the index corresponding to the range of rows in your query, plus the next key to the range.

You can use the SERIALIZABLE locking hint to apply this isolation level to a specific table in a query, overriding the actual transaction isolation level.

This isolation level is important to prevent insertion that could invalidate your analysis. Listing 13.17 shows an example of this isolation level.

Code Listing 13.17. Use the SERIALIZABLE Locking Hint to Prevent an Inconsistent Analysis Because of Phantom Reads

						
USE Northwind
GO

-- Use the SERIALIZABLE or HOLDLOCK locking hints to prevent
-- inconsistent analysis due to Phantom Reads

DECLARE @ID int

BEGIN TRANSACTION

SELECT @ID = MAX(RegionID)
FROM Region (SERIALIZABLE)

INSERT Region
(RegionID, RegionDescription)
SELECT @ID + 1, 'Europe Union'

COMMIT TRANSACTION
GO

-- Use the SERIALIZABLE Isolation level to prevent
-- inconsistent analysis due to Phantom Reads in a long running process

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRANSACTION

-- Your queries

COMMIT TRANSACTION