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?
Best Answer
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.