CODEWITHSUNDEEP
sql-servertransactionslocking
Shahar Mosek
Shahar Mosek

Reputation: 1982

How to synchronize between transactions

I have the following stored procedure. It is meant to get a name. If the name exists in a table it returns its corresponding ID. If it doesn't exist yet, it creates a new ID, and adds it to the table with the new ID.

The problem is that when I run this on multiple threads which use the same name (not yet in the table) I sometimes get the name entered several times, with subsequent IDs.

What I would like is for the transactions to block while another transaction is running, so that the name only gets in the database once.

Is there an isolation level or select hint I should be using? Is there another way to do this?

CREATE PROCEDURE [dbo].[sp_createID] 
@name varchar(max)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN
    DECLARE @id as int
    SELECT @id=Id FROM MyTbl WHERE name=@name
    IF @id IS NULL
    BEGIN
        UPDATE idGeneratorTbl SET lastkey=lastkey+1
        SELECT @id=lastKey FROM idGeneratorTbl
        INSERT INTO MyTbl VALUES(@id, @name)
    END
    SELECT @id
COMMIT          
END

Upvotes: 2

Views: 1353

Answers (1)

Diego
Diego

Reputation: 36176

Both of the procs should be on the same transaction to achieve what you want.

You can achieve that by removing the transaction control in the [sp_generateNewId] proc.

FYI, you don’t need to worry about keys generation, why don’t you just create an IDENTITY field

Upvotes: 1

Related Questions