How to lock a table - 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 2005 Forums

Transact-SQL (2005)

How to lock a table

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-09-23 : 07:52:10

I have a table:

CREATE TABLE staging.GetIdentity (ID BIGINT IDENTITY)

Several threads will be inserting into this table to get an ID:

(Thanks to PESO for this code)
INSERT INTO staging.GetIdentity
DEFAULT VALUES

SET @TransactionID = SCOPE_IDENTITY()

However, i have a nightly job that clears the table and reseeds the IDENTITY:

DECLARE @LastID INT

SET @LastID = IDENT_CURRENT('staging.GetIdentity')
TRUNCATE TABLE staging.GetIdentity
DBCC CHECKIDENT ('staging.GetIdentity',RESEED, @LastID)

However, how can I prevent an insert occurring from when I get the last ID, to the truncate statement? Therefore, if I reseed the value, I will have an issue with duplication.

Several processes may be accessing the table, and each process wraps a batch within a transaction.

So I need do something on the lines of:

BEGIN TRAN
DECLARE @LastID INT

   CODE TO LOCK ALL ACCESS TO 'staging.GetIdentity' TABLE   SET @LastID = IDENT_CURRENT('staging.GetIdentity')
   TRUNCATE TABLE staging.GetIdentity
   DBCC CHECKIDENT ('staging.GetIdentity',RESEED, @LastID)
COMMIT TRAN

I could do a DELETE FROM, but the difference in time is significant:
truncate - 133 ms
delete - 97653 ms

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-09-23 : 08:23:15

No worries, I have a solution. Bit of a cheat, but does the job:

BEGIN TRAN

-- Statement will hold a table lock for the duration of the transaction
SELECT TOP 1 '1' FROM staging.GetIdentity WITH (HOLDLOCK, TABLOCKX)

-- Get the lastID inserted  
SET @LastID = IDENT_CURRENT('staging.GetIdentity')

TRUNCATE TABLE staging.GetIdentity
			
IF (@LastID > 99999000000)
	BEGIN
		DBCC CHECKIDENT ('staging.GetIdentity',RESEED, 1)
	END
ELSE 
	BEGIN
		DBCC CHECKIDENT ('staging.GetIdentity',RESEED, @LastID)
	END

COMMIT TRAN

Hearty head pats

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-23 : 08:59:33

If you feel like it and have the time feel free to explain why you need to do it this way...I find this solution to be...well...interesting

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-23 : 09:37:07

DELETE * FROM staging.GetIdentity

keeps your identity intact, whereas truncate will reseed.

N 56°04'39.26"
E 12°55'05.63"

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-09-23 : 09:37:49

Hey Lumbago

I'd be happy to do so...... but it may take some time. I'll try and write something up tonight that is to the point as possible!!

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-23 : 10:02:59

One more thing. Declare @LastID as BIGINT since column is BIGINT.

DECLARE @LastID BIGINT

SET	@LastID = IDENT_CURRENT('staging.GetIdentity')

WHILE @@ROWCOUNT > 0
	DELETE	TOP (100000)
	FROM	staging.GetIdentity
	WHERE	ID < @LastID

N 56°04'39.26"
E 12°55'05.63"

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-09-23 : 10:32:34

well spotted.

I did get a casting error whilst testing this!!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources