How does IDENTITY_INSERT affect concurrency?

Question

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.

Answer 1

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.

Answer 2

I know this is an old post, but in this interesting edge case of restoring "archived" records I want to point out that you probably have nothing to worry about. The ID seed in this case presumably lives out in front of where you are doing explicit ID inserts, and will not "revert back" just because you are inserting some older IDs.

I've had a similar scenario when swinging traffic from multiple application servers to a new database server, and need to restore just the traffic that occurred during the transition. Before starting the swing, I use DBCC CHECKIDENT(Table, RESEED, SomeFutureValue) to push the ID seed for the "other" active users out into the future. Once everyone has moved over to that new ID range, then I use IDENTITY_INSERT in my session to insert the "transition activity" records with their old explicit values.

The inserts of explicit "old" ID's behind the current ID seed do not impact the seed nor the other users.

So for example if the seed is set to 100, new inserts will grab ID's 100+. You can at this moment insert an ID "20" with IDENTITY_INSERT and it will not impact the other users' IDs - they will stay in the 100+ range.

You can test this by modifying Aaron's session 2 as follows:

-- session 2
DBCC CHECKIDENT ('dbo.what', RESEED, 100)
go
INSERT dbo.what DEFAULT VALUES;
WAITFOR DELAY '00:00:01';
GO 20

I think this models your scenario: new inserts are happening out on the edge of the ID range and you want to insert some older ID's behind them. Should be fine.

Now if you inserted an explicit ID that was beyond the current seed, that will update the seed and impact future inserts by any user. But that's a different scenario.