CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Mohammad Sa'ed Naboti
Mohammad Sa'ed Naboti

Reputation: 63

SQL Stored Procedure Simultaneously Call Issue

I have stored procedure in the sql server 2008, my stored procedure calculate and get the last number "not primary key" from column from table B and add one ( +1 ) to this number to use it on the next statement on the same stored procedure.

My issue that i have a duplicate number some times, i think this happened when multiple users call the stored procedure on the same time. is this the issue and how can i solve it

my code is like the below:- 

DECLARE @ID AS NVARCHAR(10)
SET @ID =   (
                SELECT TOP 1 MyNo
                FROM Employee
                WHERE (
                            (TypeID = @TypeID) AND
                            (Year = @Year)
                      )
                ORDER BY ID DESC
            )

SET @ID = ISNULL(@ID,0) + 1


INSERT INTO Employee (name,lname,MyNo) VALUES (@name,@lname,@MyNo)

Upvotes: 1

Views: 1701

Answers (2)

JBdev
JBdev

Reputation: 363

You can lock a table for the duration of a transaction with the WITH (TABLOCKX, HOLDLOCK) syntax:

BEGIN TRANSACTION
DECLARE @ID AS NVARCHAR(10)
SET @ID =   (
                SELECT TOP 1 MyNo
                FROM Employee WITH (TABLOCKX, HOLDLOCK)
                WHERE (
                            (TypeID = @TypeID) AND
                            (Year = @Year)
                      )
                ORDER BY ID DESC
            )

SET @ID = ISNULL(@ID,0) + 1


INSERT INTO Employee (name,lname,MyNo) VALUES (@name,@lname,@MyNo)
COMMIT TRANSACTION

You can find more information about TABLOCK and TABLOCKX here: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

Per discussion, the best lock to use in this case would be:

(UPDLOCK,HOLDLOCK)

Upvotes: 1

Manvendra Gupta
Manvendra Gupta

Reputation: 416

If you cannot use Identity column or the Table lock, another alternative is to use sp_getapplock

The advantage with this mechanism is that this kind of lock can be used across multiple stored procedures that should not run concurrently or for operations that span multiple tables. It also allows for handling timeout and other kinds of behavior if the lock is not available.

You have to be careful when using this feature and ensure you acquire and release locks properly or you will create more problems than you solve.

Upvotes: 1

Related Questions