CODEWITHSUNDEEP
sqlsql-servert-sql
Steve Temple
Steve Temple

Reputation: 2278

MS SQL locking for update

I am implementing a competition where there might be a lot of simultaneous entries. I am collecting some user data, which I am putting in one table called entries. I have another table of pre-generated unique discount codes in a table called discountCodes. I am then assigning one to each entry. I thought I would do this by putting a entry id in the discountCodes table.

As there may be a lot of concurrent users I think I should select the first unassigned row and then assign the entry id to that row. I need to make sure between picking an unassigned row and adding the entry id that another thread doesn't find the same row.

What is the best way of ensuring that the row doesn't get assigned twice?

Upvotes: 3

Views: 243

Answers (5)

Jason
Jason

Reputation: 3960

You can try and use sp_getapplock and synchronize the write operation, just make sure it locks against the same hash, like

DECLARE @state    Int

BEGIN TRAN

-- here we're using 1 sec's as time out, but you should determine what the min value is for your instance
EXEC @state = sp_getapplock 'SyncIt', 'Exclusive', 'Transaction', 1000

-- do insert/update/etc...

-- if you like you can be a little verbose and explicit, otherwise line below shouldn't be needed
EXEC sp_releaseapplock 'SyncIt', 'Transaction'

COMMIT TRAN

Upvotes: 0

dpw
dpw

Reputation: 1586

I would just put an IDENTITY field on each table and let the corresponding entry match the corresponding discountCode - i.e. if you have a thousand discountCodes up front, your identity column in the discountCodes table will range from 1 to 1000. That will match your first 1 to 1000 entries. If you get more than 1000 entries, just add one discountCode per additional entry.

That way SQL Server handles all the problematic "get the next number in the sequence" logic for you.

Upvotes: 0

huMpty duMpty
huMpty duMpty

Reputation: 14470

Something can be done like

The following example sets the TRANSACTION ISOLATION LEVEL for the session. For each Transact-SQL statement that follows, SQL Server holds all of the shared locks until the end of the transaction. Source:MSDN

USE databaseName;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT * 
    FROM Table1;
GO
SELECT * 
    FROM Table2;
GO
COMMIT TRANSACTION;
GO

Read more SET TRANSACTION ISOLATION LEVEL

Upvotes: 1

Khan
Khan

Reputation: 18162

I would recommend building a bridge table instead of having the EntryId in the DiscountCodes table with an EntryId and a DiscountCodeId. Place a Unique Constraint on both of those fields.

This way your entry point will encounter a constraint violation when it tries to enter a duplicate.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425803

WITH    e AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY id) rn
        FROM    entries ei
        WHERE   NOT EXISTS
                (
                SELECT  NULL
                FROM    discountCodes dci
                WHERE   dci.entryId = ei.id
                )
        ),
        dc AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY id) rn
        FROM    discountCodes
        WHERE   entryId IS NULL
        )
UPDATE  dc
SET     dc.entryId = e.id
FROM    e
JOIN    dc
ON      dc.rn = e.rn

Upvotes: 0

Related Questions