• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

SQL Server - Get Next Unique Record for Atomic Insert (Multi-Threaded Application)

Hi All

I am trying to ensure that the next sequential record unique record is found and inserted into an AssignedPattern table. To do this I have three tables.

1. Pattern - Repository for pattern records
2. TempAssignPattern - used to temporarily store the next available record ready for assignment
3. AssignedPattern - used for the currently assigned records.

I have included example code to create the tables and a procedure to run simultanaeously by SQL Server Management Studio.

By doing so it should demonstrate that errors can occur violating the Primary Key for the TempAssignPattern table.

What can I do differently? Can anyone help?


--***************************************************************************
--CREATE TABLES AND INSERT DEFAULT DATA
--***************************************************************************

CREATE TABLE Pattern
(
	PatternCode				varchar(20)		NOT NULL,
	
	CONSTRAINT PK_Pattern_PatternCode PRIMARY KEY (PatternCode)	
)
GO

CREATE TABLE TempAssignPattern
(
	PatternCode				varchar(20)		NOT NULL,
	TempGUID				varchar(100)	NOT NULL,
	
	CONSTRAINT PK_TempAssignPattern_PatternCode PRIMARY KEY (PatternCode)	
)
GO

CREATE TABLE AssignedPattern
(
	PatternCode				varchar(20)		NOT NULL,
	Assigned				datetime		NOT NULL,
	TUID					varchar(100)	NOT NULL,
	
	CONSTRAINT PK_AssignedPattern_PatternCode PRIMARY KEY CLUSTERED 
	(
		PatternCode ASC,
		Assigned ASC
	),
	
	CONSTRAINT UQ_AssignedPattern_PatternCode UNIQUE (PatternCode),
	
	CONSTRAINT FK_AssignedPattern_PatternCode FOREIGN KEY (PatternCode) REFERENCES Pattern(PatternCode)
)
GO
DECLARE @PatternCode varchar(20)
DECLARE @first int
DECLARE @second int
DECLARE @third int
DECLARE @forth int

SET @first = 21
SET @second = 2144
SET @third = 0
SET @forth = 0

while @second <= 2150
begin	
	while @third <= 24	
	begin

		while @forth < 255
		begin
			insert into pattern (patterncode)
			values (
						cast(@first as varchar(10)) + '.' + 
						cast(@second as varchar(10)) + '.' +
						cast(@third as varchar(10)) + '.' +
						cast(@forth as varchar(10))
					)
			set @forth = @forth + 1
		end	
		
		set @forth = 0
		set @third = @third + 1
	end
	
	set @third = 0
	set @forth = 0
	set @second = @second + 1
end	
GO
	 
--***************************************************************************
--***************************************************************************
--***************************************************************************


--***************************************************************************
--PROCEDURE TO RUN - SIMULTANEOUSLY IN TWO SEPARATE QUERY WINDOWS IN MS SQL SERVER MANAGEMENT STUDIO...
--***************************************************************************

DECLARE @counter int

SET @counter = 1

WHILE @counter < 500
BEGIN
	declare @TempGuid varchar(100)
	SET @TempGuid = NewID()

	INSERT INTO TempAssignPattern (
									PatternCode,
									TempGUID
								)
	SELECT TOP 1
				Pattern.PatternCode,
				@TempGUID
	FROM Pattern
	WHERE 
				Pattern.PatternCode NOT IN (SELECT PatternCode FROM TempAssignPattern)
				AND Pattern.PatternCode NOT IN (SELECT PatternCode FROM AssignedPattern with (nolock)) --Do not want to lock the assigned pattern table	
	ORDER BY 
		CAST(PARSENAME(Pattern.patterncode, 4) AS INT),
		CAST(PARSENAME(Pattern.patterncode, 3) AS INT),
		CAST(PARSENAME(Pattern.patterncode, 2) AS INT),
		CAST(PARSENAME(Pattern.patterncode, 1) AS INT)
				
	declare @NextAvailablePattern varchar(20)

	SELECT 
					@NextAvailablePattern = PatternCode
	FROM TempAssignPattern
	WHERE TempGUID = @TempGUID
	
	PRINT @NextAvailablePattern
	
	IF @NextAvailablePattern IS NOT NULL
	BEGIN
		INSERT INTO AssignedPattern (PatternCode, Assigned, TUID)
		VALUES (@NextAvailablePattern, GetDate(), NewID())
	END
	
	SET @Counter = @Counter + 1
	
	DELETE FROM TempAssignPattern
	WHERE TempGUID = @TempGUID
	
END
GO

Open in new window

0
CAMSYSTEMS
Asked:
CAMSYSTEMS
  • 6
  • 5
1 Solution
 
CAMSYSTEMSAuthor Commented:
Errors I receive are both....

Violation of PRIMARY KEY constraint 'PK_TempAssignPattern_PatternCode'. Cannot insert duplicate key in object 'dbo.TempAssignPattern'.
Violation of UNIQUE KEY constraint 'UQ_AssignedPattern_PatternCode'. Cannot insert duplicate key in object 'dbo.AssignedPattern'.
0
 
