SQL Server Lock Experiment (update lock probe)

in this example, the begin Tran and with (holdlock) hints are used to observe the locks of SQL Server in the SELECT statement. The transaction is turned on to ensure that the lock is also observed by a very short query, because HOLDLOCK releases the lock after the transaction ends. UPDATE statement: The lock case is: you can see that the lock-up situation is as follows:1. Table-level IX lock added2. IX locks are added for data page 3105 to update the data rows3. Added x-mode key lock to data row in data page 31054. To update the relevant index containing the CLINICID column after the data has been updated, let's take a look at the index that involves the column: (You can see that the index is really 60th)

SELECT * from sys.indexes where object_id=object_id (' ris_request ')

An IX lock was added to index page 50160, 1458239 to update the index row5. Add an X-mode key lock to the index row in the 2 index pages above, and update the index line to complete. Here's a question: I'm updating by the primary key, which means that only one row of data is involved, and that the index should only correspond to a single row, why does the X-mode key lock appear for 2 index records? only to see 50160, 1458239 of the two pages of the specific content, here I have two pages of the specific content of all the DBCC page to dump out: page 50160 captures only the parts that may contain the primary key 2012121218060024: 1458239 pages Total only so many rows of records: you can see that the index of the primary key value of 2012121218060024 is the 2nd row in the 1458239 index page, and its corresponding hash value is also seen in the sp_lock 7be5a319785d, The other hash value fcee1248c8e6 the corresponding index row is not found, this can be foreseen, because Clinicid is updated after the hash value must change.      

SQL Server Lock Experiment (update lock probe)