MmKVMwUe

1/* Note: don't run this all at once. There are prompts to run some queries in another session, etc. */
2
3WHILE @@trancount > 0
4	ROLLBACK
5GO
6
7USE master;
8GO
9
10IF DB_ID('lockingtest') IS NOT NULL
11BEGIN
12	ALTER DATABASE lockingtest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
13	DROP DATABASE lockingtest;
14END
15GO
16
17CREATE DATABASE lockingtest;
18GO
19
20USE lockingtest;
21GO
22
23SET NOCOUNT ON;
24
25/********************************************************
26Here we have dbo.ProductionTable and dbo.StagingTable
27*********************************************************/
28DROP TABLE IF EXISTS dbo.ProductionTable;
29GO
30
31CREATE TABLE dbo.ProductionTable (
32	i int identity not null,
33	varcharcol varchar(256) default ('old data'),
34	tinyintcol tinyint default (2),
35	intcol int default (20000),
36	GUIDcol uniqueidentifier default (newid()),
37	datetime2col datetime2(0) default ('2016-01-01')
38);
39GO
40/* populate */
41DECLARE @i INT = 1;
42BEGIN TRAN
43	WHILE @i < 1000
44	BEGIN
45		INSERT dbo.ProductionTable DEFAULT VALUES;
46		SET @i=@i+1;
47	END
48COMMIT
49GO 
50
51DROP TABLE IF EXISTS dbo.StagingTable;
52GO
53
54CREATE TABLE dbo.StagingTable (
55	i int identity not null,
56	varcharcol varchar(256) default ('New data'),
57	tinyintcol tinyint default (2),
58	intcol int default (20000),
59	GUIDcol uniqueidentifier default (newid()),
60	datetime2col datetime2(0) default ('2017-01-01')
61);
62GO
63/* populate */
64DECLARE @i INT = 1;
65BEGIN TRAN
66	WHILE @i < 2000
67	BEGIN
68		INSERT dbo.StagingTable DEFAULT VALUES;
69		SET @i=@i+1;
70	END
71COMMIT
72GO 
73
74
75/********************************************************
76Traditional method: use rename.
77Problem: what if another query has a shared schema lock on the table?
78*********************************************************/
79
80
81--Run in another session:
82BEGIN TRAN
83
84	SELECT top 1 i
85	FROM dbo.ProductionTable WITH (HOLDLOCK)
86
87	
88
89
90--Now back in this session:
91
92exec sp_rename 'dbo.ProductionTable', 'ProductionTableOld';
93GO
94
95--We'll be blocked. 
96--We can see this by running sp_WhoIsActive in a third session
97
98--cancel the rename, leave the select running in the other session 
99
100
101
102/********************************************************
103Alternate approach: partition switching
104*********************************************************/
105
106--Create ProductionTableOld
107CREATE TABLE dbo.ProductionTableOld (
108	i int identity not null,
109	varcharcol varchar(256) default ('old data'),
110	tinyintcol tinyint default (2),
111	intcol int default (20000),
112	GUIDcol uniqueidentifier default (newid()),
113	datetime2col datetime2(0) default ('2016-01-01')
114);
115GO
116
117BEGIN TRAN
118
119	ALTER TABLE dbo.ProductionTable SWITCH PARTITION 1 TO dbo.ProductionTableOld PARTITION 1
120		WITH ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ));  
121
122	--Anyone who tries to query the table after the switch has happened and before
123	--the transaction commits will be blocked: we've got a schema mod lock on the table
124
125	ALTER TABLE dbo.StagingTable SWITCH PARTITION 1 TO dbo.ProductionTable PARTITION 1
126
127COMMIT
128
129--Voila, we now have only New Data
130SELECT * FROM dbo.ProductionTable
131
132--This has old data
133SELECT * FROM dbo.ProductionTableOld
134
135--This is empty
136SELECT * FROM dbo.StagingTable
137
138
139/********************************************************
140What if we just wanna ditch the old data?
141*********************************************************/
142
143--Rerun the commands above to create and populate ProductionTable and StagingTable
144--Restart the select in another session
145
146BEGIN TRAN
147
148	TRUNCATE TABLE dbo.ProductionTable   
149	WITH (PARTITIONS (1));
150
151	--Anyone who tries to query the table after the switch has happened and before
152	--the transaction commits will be blocked: we've got a schema mod lock on the table
153
154	ALTER TABLE dbo.StagingTable SWITCH PARTITION 1 TO dbo.ProductionTable PARTITION 1
155
156COMMIT
157
158--Whoops, this has a problem. TRUNCATE TABLE doesn't have WAIT_AT_LOW_PRIORITY and its glorious options.
159--If you wanna ditch the data and be able to manage the blocking situation better, you gotta
160--switch out to another table with WAIT_AT_LOW_PRIORITY and your preferred options, then truncate there.