How to write thread safe SQL Server stored procedures

Question

I have some stored procedures in which multiple queries are being executed. To get last identity of insert I am using IDENT_CURRENT which is causuing problem.

My question is can I have lock statements like C# in T-SQL so that it can be thread safe?

EDIT: Code I am using

INSERT INTO activities
    (creator
    ,title
    ,description)
VALUES
    (@Creator
    ,@Tile
    ,@Description)

SET @ActivityId = IDENT_CURRENT('dbo.activities');

INSERT INTO [dbo].activity_cfs
    ([activity_id],
    [cf_id],
    [cf_field_name],
    [field_key],
    [value])
SELECT 
    @ActivityId,
    cf_id,
    cf_field_name,
    field_key,
    value
FROM @ActivityCustomFields

@ActivityCustomFields is my temp table.

Some code please. We can not assume that you have written correct code :-) — Amit, Aug 26 '15 at 06:05

score 5 · Accepted Answer · edited May 23 '17 at 11:58

It is quite likely that you should use SCOPE_IDENTITY instead of IDENT_CURRENT. There are many explanations how they differ, for example: What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current?

But, if you really need to guarantee that certain part of the code is not being run by several threads at the same time, you can use sp_getapplock.

Based on the code that you added to the question I'm now pretty sure that you should simply use SCOPE_IDENTITY, like this:

INSERT INTO activities
    (creator
    ,title
    ,description)
VALUES
    (@Creator
    ,@Tile
    ,@Description);

SET @ActivityId = SCOPE_IDENTITY();

INSERT INTO [dbo].activity_cfs
    ([activity_id],
    [cf_id],
    [cf_field_name],
    [field_key],
    [value])
SELECT 
    @ActivityId,
    cf_id,
    cf_field_name,
    field_key,
    value
FROM @ActivityCustomFields;

The SCOPE_IDENTITY function returns the last identity created in the same session and the same scope. The IDENT_CURRENT returns the last identity created for a specific table or view in any session. So, if you have several threads that run this procedure simultaneously IDENT_CURRENT will see identities generated in other threads, which you don't want.

score 1 · Answer 2 · answered Aug 27 '15 at 06:54

SCOPE_IDENTITY would be the way to go in my understanding. Please follow following link that in SQL Server when using SCOPE_IDENTITY will always be threadsafe:

http://www.vbforums.com/showthread.php?727047-RESOLVED-Is-MSSQL-s-SCOPE_IDENTITY()-thread-safe

How to write thread safe SQL Server stored procedures

2 Answers2