SQL Server IDENTITY_INSERT – Impact on Concurrency

identityinsertsql serversql-server-2005

I am trying to help a customer with a 3rd party SAP add-on that has a posting glitch and has gone out-of-support.

Under certain circumstances, it archives and incomplete post from the posting queue table to the posting archive table. I need to move these archived results back into the queue.

The queue ID is an identity column and I would like to keep it the same.

The question is, if I do identity_insert on/insert/identity_insert off, what can I expect with respect to concurrency with processes that create the queue entries and expect the identity column to be generated automatically?

Any pointers on the best way to demonstrate such behavior would be greatly appreciated as well.

Best Answer

Setting IDENTITY_INSERT ON on its own does not eliminate concurrency - this does not place any exclusive locks on the table, only a brief schema stability (Sch-S) lock.

So what could theoretically happen, under the default behavior, is you could do this in session 1:

BEGIN TRANSACTION;

-- 1
SET IDENTITY_INSERT dbo.tablename ON;

-- 2
INSERT dbo.tablename(id, etc) VALUES(100, 'foo'); -- next identity is now 101

-- 3
INSERT dbo.tablename(id, etc) VALUES(101, 'foo'); -- next identity is now 102

-- 4
SET IDENTITY_INSERT dbo.tablename OFF;

COMMIT TRANSACTION;

In another session, you can insert rows into the table at points 1, 2, 3 or 4. This may seem like a good thing, except what happens for any insert that happens between 2 and 3, is that the auto-generated value triggered by another session is based on the results of statement 2 - so it will generate a 101, and then statement 3 will fail with a primary key violation. This is pretty simple to set up and test yourself with some WAITFORs:

-- session 1
-- DROP TABLE dbo.what;
CREATE TABLE dbo.what(id INT IDENTITY PRIMARY KEY);
GO
BEGIN TRANSACTION;

SET IDENTITY_INSERT dbo.what ON;

INSERT dbo.what(id) VALUES(32);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(33);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(34);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(35);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(36);

SET IDENTITY_INSERT dbo.what OFF;

COMMIT TRANSACTION;

Once that batch has started, start this batch in another window:

-- session 2
INSERT dbo.what DEFAULT VALUES;
WAITFOR DELAY '00:00:01';
GO 20

Session 2 should only ever insert values from 1-20, right? Except that because the underlying identity has been updated by your manual inserts session 1, at some point session 2 will pick up where session 1 left off, and insert 32, or 33, or 34 etc. It will be allowed to do this, but then session 1 will fail on the next insert with a PK violation (which one wins may just be a matter of timing).

One way to workaround this is to invoke a TABLOCK on the first insert:

INSERT dbo.what WITH (TABLOCK) (id) VALUES(32);

This will block any other users trying to insert (or do anything, really) with this table until you are done moving those archived rows back. This throttles concurrency, sure, but this is the way you want blocking to work. And hopefully this isn't something that's happening at such a frequent rate where you're blocking other people all the time.

A couple of other workarounds:

  • stop caring about the IDENTITY value generated. Who cares? Use a UNIQUEIDENTIFIER (maybe generated in a separate table with an IDENTITY as a surrogate) if the original value is very important.
  • change the archive process to use a "soft delete" where something is marked as archived initially and the archival isn't made permanent until some later date. Then whatever process is trying to move them back can simply perform a direct update and fix the soft delete flag.
Related Question