Why is the row exclusive lock still unable to lock other processes to select the data row?

Why is the row exclusive lock still unable to lock other processes to select the data row?

 

Example:

Table TMP, 10000 data records, with ID as the primary key.

Run the following command in process 1 (spid = 55:

 

Run the following command in process 2 (spid = 64:

 

View lock information:

 

We can see that the S lock of the second pair of ID = 10 in the process is not locked by the X lock in process 1. This seems to be in conflict with Microsoft's shared lock and exclusive lock. Why?

 

Explanation:
In general, when SQL server uses the default locking method (Row-Level Lock) to read a row of records, it needs to obtain the is lock (intention to share lock) on the corresponding table, page, and row in sequence) and s locks (shared locks ). If so, connection 64 will be blocked by connection 55 because the S lock on ID = 10 cannot be obtained directly (s lock and X lock are mutually exclusive ).

This involves a special case of SQL Server lock management. When the connection 64 has obtained the is lock on the page where id = 10 is located (Is and IX compatible ), SQL Server first checks whether the page is a dirty page (dirty page), that is, whether the page has been updated since the last time it was written to the disk (usually occurs at the checkpoint. If this page is not a dirty page, SQL Server can directly read data in the row id = 10 without obtaining the S lock on the row, this ensures that all data on this page is not updated in any other active transactions (that is, no dirty read problem occurs ). In this case, the row lock step is omitted for this query statement (even if the rowlock lock is added ).

If we write the statement in connection 64
Select * from TMP with (xlock) Where id = 10
Or
Select * from TMP with (paglock) Where id = 10
The preceding query is blocked by connection 55 because the X lock on the row cannot be obtained (mutually exclusive with the X lock) and the S lock on the page (mutually exclusive with the IX lock.