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.