SQL Server table isolation level and lock issue

Question

Hello everyone,

For the same ADO.Net statement, I want to make sure my understanding of isolation level and lock is correct.

1. In default SQL Server isolation level (read committed), after read each row, the row will unlocked;

2. If I raise isolation level to repeatable read, the lock (on the whole table? or some other level lock?) will be hold until the end of while loop?

e.g.:

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText= "select operation_id, operation_code, product_id, quantity
from dbo.operations where processed=0";

reader=cmd.ExecuteReader();

while (reader.Read())    
{
   // some operations
}

thanks in advance, George

Answer 1

1) Your first point is not correct: The default isolation level of Read Committed means that Dirty Reads will not occur (although phantom or non-repeatable reads might). It does not guarantee that single rows are locked.

A non-repeatable read can occur in the following situation:

1. Transaction 1 begins
2. Transaction 1 read a row
3. Transaction 2 begins
4. Transaction 2 changes the value of the same row read by Transaction 1
5. Transaction 2 commits
6. Transaction 1 reads the row again. Transaction 1 has inconsistent data.

2) Repeatable Read isolation level means the above situation cannot occur (though phantom reads still might). A phantom read can occur in the following situation:

1. Transaction 1 begins
2. Transaction 1 read a row
3. Transaction 2 begins
4. Transaction 2 deletes the row read by Transaction 1
5. Transaction 2 commits. Transaction 1 can no longer repeat its initial read, 
   since the row no longer exists.

If you want to guarantee that data does not chnage while you read it, you would need the Serializable isolation level. I would strongly advise agianst not using the Serializable isolation level unless you absolutely have to, as concurrency will suffer.

Answer 2

A few useful articles about differences in isolation levels: Selects under READ COMMITTED and REPEATABLE READ may return incorrect results.

When Snapshot Isolation Helps and When It Hurts

Answer 3

In repeatable read or in serializable isolation levels the row locks acquired by the SELECT will be held until the transaction commits, not until the end of the loop. If you do not specify an explicit transaction then the SELECT statement will start an implicit one that will auto-commit when the SELECT statement completes. That is not the same moment as when the while loop ends, the loop is on the client and the SELECT statement may complete on the server before your loop ends.

As Mitch said, the higher level isolation levels have a certain purpose, to avoid phantom reads or non-repeatable reads. A single SELECT statement, in an auto-committed implicit transaction, cannot require a higher isolation level. These levels only come into play on multiple statement transactions, when data is read multiple times. Perhaps is better if you explain the context of the operation you're doing and why are you concerned about the locks this SELECT will place?