3

Assume that SQL Server received both select and update statements to the same table at the same time

Do any of them get prioritized? I know that select statements are delayed until update completed. If table lock continue for a long time due to update, select statement gets cancelled with too much waiting error

So what happens when both received at the same time?

Improve this question

2 Answers 2

Reset to default
7

There is no priority in SQL Server for commands which ever comes first would be executed first. There would always be nanosecond/microsecond difference, of course you would not be able to notice it.

Improve this answer
1
  • 1
    In SQL Server 2014 and above, certain operations can leverage Managed Lock Priority which lets you specify the priority of the request. This is a deviation from the regular behavior of the first come, first serve to allow certain maintenance operations from not blocking critical tasks.
    – Amit Banerjee
    Commented Oct 12, 2015 at 18:31
3

As @Shanky said - the requests will never be exactly at the same time - one will be handled before the other ...

A SELECT statement will place a shared lock (S) on any rows it's reading - depending on the isolation levels, that lock will be held for various amounts of time. In the default READ COMMITTED isolation level, the lock is held only while actually reading the row - once it's read, the lock is released right away.

The shared lock is compatible with other shared locks - so any number of SELECT statements can read the same rows simultaneously.

The UPDATE statement will place an update (U) lock on the row it wants to update, to read the existing values. Then, after that's done, before the actual updated values are written back, the lock is converted into an exclusive (X) lock for the time the data is written. Those locks are held until the transaction they're executing in is committed (or rolled back).

An update lock is not compatible with another update lock, nor with an exclusive lock. It is compatible with a shared lock however - so if the UPDATE statement is currently only reading the existing values, another transaction might read those same values using a SELECT statement with a shared lock.

An exlusive lock is incompatible with anything - you cannot even read the row anymore, while an X lock is on it.

So if you have two statements that come in and try to access the same row, then:

  • if the SELECT comes first, it will place a S lock on the row, read it, and typically release that lock again

  • at the same time, the UPDATE statement can place a U lock on the row and read the existing values; the "promotion" of the lock to X will not be possible until the S lock has been released - if that's not happening, the UPDATE statement will wait, and eventually time out, if the S lock is never released

  • if the UPDATE lock comes first, it will place an U lock on the row to read the existing values

  • at the same time, another transaction could be placing a S lock on the row to read it

  • and again: the UPDATE statement can only progress to the X level to write back the new values once the S lock is gone - otherwise it will time out

  • if the UPDATE lock comes first, it will place an U lock on the row to read the existing values, and already places the X lock on the row to actually do the update

  • then at this time, no other transaction can even read that row - they will have to wait (or time out, if it takes too long for them to get serviced)

Read SQL Server Transaction Locking and Row Versioning Guide for a more in-depth overview of the topic and more details

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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