1

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:

table output with page information

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.

4

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.

  • Thank you for detailed explanation. You are perfectly right, this path_id index is not selective, not even close. In fact, it appears to have no real merit. I rearranged indexes a bit, I removed this path_id index completely and added path_id as a second field in what used to be an index on value_string. With this locks became more precise. At very least I don't see engine looking too many pages. – Andrewani Nov 11 '15 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.