CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33944

Lock table for Update/Delete/Insert when executing a transaction

I am trying to convert my DELETE statements to TRUNCATE using How to delete large data of table in SQL without log?

Here is what I am trying,

   -- Move recent records from Main table to a Temp table
   -- TRUNCATE the Main table
   -- Return back data from Temp table to Main table

In this period, I wanna stop any INSERT/UPDATE/DELETE statements (until TRUNCATE statement ran) to run on my Main table because if I allow then we might loss some data during TRUNCATE.

Upvotes: 1

Views: 1735

Answers (3)

Dan Guzman
Dan Guzman

Reputation: 46193

Below is an example script that reduces logging the FULL recovery model using SWITCH and TRUNCATE. The SWITCH is a fast meta data only operation. The space deallocation performed by TRUNCATE is done by an asynchronous background thread with larger tables (64MB+) so it is also fast and reduces logging greatly compared to DELETE;

A transaction is used to ensure all-or-none behavior and a schema modification lock is held for the duration of the transaction to quiesce data modifications during the process.

Below is the transaction log space used before and after the process by the example with 1M rows initially and 50K retained:

+--------+---------------+--------------------+
|        | Log Size (MB) | Log Space Used (%) |
+--------+---------------+--------------------+
| Before |      1671.992 |           27.50415 |
| After  |      1671.992 |           30.65533 |
+--------+---------------+--------------------+

Test setup:

--example main table
CREATE TABLE dbo.Main(
      MainID int NOT NULL CONSTRAINT PK_Main PRIMARY KEY
    , MainData char(1000) NOT NULL
);
--staging table with same schema and indexes as main table
CREATE TABLE dbo.MainStaging(
      MainID int NOT NULL CONSTRAINT PK_MainStaging PRIMARY KEY
    , MainData char(1000) NOT NULL
);
--load 1M rows into main table for testing
WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.Main WITH(TABLOCKX) (MainID, MainData) 
SELECT num, CAST(num AS char(1000))
FROM t1g
WHERE num <= 1000000;
GO

Example script:

SET XACT_ABORT ON; --ensures transaction is rolled back immediately even if script is cancelled
BEGIN TRY

    BEGIN TRAN;

    --truncate in same transaction so entire script can be safely rerun
    TRUNCATE TABLE dbo.MainStaging;

    --ALTER TABLE will block other activity until committed due to schema modification lock
    --main table will be empty after switch
    ALTER TABLE dbo.Main SWITCH TO dbo.MainStaging;

    --keep 5% of rows
    INSERT INTO dbo.Main WITH(TABLOCKX) (MainID, MainData)
    SELECT MainID, MainData
    FROM dbo.MainStaging
    WHERE MainID > 950000;

    COMMIT;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;

END CATCH;
GO

Upvotes: 1

Esat Erkec
Esat Erkec

Reputation: 1724

TRUNCATE statement acquires SCH-M lock it means that it creates a Schema Modification lock

Second type of the lock is schema modification lock – SCH-M. This lock type is acquired by sessions that are altering the metadata and live for duration of transaction. This lock can be described as super-exclusive lock and it’s incompatible with any other lock types including intent locks

Locking in Microsoft SQL Server (Part 13 – Schema locks)

During this time, the update, select and delete statements will be waiting for the table truncating operation. As a result, the CRUD operation will stop automatically until the TRUNCATE statement will be completed.

Upvotes: 2

Nayanish Damania
Nayanish Damania

Reputation: 652

Try to use transaction:

BEGIN TRANSACTION

SELECT TOP 1 *
FROM table_name
WITH (TABLOCK, HOLDLOCK)

-- do your stuff

COMMIT

Upvotes: 1

Related Questions