Solved

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

Posted on 2010-09-22
11
644 Views
Last Modified: 2012-05-10
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
Comment
Question by:CAMSYSTEMS
  • 6
  • 5
11 Comments
 

Author Comment

by:CAMSYSTEMS
ID: 33735194
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
 
LVL 7

Expert Comment

by:tlovie
ID: 33735427
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
 
LVL 7

Expert Comment

by:tlovie
ID: 33735715
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
 
LVL 7

Expert Comment

by:tlovie
ID: 33735977
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
 

Author Comment

by:CAMSYSTEMS
ID: 33736141
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:CAMSYSTEMS
ID: 33736170
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
 
LVL 7

Expert Comment

by:tlovie
ID: 33739262
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
 

Author Comment

by:CAMSYSTEMS
ID: 33741607
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
 
LVL 7

Expert Comment

by:tlovie
ID: 33744246
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
 

Accepted Solution

by:
CAMSYSTEMS earned 0 total points
ID: 33796848
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
 

Author Closing Comment

by:CAMSYSTEMS
ID: 34228584
Had to implement a lock in c# to prevent code from executing on multiple threads at the same time.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now