How does SQL Server update lock work?

Question

I'm learning SQL Server and trying to understand how SQL Server updates a row.

As I understand, first SQL Server puts an intent exclusive lock on the database, then an intent exclusive lock on the table, and then an update lock on the record to be updated. Later, it will convert the update lock on the row to an exclusive lock so that the data in the row can be modified.

But the problem is that shared lock is compatible with an update lock. So, while there is an update lock on the row, other sessions can still read the row using a shared lock. With a shared lock on the row, it's impossible to convert the update lock to an exclusive lock, because shared and exclusive locks are incompatible.

Does that mean if there are sessions reading the row continuously, SQL Server simply doesn't have a chance to update the row. This could potentially last for an extended period of time. Is this true? Will SQL Server wait until there are no shared locks on the row and then started updating the data in it?

By 'continuously', I mean a session reads the row, and before the shared lock gets released from the row, another session puts a shared lock on the same row. And before the second shared lock gets released from the row, another session puts another shared lock on the same row, etc. So, there is always at least a shared lock on the row, basically.

Answer 1

SQL Server does not take an intent exclusive (IX) lock on the database in the situation you describe. IX locks are taken at the object (table) level, and on the page containing the target row before an update (U) lock is taken on the row.

The scenario where newly arriving repeated shared (S) locks would prevent a conversion from update (U) lock to exclusive (X) is called 'lock starvation'. SQL Server takes steps to avoid this.

In early versions of SQL Server, locks were granted in strict first-in, first-out (FIFO) order. This means a waiting lock always makes progress toward the front of the queue.

This arrangement has been refined several times since then, and not all the details are public. If you are interested, you can find some details in SQL Server, Lock Manager, and “relaxed” FIFO… by the Microsoft CSS team. A short quote:

Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce unnecessary deadlocks and blocking. New lock requests where the requestor does not yet own a lock on the resource become blocked if the requested mode is incompatible with the union of granted requests and the modes of pending requests. A conversion request becomes blocked only if the requested mode is incompatible with the union of all granted modes, excluding the mode in which the conversion request itself was originally granted.