Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to troubleshoot locking behavior and the READ_COMMITTED_SNAPSHOT isolation level while attempting to resolve concurrency issues.

Background: Assume an online ordering system (ecommerce). Product price changes are calculated minimum monthly, and this results in around 600,000 records that must be changed in the database. When posting the price change updates to the database (SQL Server 2008R2 Web Edition) the site becomes unusable due to the significant levels of locking in the primary ProductDetails table when using READ_COMMITTED transaction isolation level.

To resolve this, READ_COMMITTED_SNAPSHOT is enabled, however other transactions are still being blocked during the price updates. Investigation of sys.dm_tran_locks shows the blocked session is caused by a waiting Sch-S lock. As I understand it, Sch-S locks (schema stability) are taken while a schema-dependent query is being compiled and executed (aren't they all schema-dependent?). But sys.dm_tran_locks also shows a series of Sch-M locks (schema modification), which are not compatible with any outside operations per BOL. I assume this is caused by the fact that the 3rd party tool used to replicate data changes drops foreign keys during the update process and recreates them after the update is completed. And so, in spite of READ_COMMITTED_SNAPSHOT, other queries are still blocked, not by the update, but by the Sch-M locks cause by the changes to foreign key relationships.

This theory was confirmed by eliminating the setting that dropped/recreated the foreign keys. Now the update process no longer takes Sch-M locks (sys.dm_tran_locks only shows X, IX, S locks), and other transactions are not blocked from using the version store to satisfy their queries.

However, when executed using this process, the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated.

Can anyone explain this behavior and offer suggestions on how concurrency could be maintained without exponentially increasing the maintenance time for price changes?

share|improve this question
    
Do you have indexes on the referring side of your FK constraints? –  A-K Oct 9 '12 at 18:18
    
@AlexKuznetsov: you think the OP have an unindexed FK on a big table causing table scans? –  Fabricio Araujo Oct 9 '12 at 19:31
    
@FabricioAraujo this is one possible explanation –  A-K Oct 9 '12 at 19:35
    
Thanks for the feedback guys. I went back and noticed several tables missing indexes on their foreign keys. Have not had time to verify all. I'll have to go back and add these indexes in and test again to verify. Thanks –  sqlbattsman Oct 9 '12 at 21:07

2 Answers 2

up vote 3 down vote accepted

The following behavior may be caused by missing indexes on referring side of your FKs: "the price changes take approx 1 hour to process (vs. 1-2 minutes) and sys.dm_tran_locks shows the transaction taking almost 90,000 different locks, compared to around 100-150 when foreign keys were being dropped/recreated"

When a row is deleted or its PK/Unique is mutating, the database engine need to make sure there are no orphans. When there is no proper index to support it, it scans the whole thing.

share|improve this answer

AFAIK, snapshot isolation works on DML only. DDL still gets it's Sch-M/S locks, which - in your present case - are caused by drop/recreate FKs.

Another way of doing that is creating an price map (in fault of a english word equivalent for this concept) with validity date (I.e the datetime where it becomes valid).

Things become more complex, I agree, but you can create an price table simply INSERTING a new price map.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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