Allocate Identity-ID range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Allocate Identity-ID range

Author

Topic

Cine
Starting Member

2 Posts

Posted - 2006-07-28 : 06:47:24

Hi,

I have a problem that I want to allocate a range of IDs for use in import.

What we currently do is:
1 begin transaction
2 DECLARE @seed BIGINT
3 SET @seed = IDENT_CURRENT('resource') + 1 + 1
4 DBCC CHECKIDENT('resource', RESEED, @seed)
5 COMMIT

problem is that sometimes between 3 and 4 another thread inserts a record and then the import will fail, because it will insert a record that already exists.

The problem is that IDENT_CURRENT only gets a sch-s lock on the table and DBCC CHECKIDENT obtains a SCH-M lock. Is there any possible way to force a SCH-M lock?

Or a completly other way of doing this?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-28 : 09:55:12

Change ALL processes that insert records...to check an external flag to see if an 'import' is in progress - flag set "no other insert allowed". And in the import process...set and un-set the flag at the start and end.

Alternatively, put the DB into single-user mode...drastic....but a solution.

Cine
Starting Member

2 Posts

Posted - 2006-07-31 : 04:36:01

Actually I found a solution which seems to work.

1 begin transaction
2 DECLARE @seed BIGINT
3 SELECT top 1 @seed = ID FROM resource WITH (TABLOCKX)
4 SET @seed = IDENT_CURRENT('resource') + 1 + 1
5 SET IDENTITY INSERT resource ON
6 INSERT INTO resource (id, xxx) values (@seed, yyy)
7 SELECT @@IDENTITY
8 DELETE FROM resource WHERE ID = @@IDENTITY
9 COMMIT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources