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.