Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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
You can’t perform that action at this time.