Professional SQL Server™ 2005 Programming

12.4. Setting the Isolation Level

We've seen that several different kinds of problems that can be prevented by different locking strategies. We've also seen what kinds of locks are available and how they have an impact on the availability of resources. Now it's time to take a closer look at how these process management pieces work together to ensure overall data integrity — to make certain that you can get the results you expect.

The first thing to understand about the relationship between transactions and locks is that they are inextricably linked with each other. By default, any lock that is data modification-related will, once created, be held for the duration of the transaction. If you have a long transaction, this means that your locks may be preventing other processes from accessing the objects you have a lock on for a rather long time. It probably goes without saying that this can be rather problematic.

However, that's only the default. In fact, there are actually four different isolation levels that you can set:

• READ COMMITTED (the default)

• READ UNCOMMITTED

• REPEATABLE READ

• SERIALIZABLE

The syntax for switching between them is pretty straightforward:

SET TRANSACTION ISOLATION LEVEL <READ COMMITTED|READ UNCOMMITTED |REPEATABLE READ|SERIALIZABLE>

The change in isolation level will affect only the current connection — so you don't need to worry about adversely affecting other users (or them affecting you).

Let's start by looking at the default situation (READ COMMITTED) a little ...