Post reply

LCK_M_IX ON Truncate table

  • May 16, 2017 at 9:31 am

    #323548

    Hello,

     I am receiving LCK_M_IX  locks on truncating the table (140K) records . It was fine until last week and all of a sudden I am experiencing this issue. Please help what needs to be done. There was no change of code ..

    IF @RowCount > 0

    BEGIN

    --INSERT new records

    --LOCKS happening here

         TRUNCATE TABLE dbo.TableA <---------------------

    INSERT INTO dbo.TableA ( Column 1 INT,
                             ..)

    SELECT ..

    END

  • May 17, 2017 at 1:10 pm

    #1942998

    If the truncate is waiting with a wait type LCK_M_IX lock it means it's waiting for an Intent Exclusive lock and some other incompatible lock has it waiting. And if that's what you mean then you need to look at the locks on the table at the time querying sys.dm_tran_locks. You can filter that by database and table along the lines of:
    SELECT *
    FROM sys.dm_tran_locks
    WHERE resource_database_id = YourDatabaseID
    AND resource_associated_entity_id = OBJECT_ID(YourTableName);

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

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