A necessary evil, but still evil. Why? Because it it won’t share with Sch-S and Sch-S is taken and held for absolutely everything (Yes, even your NOLOCK queries). And that can lead to some interesting concurrency problems. Let me explain.
Sch-M
Sch-M is an abbreviation for Schema Modification. It refers to a kind of lock that is taken on a table/index or other object whenever you want to modify that object. SQL Server allows only one Sch-M lock on an object at a time. So if you want to modify a table, your process waits to take a Sch-M lock on that table. Once that lock is granted, the modification is completed and then the lock is released.
Sch-S
Sch-S is an abbreviation for Schema Stability. It is a kind of lock that is taken on an object when a process doesn’t want that object to change its definition. It makes sense. If I’m reading a set of rows through a table, I don’t want a column to disappear on me half way through. SQL Server allows many Sch-S locks on a table.
Sch-S vs. Sch-M
But Sch-S locks are incompatible with Sch-M locks. This means that when you want to modify a table, you’re not granted a Sch-M lock immediately. You have to wait for everyone using that table to finish using it. You’re essentially put on hold until the existing queries complete and their existing Sch-S locks are released. This also means that while you’re waiting, every query who wants to begin using that table is waiting in line behind you too. Basically “Everybody outta the pool while the lifeguards change shifts.” But that’s usually acceptable right? Database schema modifications are a big enough change to require a maintenance window.
Index Rebuilds Are Table Modifications
It’s true, if you have the luxury of maintenance windows for your DB changes, you’ll be alright. But you also have to consider your database maintenance plans (automated or otherwise). Those plans can launch index rebuilds while the database is online. And all index rebuilds also count as table modifications and take Sch-M locks. An index rebuild has syntax like this:
ALTER INDEX [PK_MyTable] ON [MyTable] REBUILD WITH (ONLINE=ON) |
Hopefully you’ve remembered that ONLINE=ON part. When you use that part, the index is rebuilt in the background and at the end of that processing time, a Sch-M lock is taken and released very quickly.
But maybe you’re not so lucky. Maybe you’re not running 2012 yet and have an index that includes blobs. Or maybe you’re running on Standard Edition. In those cases you won’t be able to use the ONLINE=ON feature. In that case, the Sch-M lock is taken by the rebuild process and it’s held the entire time that index is rebuilt. During the rebuild, that index is now truly offline. No access for you.
You Can Get Stuck
Just like I did. A while ago, I was asked to help with this exact situation. An index rebuild had been running for hours it was offline and the Sch-M lock that was held was preventing anybody from using or even looking at that table. I was stuck between a rock and a hard place. I had to choose between letting the index rebuild complete (which could take hours) or cancelling the job (whose rollback could take hours). There was nothing I could do to avoid additional hours of downtime. There was another bit of irony in my situation. We didn’t care about the existing data in that table. A truncate table or drop/recreate table would have suited us just fine.
… Like Really Stuck
It occurred to me to try something out. What if I created an identical empty table with a different name. We didn’t need any data in the locked table. So using a new table could work. And because the locked table is only accessed by stored procedures, I can modify those procedures to use the new table instead of the locked one.
Nope!
For some reason, the ALTER PROCEDURE requires a Sch-S lock on the old table, the table it no longer refers to. The sprocs can’t operate or be modified without a Sch-S lock on that locked table. This sketch illustrates my situation.
Make sure your index plans never attempt to rebuild big indexes offline.
Extras
Some relevant DBAReactions gifs:
Nice explanation.. Thanks Mike..
Comment by RaviTeja — April 22, 2013 @ 2:48 am
Hi very nice explanation. Especially the cartoon is too good.
I always run into this as many of our DB are partitioned. I am not sure if we can rebuild partition indexes online in 2012, something i have to check it out.
Thanks
Comment by Mushtaq Mohammed — April 22, 2013 @ 10:08 am
Michael,
This is one of the most “understandable” articles I have ever read. Your writing style is engaging and down-to-earth. Even end-users will be able to understand these complex subjects after reading your article. Great job!
Comment by Jeff Bennett — April 22, 2013 @ 10:10 am
I also ran into this causing issues with version store/snapshot isolation. We have a regular monthly deployment of price changes, etc. that makes hundreds of thousands of changes to “static” reference data. The goal was to permit this deployment without downtime to the website. The answer: read committed snapshot isolation, so the site can continue to use the version store without user interruption until deployment of the data changes was committed. However, I discovered the deployment process disabled foreign keys (Sch-M lock) to speed the data load, dumped the data, and then re-enabled the foreign keys. All wrapped in a transaction. So the Sch-M was held until the full transaction committed, preventing any usage of the data, even from the version store.
Comment by @sqlbattsman — April 22, 2013 @ 10:21 am
@Mushtaq, @Ravi, @Jeff
Thanks for those words! I almost wish I “finished” the cartoon now.
@sqlbattsman,
Wow, hard lessons learned eh? Although they come with some pain. They seem to stick to the memory better don’t they?
Comment by Michael J. Swart — April 22, 2013 @ 10:31 am
This post is simply awesome! It would be nice to put it in a more eloquent way but “awesome” just emphasizes my precise feeling about it very nicely. You may want to look into teaching as you have a very good way of explaining things. Also those illustrations are very funny. I can only speak for me but humor definitely makes understanding anything easier.
Comment by Manuel — April 22, 2013 @ 11:33 am
Nice post, Michael. It’s definitely made me think twice about changes I thought would be minimally disruptive.
Comment by Sandra — April 23, 2013 @ 11:46 am
I am running this statement below. I believe it is doing a schema lock, as pointed out in your post. This seems to not allow any reads while altering this column. Is that correct? Is there a way to allow dirty reads while doing this operation?
Alter table member.dbo.member Alter Column [user_name] varchar(100);
Thanks,
David
Comment by David — September 29, 2014 @ 10:59 am
Hi David,
There’s no way to allow dirty reads while doing this operation. Schema stability locks (Sch-S) are always taken by any query that reads from a table. The best you can hope for is a short lived schema lock taken by your alter statement.
I wrote about how quick SQL Server is at altering text columns here https://michaeljswart.com/2013/04/altering-text-columns-only-a-metadata-change/
If the schema lock is held for an unacceptably long time, then you may have to get really creative. Consider some fancy data migration techniques as described here:
https://michaeljswart.com/2012/04/modifying-tables-online-part-1-migration-strategy/
This migration strategy is usually really difficult and it doesn’t avoid a schema locks. But it can minimize the duration these locks are held.
Comment by Michael J. Swart — September 29, 2014 @ 12:09 pm
Thanks for your explaination Michael!
I think this is the reason why my Rebuilding Index job in Maintenance Plan always running a whole day and got nothing.
But How can I prevent this problem? index always can’t be rebuilt make me feel sick. lol
Comment by Terence — March 6, 2015 @ 4:46 am
Maybe, maybe not Terence. I’m can’t say anything about your situation, but I know that many have great success by using Ola Hallengren’s maintenance scripts instead of maintenance plans. https://www.google.ca/search?q=ola+hallengren+maintenance
Comment by Michael J. Swart — March 6, 2015 @ 4:36 pm
Assuming table_a is the table that has the sch-m lock due to offline index operation:
Could you have created a table with similar DDL but different name table_b, flushed procedure cache, then created a view (named table_a_ pointing to table_b? I remember that the view would take precedence over the table if the names coincided… not sure if all the necessary steps would be permitted, though. I’ll give it a shot and try to verify with trace flag 1200, but my ability to produce a meaningful repro is always in question 🙂
Comment by sqL_handLe — April 18, 2015 @ 12:51 am
Hi sqL_handle,
I did a demo on this a while ago and it turns out there *is* a way to get around the lock. I haven’t tested whether your method works, but it sounds similar to mine.
Follow this link to get find a set of scripts that demonstrate my own reproduction: http://1drv.ms/1aOm8Xk
Comment by Michael J. Swart — April 21, 2015 @ 9:47 am
[…] seeing? Turns out the Sch-M is a “schema modification lock”, and I have to agree with Michael J. Swart’s article, schema modification locks are indeed evil. They are essentially incompatible with every other lock […]
Pingback by Thanks for the Help with Deadlocks | Oh Yeah — May 11, 2015 @ 9:58 am
I was stuck between a lock and a hard place*
sorrynotsorry
Comment by Randi — October 15, 2019 @ 3:41 am
I’m running into the same thing right now between SQL 2016 database deploys using SSDT and CDC. The deploy wants to GRANT a permission which does not affect the shape of the thing CDC wants to query. It still yields the same Sch-M versus Sch-S competition. The only good thing is Extended Events is so much nicer to monitor for the deadlocks.
Have you found anything new?
Comment by Bryant McClellan — January 3, 2020 @ 3:10 pm
Seems to me that we need to be asking why Sch-S locks are taken out on things that they really don’t need to be taken out on.
“For some reason, the ALTER PROCEDURE requires a Sch-S lock on the old table, the table it no longer refers to.”
Is there an actual reason for this, or is it an oversight?
Comment by Chris Hurd — August 18, 2020 @ 5:55 pm
[…] is nice reading about this lock by Michael […]
Pingback by Duplicate resource on deadlock xml — August 5, 2022 @ 9:31 am