Excessive page locking There is a query:


SELECT entry_id FROM table_name 
WHERE (path_id = 7 AND value_string =  N'00020003-0004-0005-0006-000700080009' )


The table in this query contains some millions of records. Both path_id and value_string have their own non-clustered indexes.

When I use SQL Server Profiler to trace this query with Lock:Acquired event, I see that there are three IS page locks taken: one on a page that contains this value_string as '00020003-0004-0005-0006-000700080009', one on a page that contains path_id as 6 (which is staggering), and one on a page that contains path_id as 7 (which is expected).

The way I get information about pages: in Lock:Acquired there is a field TextData containing text like 1:87032; I take this and execute


DBCC PAGE ('mc33', 1, 87032, 3) 


Then I get output like this:



The question is: why engine puts an IS lock on a page containing only sixes, if the query is only interested in sevens? This "excessive" locking causes us deadlocks when this query is executed in parallel with certain inserts, and I would like to know how I can get rid of such locking.
Locating a value implies traversal from the upper level point anchor (which explains the 6es). IS locks are required for traversal under read-commited isolation. This is neither "excessive" nor unexpected. If I'd venture a guess I would speculate that path_id is a low selectivity value and the index is not helping much.

You complain about deadlocks, I suggest you investigate them accordingly, starting with the deadlock graph.

As a side note, excessive locking (when truly a concern) can be alleviated using row versioning based isolation levels. If you're interested in low level details on how B-Tree seek actually works, start with Transaction Processing: Concepts and Techniques

To detail a bit: an inner page will contain a key and a pointer to a page/slot. This means that 'the page starting at the pointer contains values smaller than this key value'. Seek will search the upper page until it finds the first key bigger than the value seek-ed. It jumps to the pointer and continues from there, recursively. If the upper page says 'from page 148 all keys are smaller than 8' and the page 148 contains 6es, seek will  lock page with 6es. There is no magic pixie dust here. Locking lots of 6es is a clear hint that the key has very low selectivity: there are many rows that repeated a small range of key values.