SQL Stored Procedure Simultaneously Call Issue

Question

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)

This type of "roll your own" identity replacement is an anti-pattern. It is fraught with errors. And why oh why are doing math on this value and then storing it in nvarchar(10)??? You should fix your schema. Change the datatype of your column to an int and use the identity property. Anything else is going to run into issues at some point. — Sean Lange, May 09 '17 at 21:37
thanks all. but this is a sample code not the actual code. the actual code is varcharchar(10) because the no is something like this no-year example 123-2017 and the 123 is the incremental no that i generate it and it not depends on the primary key. — Mohammad Sa'ed Naboti, May 09 '17 at 21:41
@KannanKandasamy as i mentioned before the no is calculated depends on many conditions and types, cannot be an identity — Mohammad Sa'ed Naboti, May 09 '17 at 22:06

JBdev · Accepted Answer · 2017-05-15T13:36:57.333

1

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)

edited May 15 '17 at 13:36

answered May 09 '17 at 21:37

JBdev

353
1
10

I think it is worth noting though that this looks like an issue that should be fixed by an identity column as part of the primary key. – JBdev May 09 '17 at 21:37
2

thanks all. but this is a sample code not the actual code. the actual code is varcharchar(10) because the no is something like this no-year example 123-2017 and the 123 is the incremental no that i generate it and it not depends on the primary key. – Mohammad Sa'ed Naboti May 09 '17 at 21:41
dear JBdev, just i need to add the BEGIN TRANSACTION and the COMMIT TRANSACTION ?? is this will do the table lock ?? – Mohammad Sa'ed Naboti May 09 '17 at 21:42
No , actually you need a Pk (identity) – Ilyes May 09 '17 at 21:43
@MohammadSa'edNaboti Think that I say Identity – Ilyes May 09 '17 at 21:44
The key is the WITH (TABLOCKX, HOLDLOCK) line after your from table "SELECT TOP 1 MyNo FROM Employee WITH (TABLOCKX, HOLDLOCK)" See this for more information on tablock vs tablockx: http://stackoverflow.com/questions/5102152/tablock-vs-tablockx – JBdev May 09 '17 at 21:46
@JBdev, thank you, is the WITH (TABLOCKX, HOLDLOCK) needed on the insert statement ? – Mohammad Sa'ed Naboti May 09 '17 at 21:50
Note that (UPDLOCK,HOLDLOCK) is less restrictive and, but is sufficient to prevent duplicates. Note also that HOLDLOCK (aka SERIALIZABLE) does not affect the lock duration here. It is used to force range locks in case the query returns no rows. – David Browne - Microsoft May 09 '17 at 21:58
@DavidBrowne so in my case what do you think it's better ?? (TABLOCKX, HOLDLOCK) or (UPDLOCK,HOLDLOCK) – Mohammad Sa'ed Naboti May 09 '17 at 22:02
1

Do you want to lock the whole table or just ensure that you don't generate duplicates? – David Browne - Microsoft May 09 '17 at 22:39
@DavidBrowne-Microsoft i need only ensure that no duplicate will be generated. – Mohammad Sa'ed Naboti May 13 '17 at 12:00
@MohammadSa'edNaboti then (UPDLOCK,HOLDLOCK) should be enough. – David Browne - Microsoft May 13 '17 at 12:57

score 1 · Answer 2 · answered May 09 '17 at 21:48

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.

SQL Stored Procedure Simultaneously Call Issue

2 Answers2