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.

  • How many rows in the table? And what if you use the ROWLOCK hint?
    – gbn
    Apr 22 '13 at 15:22
  • 2
    @JNK I think this question should be deleted and this question should be migrated properly. Or vice-versa. Apr 22 '13 at 15:25
  • 1
    I second @AaronBertrand 's suggestion. Who can migrate intra-site posts? Apr 22 '13 at 16:14
  • 1
    The close votes of the other question were 4-1 in favour of migration; unfortunately the 1 vote as simply "off-topic" prevented migration. I've flagged for moderator attention with the details.
    – Jon Seigel
    Apr 22 '13 at 16:40
  • 1
    If what you're asking is if SQL Server places shared locks (S) on the data to be updated during an UPDATE, then the answer to that is no. SQL Server utilizes update locks (U) in order to read the data, and those are converted to exclusive locks (X) to modify the data. If a shared lock (S) was used, and wasn't relinquished for the duration of the transaction, the data modification part of the UPDATE would never happen. Apr 22 '13 at 17:36

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.


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:

  • Thanks, but you misunderstood me. Placing X on updated is fine, but what about the rows scanned by the UPDATE statement, but did not match where clause, shouldn't they get a S lock when the UPDATE is completed so that they can not be modified into something which would match the WHERE clause, there by violating the REPEATABLE READ?
    – BuddhiP
    Apr 22 '13 at 15:25
  • @BuddhiP: no - the locks will only be placed on those rows that actually match your UPDATE's WHERE criteria. Only those rows are "scanned" and then updated. Otherwise, you'd be locking the whole table all the time, no??
    – marc_s
    Apr 22 '13 at 15:26
  • True, you have a valid point! Do you happen to have a link to a documentation where this is explained in detail? Is this same in all RDBMS?
    – BuddhiP
    Apr 22 '13 at 15:37
  • @BuddhiP: this is definitely different from vendor to vendor. My response is only for Microsoft SQL Server
    – marc_s
    Apr 22 '13 at 16:09
  • @BuddhiP: updated my response with two links to resources
    – marc_s
    Apr 22 '13 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.