SQLCmd session which is using transaction isolation level
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
If I execute a
SELECT *, then I can see it placing
S locks on each row, which are not already locked with
Can anyone help me understand this situation?
I've tried with both SQL Server 2008 R2 and 2012, same behavior in both.