Does SQL Server place shared locks on scanned records when using REPEATABLE READ

Question

Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ.

In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. This statement should evaluate WHERE clause for each record in the table, but only one will match.

If I examine the locks placed under this transaction after running the UPDATE statement I can only see two IX locks on Table and Page, and a X lock on the row which was updated.

My question is: shouldn't the database engine place shared locks on all the rows it read to ensure REPEATABLE READ? What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

If I execute a SELECT *, then I can see it placing S locks on each row, which are not already locked with X.

Can anyone help me understand this situation?

I've tried with both SQL Server 2008 R2 and 2012, same behavior in both.

Answer 1

What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

That would be SERIALIZABLE READ, not REPEATABLE. REPEATABLE only guarantees that the rows that qualified for the WHERE clause the first time will not disappear or be modified. It doesn't say anything about other rows qualifying in a second read.

Answer 2

OK, two things:

• an UPDATE statement will always place an X exclusive lock on the row(s) it updates. This is true for any isolation level - and since those rows are already exclusively locked - what point would there be to add a S shared lock in addition?

• under REPEATABLE READ isolation level, any rows that you SELECT within the transaction will have a S shared lock, and that lock will be held on to until the end of the transaction - which also means: any rows that you selected in your transaction won't be able to be exclusively locked by another transaction (for an update or delete operation)

So I would say: what you're seeing is absolutely the correct and expected behavior.

See more resources on this:

• MSDN documentation on isolation levels
• Locks and Duration of Transactions in SQL Server