REPEATABLE READ
Let us now run the query in the second window also as a transaction. We will set the transaction isolation level back to the default ? READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION

SELECT empid, city FROM Employee
WHERE empid = 1
Run a transaction in the first query window.

BEGIN TRANSACTION
UPDATE Employee
SET city = 'Mumbai'
WHERE empid = 1
COMMIT TRANSACTION

Come back and execute the SELECT statement in the transaction of the second query window.

SELECT empid, city FROM Employee
WHERE empid = 1

You will get the value after the modification.
What we notice is within the transaction of the second query window, the same SELECT statement is giving two different results at two different points in time. We may however want to make the read repeatable, that is, we want to be assured that within the same transaction if I read the same piece of data, the results would be the same, i.e., repeatable.

For that we need to set the transaction isolation level of the second query window to REPEATABLE READ.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT empid, city FROM Employee
WHERE empid = 1

Now run the update in the first query window. Now the first query is blocked by the second query to make sure its read is repeatable. Complete the second transaction so that the first query proceeds to completion.

COMMIT TRANSACTION

SERIALIZABLE
Start the transaction of the second query window again. Let us now see all rows instead of only one row.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT empid, empname, city FROM Employee

We now know the first query window would be blocked if it tries to do an update. However, let us try something different.

BEGIN TRANSACTION

INSERT INTO Employee
VALUES('Atul', 'Bangalore')
COMMIT TRANSACTION
This would proceed to completion. The transaction in the second query window is not complete. Let us run the SELECT statement again.

SELECT empid, empname, city FROM Employee

The same query is returning an extra row in the same transaction. This extra row is what is called a phantom row. Let us complete the second transaction.

COMMIT TRANSACTION

If we do not want phantom rows to appear within the same transaction, we would set the transaction isolation level to SERIALIZABLE.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT empid, empname, city FROM Employee
Insert a different row in the first query window.

BEGIN TRANSACTION
INSERT INTO Employee
VALUES('Avinash', 'Bangalore')
COMMIT TRANSACTION

The INSERT would now be blocked by the second query window.

Complete the second transaction.

COMMIT TRANSACTION

The first query will complete now.

Summary
We learnt five transaction isolation levels in Microsoft SQL Server 2005 and 2008 ? READ UNCOMMITTED, SNAPSHOT, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. SNAPSHOT isolation level was introduced in SQL 2005, but the other four would work on SQL Server 2000 also.

In principle, the higher the concurrency (SERIALIZABLE would be the highest), the lower the performance because other users would be blocked for a long duration. And higher the performance, lower the concurrency (READ UNCOMMITTED would be the lowest). You would need to set the right balance between the two, depending on your priorities for the specific application you are developing.

Amaresh Patnaik