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

Question

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.

`TRUNCATE` will acquire an exclusive table lock for the duration of the operation an will block concurrent activity. — Dan Guzman, Mar 07 '20 at 18:57
TRUNCATE will remove all data in the table. Can you elaborate on what data loss you are concerned about? If there's data in the table you don't want to lose, don't truncate or delete. — Dan Guzman, Mar 07 '20 at 19:01
@DanGuzman think about this. After moving data to the Temp table and before TRUNCATE starts if there is any data INSERTED/UPDATED/DELETED will be lost. — Imran Qadir Baksh - Baloch, Mar 07 '20 at 19:01
I see what you mean now. If you move data with an INSERT...SELECT... WITH(TABLOCKX), TRUNCATE, and move back all in the same transaction. Then there is no risk of loss. — Dan Guzman, Mar 07 '20 at 19:03
I assume there's a reason form moving the data out and back in? Why not modify the table directly with an exclusive lock? — Dan Guzman, Mar 07 '20 at 19:06
related to my previous question https://stackoverflow.com/questions/60542070/delete-without-affecting-the-log-files I am using DELETE which is hammering the transaction log. That's why moving to TRUNCATE — Imran Qadir Baksh - Baloch, Mar 07 '20 at 19:07
I assume the move data back is an INSERT statement...wouldn't that be fully logged? Is the database in the SIMPLE recovery model? — Dan Guzman, Mar 07 '20 at 19:11
@DanGuzman DB is in FULL recovery mode. The data that we INSERT again is limited around 1-5% of the total data. David, how can I implement partitions in the above query? — Imran Qadir Baksh - Baloch, Mar 08 '20 at 05:25

score 2 · Answer 1 · answered Mar 07 '20 at 19:27

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.

See my above comment, `after moving data to the Temp table and before TRUNCATE starts if there is any data INSERTED/UPDATED/DELETED will be lost` — Imran Qadir Baksh - Baloch, Mar 08 '20 at 05:26

Nayanish Damania · Answer 2 · 2020-03-07T19:10:11.507

1

Try to use transaction:

BEGIN TRANSACTION

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

-- do your stuff

COMMIT

edited Mar 07 '20 at 19:10

answered Mar 07 '20 at 18:57

Nayanish Damania

542
5
13

`TRUNCATE` runs in an autocommit transaction by default. The explict transaction doesn't provide value unless there are other statements in the same transaction. – Dan Guzman Mar 07 '20 at 18:58

Dan Guzman · Accepted Answer · 2020-03-08T21:22:45.723

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

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

3 Answers3