Locking a table while being refreshed

Question

Locking a table while being refreshed

BI_Developer

SSC Eights!

Points: 908
More actions
October 11, 2016 at 10:27 am

#322008

Hi,
Is it possible to block all users from querying a table while it's being refreshed (truncate/insert into) by a maintenance job? I'm not a DBA but would like to know the possible options.
Thx,
J.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

BI_Dev (10/11/2016)
Hi,
Is it possible to block all users from querying a table while it's being refreshed (truncate/insert into) by a maintenance job? I'm not a DBA but would like to know the possible options.
Thx,
J.

You can use Table Hints (Transact-SQL) in the maintenance operation.

😎

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

To block all readers suring your insert operation, either the TABLOCK or TABLOCKX hint will work.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

BI_Developer SSC Eights! Points: 908 More actions · Answer 3

So would the syntax below be correct:

TRUNCATE TABLE DBO.OUTPUTTABLE

INSERT INTO DBO.OUTPUTTABLE WITH (TABLLOCK) OR (TABLLOCKX)

SELECT *

FROM DBO.INPUTTABLE

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 4

BI_Dev (10/11/2016)
Hi,
Is it possible to block all users from querying a table while it's being refreshed (truncate/insert into) by a maintenance job? I'm not a DBA but would like to know the possible options.
Thx,
J.

If you have the space for it, I wouldn't block any users during such an evolution. Instead, here's what I do...

Let's say that the table that you're trying to refresh is called TheTable. To set things up for a "no outage" refresh for all future refreshes...

1. Start out by renaming TheTable to TheTableRefresh1.

2. Now, create a synonym called TheTable and point it at TheTableRefresh1

3. Create an identical table and call it TheTableRefresh2

The stage is set for all future updates.

1. During the next refresh cycle, refresh TheTableRefresh2. During this time, the TheTable synonym is still pointing at TheTableRefresh1 and the users continue to use it.

2. Once TheTableRefresh2 has been refreshed and validated, simply drop and rebuild the TheTable synonym to point at the newly refreshed TheTableRefresh2 table. Total down time will be measured in milliseconds and the system will dutifully wait until the synonym is no longer used before dropping and renaming through "normal blocking protocol".

3. During the next refresh process, just reverse the roles of the tables. Wash, rinse, repeat for each refresh cycle.

Your users will love you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

Here is another "Zero downtime" option that uses partition switching.

See the inline comments for the details...

USE tempdb;

GO

--=============================================================================

-- Create the "original" table and populate it

IF OBJECT_ID('dbo.OriginalTable', 'U') IS NOT NULL

DROP TABLE dbo.OriginalTable;

CREATE TABLE dbo.OriginalTable (

SomeNumber INT NOT NULL

CONSTRAINT pk_OriginalTable_SomeNumber PRIMARY KEY CLUSTERED (SomeNumber) ON [DEFAULT]

);

WITH

n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),

cte_Tally (SomeNumber) AS (

SELECT

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))--(1M sequential numbers)

FROM n n1, n n2, n n3, n n4, n n5, n n6

)

INSERT dbo.OriginalTable (SomeNumber)

SELECT t.SomeNumberFROM cte_Tally t;

-- Verify that 1,000,000 rows were inserted...

--SELECT row_count = COUNT(*) FROM dbo.OriginalTable ot;

--=============================================================================

-- Create a "New" table that contains the newly refreshed values.

-- Note: The NewTable must have the same structure as the OriginalTable... Including indexes!!!

IF OBJECT_ID('dbo.NewTable', 'U') IS NOT NULL

DROP TABLE dbo.NewTable;

CREATE TABLE dbo.NewTable (

SomeNumber INT NOT NULL

CONSTRAINT pk_NewTable_SomeNumber PRIMARY KEY CLUSTERED (SomeNumber) ON [DEFAULT]

);

-- add some "refreshed" data...

WITH

n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),

cte_Tally (SomeNumber) AS (

SELECT

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 2 --(1M even numbers)

FROM n n1, n n2, n n3, n n4, n n5, n n6

)

INSERT dbo.NewTable (SomeNumber)

SELECT t.SomeNumberFROM cte_Tally t;

-- Again... Verify that 1,000,000 rows were inserted...

--SELECT row_count = COUNT(*) FROM dbo.NewTable ot;

--=============================================================================

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION

-------------------------

DECLARE @SwitchStart DATETIME2(7) = SYSDATETIME();

-- 1) Truncate the original...

TRUNCATE TABLE dbo.OriginalTable;

-- 2) Make the switch...

ALTER TABLE dbo.NewTable SWITCH TO dbo.OriginalTable;

-- See how long it took to actually make the switch...

SELECT DownTimeInMS = DATEDIFF(ms, @SwitchStart, SYSDATETIME())

-- 3) Trash NewTable (it's now empty)

DROP TABLE dbo.NewTable;

-------------------------

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@trancount > 0 ROLLBACK TRANSACTION;

DECLARE @msg nvarchar(2048) = error_message();

RAISERROR (@msg, 16, 1);

END CATCH;

-- 4) verify that Original table has the refreshed values.

SELECT TOP 1000 * FROM dbo.OriginalTable ot;