SQL Server Lock Experiment (SELECT Lock Explore)

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. 1. Query the SELECT statement for the primary key index The lock case is:Here I select a more forward primary key value, the result set has 6, because is the primary key index of the range scan so scan to the 7th row stop, so hold 7 key lock, moreover these records are in one page (1:3104 pages), So the final case is the 7 key lock shown and the is lock for a data page. Each key lock here corresponds to a primary key data row. It is important to note that if a range query has too many key values (approximately 5000), a lock escalation occurs, where only one s lock is added to the table, releasing all primary key key locks and is table locks. For lock escalation See the website lock upgrade page.   2. Querying a SELECT statement for a nonclustered index The lock case is: Patientid is a non-unique nonclustered index, the result set only 1, so the index lookup will find an index key, so add two key lock, of course, the index page 22872 of the key itself also needs an IS lock, After that, the bookmark lookup needs to be locked on page 3104 to get the data rows, and then the key locks of type S are added to the related data rows. Here we then use the DBCC page to look at the contents of index page 22872: (only the first 10 lines are intercepted here)you can find that the bookmark for the nonclustered index page is the key value of the primary key clustered index, and the bookmarks for the Oracle index are all ROWID.    3. Select statement for full table scan 1) When I directly execute select * FROM Ris_request, its lock condition is:The SELECT statement locks each page that is read on the table, and then releases the S lock on this page after reading and acquiring the S lock on the next page, with only one is lock on the table. 2) When I add with (holdlock):At the beginning, the statement will lock the s on many pages and add the is lock on the table, but soon it will be converted to a table-level s lock, and the S-page lock is fully released.  

SQL Server Lock Experiment (SELECT Lock Explore)