Locking a table while being refreshed

  • 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.

  • 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.

    😎

  • 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

  • So would the syntax below be correct:

    TRUNCATE TABLE DBO.OUTPUTTABLE

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

    SELECT *

    FROM DBO.INPUTTABLE

  • 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)

  • 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;

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

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