Skip to content

Instantly share code, notes, and snippets.

@LitKnd
Last active August 4, 2023 15:35
Show Gist options
  • Save LitKnd/11543b08b5b97ad9588d44dbc6f2047f to your computer and use it in GitHub Desktop.
Save LitKnd/11543b08b5b97ad9588d44dbc6f2047f to your computer and use it in GitHub Desktop.
/* 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