CODEWITHSUNDEEP
sqlsql-server
user979331
user979331

Reputation: 11841

SQL Prevent multiple inserts at the same time for non-duplicates

I am trying to prevent multiple inserts at the exact same time so I can prevent duplicate inserts. I have two tables:

  1. Table B, this table has 4 columns id, timeToken, tokenOrder and taken.

  2. Table A which I will be inserting into and that has id, createDate and timeToken.

What I am trying to do is prevent the timeToken in Table A not to have duplicate values in the case where multiple inserts are happening at the exact same time. I have the following code:

DECLARE @ReturnValue nvarchar

SELECT Top 1 @ReturnValue=timeToken FROM TableB WHERE taken = 0 Order By tokenOrder

Update TableB SET taken = 1 WHERE timeToken = @ReturnValue

INSERT INTO TableA Values(@ReturnValue, GETDATE())

Now that I think about it, is it possible to have my timeToken table in TableA auto increment with the timeToken from TableB?

Table B sample data:

id  timeToken   tokenOrder taken
1   1:00am      1          0
2   2:00am      2          0
3   3:00am      3          1
4   4:00am      4          0
5   5:00am      5          0

This is what I am expecting Table A to look like after 4 calls all at the exact same time that would cause duplicates (id starting at 5 - this could be because I have deleted old records).

Table A sample data:

id     createDate                 timeToken
5      2014-11-22 12:45:34.243    1:00am
6      2014-11-22 12:45:34.243    2:00am
7      2014-11-22 12:45:34.243    4:00am
8      2014-11-22 12:45:34.243    5:00am

Upvotes: 2

Views: 3812

Answers (2)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

Try to rewrite like this, this should ensure that you do not get the row with taken=0 in TableB updated twice.

BEGIN TRANSACTION    
    DECLARE @taken table(  
        id int NOT NULL,  
        timeToken nvarchar(max));  

    Update TOP (1) TableB 
       SET taken = 1 
       OUTPUT UPDATED.id, UPDATED.timeToken
       INTO @taken  
       WHERE timeToken = 
       (SELECT Top 1 timeToken FROM TableB WHERE taken = 0 Order By tokenOrder)

    INSERT INTO TableA 
    SELECT id, GETDATE(), timeToken
    FROM @taken    
COMMIT TRANSACTION

See SQL Server isolation levels - read commited. READ COMMITTED is the default isolation level for the Microsoft SQL Server Database Engine.

In the example I copy id from TableB to TableA, but it is not probably required.

Upvotes: 1

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

I think you can solve this problem in two steps:

Step1: Buffer all requests as soon as they arrive.

Step2: Periodically assign free tokens to the buffered requests.

Preparation

A sequence object will help resolve any order ambiguity:

CREATE SEQUENCE dbo.Taken_Seq  
    START WITH 1  
    INCREMENT BY 1 ;  
GO

An auxiliary table will play the role of the buffer:

CREATE TABLE buffer (
    requester uniqueidentifier, createdate datetime, seq_value bigint, id int);

I will also use a GUID to refer to the different processes asking for a token (requesters):

ALTER TABLE TableA add Requester uniqueidentifier;

Solution outline

As soon as a request comes (identified by a GUID) buffer it consuming the next sequence value, like this (here I use newid() to get a GUID, your application should have already assigned one to your request):

declare @seq bigint;

SELECT @seq = NEXT VALUE FOR dbo.Taken_Seq;
insert buffer values (newid(), getdate(), @seq, null);

Suppose now that three such requests arrive simultaneously, as in:

declare @seq bigint;

SELECT @seq = NEXT VALUE FOR dbo.Taken_Seq;
insert buffer values (newid(), getdate(), @seq, null);

SELECT @seq = NEXT VALUE FOR dbo.Taken_Seq;
insert buffer values (newid(), getdate(), @seq, null);

SELECT @seq = NEXT VALUE FOR dbo.Taken_Seq;
insert buffer values (newid(), getdate(), @seq, null);

The contents of the buffer table will then look like this:

requester                            createdate              seq_value            id
------------------------------------ ----------------------- -------------------- -----------
109B560C-155C-40BD-A13A-59D21EBEB1F8 2017-04-05 11:17:35.127 31                   NULL
FAC00C2E-14AA-4502-AB5C-DDD756914653 2017-04-05 11:17:35.127 32                   NULL
E95889C3-E291-4A1C-A7E8-0B8CC53D4D7B 2017-04-05 11:17:35.127 33                   NULL

Next we can match each buffered request to a token. This will be done by assinging an id value to each request in our buffered table:

; with a as 
(select rn =row_number() over (order by seq_value), *
from buffer 
where id is null), 
b as
(
select rn=row_number() over (order by tokenOrder), *
from TableB 
where taken = 0
)
update buffer set buffer.id = b.id 
from buffer 
join a on buffer.requester = a.requester 
join b on a.rn = b.rn

This is now how our buffer table looks like:

requester                            createdate              seq_value            id
------------------------------------ ----------------------- -------------------- -----------
109B560C-155C-40BD-A13A-59D21EBEB1F8 2017-04-05 11:17:35.127 31                   1
FAC00C2E-14AA-4502-AB5C-DDD756914653 2017-04-05 11:17:35.127 32                   2
E95889C3-E291-4A1C-A7E8-0B8CC53D4D7B 2017-04-05 11:17:35.127 33                   3

Join the buffer table with TableB to find the tokens:

select buffer.requester, tableB.* from buffer join tableB on buffer.id= tableB.id

Mark the tokens as taken:

update TableB set taken = 1 from buffer where buffer.id = TableB.id

Finally, insert into TableA:

insert TableA (requester, createdate, timeToken)
select buffer.requester, buffer.createdate, TableB.timeToken 
from buffer join TableB on buffer.id = TableB.id

Note: Obviously some of these steps must be contained within a single transaction

Upvotes: 0

Related Questions