What locking can I expect with DBCC CHECKIDENT(RESEED)

Question

I'm working on a project that has hit a bit of a snag: I'm replacing some replication with a home built system so that if replication goes down we don't blow our SLAs with our customers and shell out hundreds of thousands of dollars.

In my research, I learned that inserting records alongside identity values can cause the next identity for the table to be reset, which is something I want to avoid due to key range management issues.

It then seems like the obvious solution would be something like this:

DECLARE @Identity VARCHAR(15), @SQL VARCHAR(MAX)
SELECT @Identity = IDENT_CURRENT( 'dbo.My_Table' )

SET IDENTITY_INSERT dbo.My_Table ON
-- Do my inserts here

SET @SQL = 'DBCC CHECKIDENT ( ''dbo.My_Table'', RESEED,' +  @Identity + ')'
EXEC (@SQL)

Would I be killing myself with locking if this happens on a highly used table? Alternatively, has anyone figured out a way to insert identities like replication does without causing the identity value to get reset?

Answer 1

Its always tricky when you try to replicate Identity columns.

You should set NOT FOR REPLICATION option on the identity column when you replicate identity field column.

This way only user inserts cause the value to be incremented at the subscriber/s.

You can even assign chunks of identity values and have them self maintained at subscribers. WHen doing this, you have to be careful as

During a large batch insert the replication trigger is fired only once, not for each row of the insert. This can lead to a failure of the insert statement if an identity range is exhausted during an large insert, such as an INSERT INTO statement.

You can see what types of locks are generated using sys.dm_tran_locks DMV. Doing a reseed is very instantaneous and is not a big overhead. You just have to thoroughly test your idea for all possible cases to avoid any surprises.

Also, out of curiosity, how is your solution more efficient than T-Rep ? As T-Rep is tested since many many years to be more efficient than any custom/home grown solution.

Answer 2

I wrote own replication system for SQL Server and it works for 3 years. It has ~70-90 subscribers, it's faster then SQL merge replication (for my specific applications), but it has some problem with locks.
To make a long story short:
– I have own range mechanism for identity column (every subscriber has some range)
– I have some constraint for inserting rows (to make sure that inserting rows is from specyfic range)
– I put rows into table by: identity insert on and then dbcc reseed.

it wasn't as easy as it looked in the beginning, but it works ok.

dbcc reseed needs sch-m lock, so it's the biggest disadvantege of the replication mechanism. Sometimes locks wait for long transaction...