/* Note: don't run this all at once. There are prompts to run some queries in another session, etc. */ | |
WHILE @@trancount > 0 | |
ROLLBACK | |
GO | |
USE master; | |
GO | |
IF DB_ID('lockingtest') IS NOT NULL | |
BEGIN | |
ALTER DATABASE lockingtest SET SINGLE_USER WITH ROLLBACK IMMEDIATE; | |
DROP DATABASE lockingtest; | |
END | |
GO | |
CREATE DATABASE lockingtest; | |
GO | |
USE lockingtest; | |
GO | |
SET NOCOUNT ON; | |
/******************************************************** | |
Here we have dbo.ProductionTable and dbo.StagingTable | |
*********************************************************/ | |
DROP TABLE IF EXISTS dbo.ProductionTable; | |
GO | |
CREATE TABLE dbo.ProductionTable ( | |
i int identity not null, | |
varcharcol varchar(256) default ('old data'), | |
tinyintcol tinyint default (2), | |
intcol int default (20000), | |
GUIDcol uniqueidentifier default (newid()), | |
datetime2col datetime2(0) default ('2016-01-01') | |
); | |
GO | |
/* populate */ | |
DECLARE @i INT = 1; | |
BEGIN TRAN | |
WHILE @i < 1000 | |
BEGIN | |
INSERT dbo.ProductionTable DEFAULT VALUES; | |
SET @i=@i+1; | |
END | |
COMMIT | |
GO | |
DROP TABLE IF EXISTS dbo.StagingTable; | |
GO | |
CREATE TABLE dbo.StagingTable ( | |
i int identity not null, | |
varcharcol varchar(256) default ('New data'), | |
tinyintcol tinyint default (2), | |
intcol int default (20000), | |
GUIDcol uniqueidentifier default (newid()), | |
datetime2col datetime2(0) default ('2017-01-01') | |
); | |
GO | |
/* populate */ | |
DECLARE @i INT = 1; | |
BEGIN TRAN | |
WHILE @i < 2000 | |
BEGIN | |
INSERT dbo.StagingTable DEFAULT VALUES; | |
SET @i=@i+1; | |
END | |
COMMIT | |
GO | |
/******************************************************** | |
Traditional method: use rename. | |
Problem: what if another query has a shared schema lock on the table? | |
*********************************************************/ | |
--Run in another session: | |
BEGIN TRAN | |
SELECT top 1 i | |
FROM dbo.ProductionTable WITH (HOLDLOCK) | |
--Now back in this session: | |
exec sp_rename 'dbo.ProductionTable', 'ProductionTableOld'; | |
GO | |
--We'll be blocked. | |
--We can see this by running sp_WhoIsActive in a third session | |
--cancel the rename, leave the select running in the other session | |
/******************************************************** | |
Alternate approach: partition switching | |
*********************************************************/ | |
--Create ProductionTableOld | |
CREATE TABLE dbo.ProductionTableOld ( | |
i int identity not null, | |
varcharcol varchar(256) default ('old data'), | |
tinyintcol tinyint default (2), | |
intcol int default (20000), | |
GUIDcol uniqueidentifier default (newid()), | |
datetime2col datetime2(0) default ('2016-01-01') | |
); | |
GO | |
BEGIN TRAN | |
ALTER TABLE dbo.ProductionTable SWITCH PARTITION 1 TO dbo.ProductionTableOld PARTITION 1 | |
WITH ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS )); | |
--Anyone who tries to query the table after the switch has happened and before | |
--the transaction commits will be blocked: we've got a schema mod lock on the table | |
ALTER TABLE dbo.StagingTable SWITCH PARTITION 1 TO dbo.ProductionTable PARTITION 1 | |
COMMIT | |
--Voila, we now have only New Data | |
SELECT * FROM dbo.ProductionTable | |
--This has old data | |
SELECT * FROM dbo.ProductionTableOld | |
--This is empty | |
SELECT * FROM dbo.StagingTable | |
/******************************************************** | |
What if we just wanna ditch the old data? | |
*********************************************************/ | |
--Rerun the commands above to create and populate ProductionTable and StagingTable | |
--Restart the select in another session | |
BEGIN TRAN | |
TRUNCATE TABLE dbo.ProductionTable | |
WITH (PARTITIONS (1)); | |
--Anyone who tries to query the table after the switch has happened and before | |
--the transaction commits will be blocked: we've got a schema mod lock on the table | |
ALTER TABLE dbo.StagingTable SWITCH PARTITION 1 TO dbo.ProductionTable PARTITION 1 | |
COMMIT | |
--Whoops, this has a problem. TRUNCATE TABLE doesn't have WAIT_AT_LOW_PRIORITY and its glorious options. | |
--If you wanna ditch the data and be able to manage the blocking situation better, you gotta | |
--switch out to another table with WAIT_AT_LOW_PRIORITY and your preferred options, then truncate there. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment