Reseeding results in Duplicate records

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 2008 Forums

Transact-SQL (2008)

Reseeding results in Duplicate records

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-11-18 : 06:27:41

Hi

I have the following code:

	BEGIN TRAN

	--	-- Statement will hold a table lock for the duration of the transaction
		SELECT TOP 1 '1' FROM staging.TransactionIDGenerator WITH (HOLDLOCK, TABLOCKX)
		  
		-- Get the lastID inserted  
		SET @LastID = IDENT_CURRENT('staging.TransactionIDGenerator')+5

		-- Flush the table of data
		TRUNCATE TABLE staging.TransactionIDGenerator
COMMIT TRAN

TransactionIDGenerator consists of 2 columns, an IDENTITY and a datetime. I have multiple threads inserting into this table to get the IDENTITY value. I thought that the first statement would lock the table and prevent duplication, but this is not the case. Therefore, to prevent duplicates, I have to add a value to the result of the function 'IDENT_CURRENT('staging.TransactionIDGenerator')+5'

Is there a reason why I'm still getting duplicates?

Thanks

Hearty head pats

Kristen
Test

22859 Posts

Posted - 2010-11-18 : 06:55:17

Not sure I understand what you are trying to do.

TRUNCATE TABLE will reset the Identity column to Seed-value - if you are relying on Identity, and not expecting that behaviour, that may be an issue.

But I don't really see how you are incrementing the staging.TransactionIDGenerator such that its IDENTITY column would be incremented

IDENT_CURRENT will just get the most recent value that has been allocated - for any session / scope.

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-11-18 : 09:36:20

Hi Kristen

Thanks for the reply.

Sorry, I didn't include all the code stupidly....

	BEGIN TRAN

	--	-- Statement will hold a table lock for the duration of the transaction
		SELECT TOP 1 '1' FROM staging.TransactionIDGenerator WITH (HOLDLOCK, TABLOCKX)
		
		UPDATE admin.StagingTableConfiguration
		SET     IsDataLoadSchema      = IsDataTransferSchema
			  , IsDataTransferSchema  = IsDataLoadSchema
		  
		-- Get the lastID inserted  
		SET @LastID = IDENT_CURRENT('staging.TransactionIDGenerator')+5

		-- Flush the table of data
		TRUNCATE TABLE staging.TransactionIDGenerator

		-- Reseed to 1 if threshold has been reached, otherwise	reseed to previous value+1		
		IF (@LastID > 99900000000)
			BEGIN
				DBCC CHECKIDENT ('staging.TransactionIDGenerator',RESEED, 1)
			END
		ELSE 
			BEGIN
				DBCC CHECKIDENT ('staging.TransactionIDGenerator',RESEED, @LastID)
			END

	COMMIT TRAN

I have a staging database that consists of 2 sets of tables identical in structure. We load data into one set of tables, and in the other set of tables, we update fact data, and then export the data into a repository database. Once complete, the tables are TRUNCATED and then the load process will begin to load data into this set of tables whilst we process the other set. This switch method is continual and is managed/performed by a SQL agent job.

Although I have 2 sets of tables, I need to have a TransactionID attribute that is unique within both sets of tables. for this purpose, I have the TransactionIDGenerator table to produce a bigint IDENTITY value. Before inserting into the specific staging set of tables, we insert into here to get a unique value.

This table is also used to check for duplicate values that can occur on the natural key (StoreNo, TillNo, ReceiptNo, TransactionDateTime). This was originally controlled using a UNIQUE constraint on the header tables in both staging sets of tables. This resulted in deadlocks as soon as more than 1 thread loaded data into the tables. Therefore, we now do an existence check before inserting into TransactionIDGenerator table.

We only need to prevent duplicates when inserting a batch into the staging load set of tables. Once the load becomes export and export becomes load, we don't have to worry about duplicates.

We can get over 8million transactions a day, which would make the existance check take longer as the day progresses, as well as the insert into the Index on the TransactionIDGenerator table on the natural key. So I truncate the table after each switch (switch occurs each time data is exported from the staging set of tables that is acting as 'export' whislt data is loaded into the 'load' set of staging tables - this is done every 15 seconds by the sql agent job).

However, when I execute the code above as a step in the job, I was expecting to hold an exclusive lock on the TransactionIDGenerator table, so that no Thread could insert into it. I then reseed the value back to the last inserted value. However, at some point between locking the table and reseeding it to the last value, a transaction is being inserted and I am getting duplicate TransactionIDs.

So when I obtain a lock, are there circumstances where it is not exclusive, hence allowing an insert into the table??

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources