Developers have struggled with a problem for a long time: how do I load up a new table, then quickly switch it in and replace it, to make it visible to users?
There’s a few different approaches to reloading data and switching it in, and unfortunately most of them have big problems involving locking. One method is this:
- Create a schema for staging
- When loading tables, create them in the staging schema
- When load is complete, drop the table from your production schema
- Then use ALTER SCHEMA TRANSFER to move the staging table into the production schema
This method sounds good, because items being worked on for staging are easy to identify in their own schema. There’s clearly going to be issues with concurrency between dropping the table and transferring in the new table, but the gamble is that will be fast and can be minimized.
ALTER SCHEMA TRANSFER locks the whole schema
The concurrency problem is worse than you might think. When you run ALTER SCHEMA TRANSFER, it locks up everything in that schema, so nobody can use any table– the lock is not limited to the table you’re transferring.
Not sure? Let’s take a look. First, we create database:
|
1 2 |
CREATE DATABASE LockTest; GO |
The create a schema named LockMe in the database. Create a table named InnocentTable in the LockMe schema, and insert some rows.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE LockTest; GO CREATE SCHEMA LockMe AUTHORIZATION dbo GO CREATE TABLE LockMe.InnocentTable ( i int identity not null ); GO INSERT LockMe.InnocentTable DEFAULT VALUES GO 10 |
Now create a new table in the dbo schema and add a few rows. We’re going to transfer this into the LockMe schema.
|
1 2 3 4 5 6 |
CREATE TABLE dbo.NewFriend ( i int identity not null ); GO INSERT dbo.NewFriend DEFAULT VALUES GO 20 |
Alright, now it’s time to do the transfer! Here we go. To make this easy to see, we’re going to leave a transaction open:
|
1 2 |
BEGIN TRAN ALTER SCHEMA LockMe TRANSFER NewFriend |
In another session, we just try to query LockMe.InnocentTable. We didn’t run any commands against it, but this query gets blocked and can’t make any progress:
|
1 2 |
SELECT TOP 1 * FROM LockMe.InnocentTable |
Looking at this in a third session with sp_WhoIsActive, we can see the problem:
Some Bad News: There Is No Awesome Way to Swap In a Table
The locking you see above is a very high level lock. The query can’t even get a schema stability lock on LockMe.InnocentTable. There’s no magic hint that will get you around it: adding a NOLOCK hint won’t work (it still needs a schema stability lock).
Optimistic locking won’t get you around it either — queries in SNAPSHOT and READ COMMITTED SNAPSHOT need to get a schema stability lock, too.
If You Must Swap In a Table, ALTER SCHEMA is the Greater Evil
Unless you really want to block absolutely everyone who might use the schema (and live with the consequences), this command will cause you pain.
Need to get to the root cause of blocking? Read more on locking here on our blog for free, or check out our SQL Critical Care®.
16 Comments. Leave new
What’s wrong with using SWITCH PARTITION? It doesn’t require partitioning to be used.
Partition switching still requires a schema modification lock, and can block and be blocked.
What’s wrong with a simple table rename? ie:
exec sp_Rename ‘tab’, ‘tab_old’;
exec sp_Rename ‘tab_new’, ‘tab’;
Or does this cause the schema lock as well?
Yep, renaming causes an exclusive lock. If you don’t have a large amount of read activity or long running reads, that can be OK. But if your number of reads goes up or you start to have longrunning transactions holding locks, it becomes very painful to scale.
I often use partitioned views for this pattern.
The Alter View statement is blocking but it does not take much time. According to BOL it takes an Exclusive Schema Lock on the View.
One of my staging ‘tables’ receives on the order of 175M rows/week (200GB). A job creates a new table each week and then alters the view to add the new “sub-table” and remove the oldest “sub-table”. We have applied this to several of our big staging tables and it works very well.
This approach saves us loads of cleanup since we can just Truncate/Drop the old “sub-table”.
I love partitioned views.
As long as it’s pretty fast, you’re doing fine. Some folks find themselves in a position where they have to kill off activity to be able to modify the view, and that gets ugly really fast. Table partitioning has similar issues switching in partitions, but they have added some automation as to whether to kill others/kill self/etc in 2014 at least.
This seems like it might be a good fit for a retry loop with a low lock_timeout or the low priority waits in 2014 and higher. Of course if you have super long running transactions it’s still going to take forever or fail. What do you think of this idea?
Seems like a good reason to use several schemas? That at least reduces the scope of the lockout.
I’m with Jeff. Why not just use a schema per staging table (or a group of such tables that tend to be updated as a batch) to avoid the collateral damage from the schema locks when transferring or renaming? This may be a nuisance if you have lots of staging tables, but how bad can that really be?
Let’s say I have a long running query on the currently active table. As soon as I start to do anything that requires a schema modification lock, I’m blocked by it and can create a massive blocking chain behind *me*. It can be really, really bad.
Thanks for blogging about this Kendra, this is really cool. ALTER SCHEMA TRANSFER has always been in my bag of dev tricks and now I know to tread lightly.
It’s interesting, I’ve been bitten by terrible locking scenarios like the one you describe like The Sch-M lock is Evil.
Ironically, in that case, it turns out that the ALTER SCHEMA command could have saved me like I demonstrate in this demo. In this case, I created a special schema called “obsolete” that no one else cared about. I was locking on the ignorable “obsolete” schema rather than the important “dbo” schema. That’s the distinction
Yay for synonyms. Still have locking issues when switching them, of course, but then what doesn’t.
[…] In the past, I wrote that if you have to do this switcheroo, sp_rename is better than ALTER SCHEMA TRANSFER, but it still has a bunch of problems. […]
We have the need to occasionally reload our entire EDW set of tables. The only access our customers can use are through our views. So we copy all rows to shadow db tables and re-point the views to that shadow db while we do the reload. Of course there’s the potential locking of views issue. But we monitor usage and do it off hours and its quick. Which is the best method to play the switching game – synonyms, schema transfer or alter view? Partitions don’t seem appropriate here for multiple reasons. Or is there another option I haven’t thought of?
I had to learn this the hard way. I wish I had read this article before implementing a solution recommended by the architect. I used Alter Schema to move staging tables to final schema, major locks happened. Now I will have to go back and change it.
Whoops! Well, at least we’ve learned something.