tlovieCommented:
In your above code put this:

Line 94:  BEGIN TRAN
Line 131: COMMIT TRAN

That will solve the problem for this loop.... by enclosing from the start of the insert to TempAssignPattern to the delete of TempAssignPattern it will ensure that the entire statement blocks until it completes.
0
 
tlovieCommented:
I don't understand why your main loop couldn't be simplified to the below code?  do items sit in the TempAssignPattern table for a long time and you don't want to put them into the AssignPattern table until other tasks are completed?

and your comment "--Do not want to lock the assigned pattern table" doesn't seem to make sense for a database application.... I think it's very dangerous to do this, since if another process was inserting into that table, the read you are doing here would be dirty.  I don't think the select statement would take a lock here, but having the (nolock) hint would allow it to read dirty data.  


DECLARE @counter int

SET @counter = 1

WHILE @counter < 500
BEGIN

      INSERT INTO AssignedPattern (PatternCode, Assigned, TUID)
      SELECT TOP 1
                        Pattern.PatternCode, getdate(),
                        NewID()
      FROM Pattern
      WHERE
                        Pattern.PatternCode NOT IN (SELECT PatternCode FROM AssignedPattern)

      ORDER BY
            CAST(PARSENAME(Pattern.patterncode, 4) AS INT),
            CAST(PARSENAME(Pattern.patterncode, 3) AS INT),
            CAST(PARSENAME(Pattern.patterncode, 2) AS INT),
            CAST(PARSENAME(Pattern.patterncode, 1) AS INT)
      SET @Counter = @Counter + 1
            
END
GO
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tlovieCommented:
Forget my revised solution... it doesn't work when you run 2 simultaneously.  but I did test the one in my first comment, and it appeared to work, but I'm wondering if it's only working since it would take slightly longer to do the additional inserts and deletes to the TempAssignPattern table.
0
 
CAMSYSTEMSAuthor Commented:
Hi tlovie

This is just example code to demonstrate the issue. In production code the procedure only inserts one record at a time but the procedure is being running very frequently within a multi threaded application hence why there is no BEGIN TRAN and COMMIT TRAN etc.
0
 
CAMSYSTEMSAuthor Commented:
Also - my original solution was to have the more simplified version (as per your comment) I put in the TempAssignPattern table to try to rectify the issue.

For example - the plan was to do a simple query upfront to find the next available pattern, holding it so that no one else could get it (in theory) and then a more complex query is actually done in product to insert in to AssignedPattern...

0
 
tlovieCommented:
Here is the latest that I've come up with... It seems to work when running it in 2 windows.  I added TUID field to table Pattern, and I think that the insert should be moved to a trigger on table Pattern.  I have not implemented that here, but should be pretty easy to do.


DECLARE @counter int, @tries tinyint

SET @counter = 1

WHILE @counter < 500
BEGIN
      declare @TempGuid varchar(100)
      SET @TempGuid = NewID()
      
      set @tries=1
      while @tries <= 10

      begin
      begin transaction
      begin try
            update Pattern set TUID=@TempGuid from Pattern  with (rowlock, holdlock) inner join
                  (SELECT a.PatternCode, Rank() over (order by CAST(PARSENAME(a.patterncode, 4) AS INT),
                        CAST(PARSENAME(a.patterncode, 3) AS INT),
                        CAST(PARSENAME(a.patterncode, 2) AS INT),
                        CAST(PARSENAME(a.patterncode, 1) AS INT)) as r
                  FROM Pattern a with (rowlock, holdlock) WHERE TUID is null) q on q.PatternCode=Pattern.PatternCode
                  where r=1 and TUID is null

            -- we're going to move this insert statement into a trigger on Pattern
            INSERT INTO AssignedPattern (PatternCode, Assigned, TUID)
            select PatternCode, getdate(), TUID from Pattern where TUID=@TempGuid

            commit
            break
      end try
      begin catch
            rollback
            set @Tries = @Tries + 1
            continue
      end catch;
      end

      SET @Counter = @Counter + 1
            
END
GO
0
 
CAMSYSTEMSAuthor Commented:
Hi tlovie

Could you just explain why the rowlock and holdlock hints are used and also why Rank is needed instead of Top 1?
0
 
tlovieCommented:
The rank function was just an attempt by me to re-code it so it wouldn't use a subquery, but it didn't work.  It's functionally the same at yours that did SELECT TOP 1

ROWLOCK tells the optimizer to use a row lock rather than a page lock.  Row locks are smaller scope, so I thought it would be a good hint.

HOLDLOCK tells it to hold the lock until the transaction is completed.
0
 
CAMSYSTEMSAuthor Commented:
Although this does actually work I cannot actually used this in production.

The pattern can actually be used multiple times (but only once at a time). Once it has been removed from the AssignedPattern table it is free to be used again.

Hence the part of the where clause of....

AND Pattern.PatternCode NOT IN (SELECT PatternCode FROM AssignedPattern with (nolock))
0
 
CAMSYSTEMSAuthor Commented:
Had to implement a lock in c# to prevent code from executing on multiple threads at the same time.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now