SET IDENTITY_INSERT OFF - Caused Table Lock

Question

I Executed SQL on database that looked like this:

SET IDENTITY_INSERT table1 ON
GO
BEGIN TRANSACTION
GO
INSERT INTO table1 SELECT ... FROM table2
GO
DELETE FROM table2 ...
GO
COMMIT TRANSACTION
GO
SET IDENTITY_INSERT table1 OFF
GO

Problem is my table was locked on last statement SET IDENTITY_INSERT table1 OFF, all previous statements took around 6 hours, but my table is locked on this last statement for 1 day now.

In Activity Monitor it has connection that dont execute any command but is head blocker of locking chain, i killed it but nothing changed only that is in rollback state but it's still eating all I/O.

My question is what this session is doing and how to avoid it? It looks like IDENTITY_INSERT is almost costly like full table recreation for some reason, i don't know how this could cause any downsides on performance side.

Additional info: there was not identity collision during insert, bcs i insert operated on lower identity values than initial set on table1

Table design:

CREATE TABLE [dbo].[table1] (
    [id_table1] BIGINT     IDENTITY NOT NULL,
    [id_table3]              INT        NOT NULL,
    [some_id]    BIGINT     NOT NULL,
    [some_time2]           DATETIME2   NOT NULL,
    [some_time]            DATETIME2   NOT NULL,
    [some_value]                FLOAT (53) NOT NULL,
    [some_value2]               FLOAT (53) NOT NULL,
    [some_value3]            TINYINT    NULL,
    CONSTRAINT [FK_table1_table3] FOREIGN KEY ([id_table3]) REFERENCES [dbo].[table3] ([id_table3]),
    INDEX [IX_table1] CLUSTERED ([id_table3] ASC, [some_time] ASC, [id_table1] ASC) WITH (DATA_COMPRESSION = PAGE)
) WITH (DATA_COMPRESSION = PAGE);
GO
CREATE INDEX [IX_table1_some_time] ON [dbo].[table1] 
([some_time] ASC, [id_table1] ASC) INCLUDE ([id_table3], [some_value], [some_value2], [some_value3]) WITH (DATA_COMPRESSION = PAGE)
GO

Answer 1

SET IDENTITY_INSERT is just a session option allowing you to insert identity values explicitly in a subsequent statement. It doesn't do anything to the transaction log. I presume your first transaction wasn't actually committed and now is rolling back.

This could happen if you have implicit_transactions on

Or a possibly more likely scenario is that you were already in an open transaction unknowingly.

This could happen if your original script contained an error between the begin tran and commit. Executing could then terminate execution leaving an open transaction (If you had used SET XACT_ABORT ON any error would have rollbacked the transaction rather than leaving it open.)

Then after fixing the error and rerunning the script in the same session the begin tran would be called again and increment @@trancount and the COMMIT would just decrement it down to 1, leaving the transaction still open.

Rather than killing the session and causing transaction rollback the correct thing to do in that case (assuming you were happy that everything was OK and the original error condition hadn't caused changes that you wouldn't want to commit) would have been to check @@trancount and commit again as many times as needed (I.e. in the event that there were multiple executions before the error was fixed) until that reached zero.