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.

| |