using Lock in Stored Procedure SQL Server 2005

Question

What I am looking to avoid concurrency in my stored procedure

here is my script, I am trying SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

ALTER proc [dbo].[SP_GenerateNextReportID]
  @type nvarchar(255), @identity int output
   AS BEGIN
         SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    declare @id int;
    set @id =  IsNull((select LastUsedIdentity from   ReportProcessGenerator       where Type = @type), 0)
    if(@id =0)
      insert ReportProcessGenerator values(@type, @id +1)
     else
        update ReportProcessGenerator set LastUsedIdentity = @id +1 where Type = @type

    set @identity = @id +1
  END

Not sure is this a right way or not?

I think this will be prone to deadlocks. There is nothing stopping two concurrent transactions both doing the `SELECT` then blocking each other from going any further. There's a couple of approaches here http://stackoverflow.com/questions/3453411/sql-server-auto-incrementation-that-allows-update-statements/3462957#3462957 — Martin Smith, May 10 '11 at 12:10

Quassnoi · Answer 1 · 2011-05-10T12:30:41.207

If you have a UNIQUE index or a PRIMARY KEY on ReportProcessGenerator.Type, then your stored procedures will not be able to modify the record for the same type concurrently.

Note that you should use SELECT FOR UPDATE or OUTPUT clause to avoid deadlocks, as @Martin points out. With SERIALIZABLE, concurrent SELECT queries won't lift the shared locks which the UPDATE queries will not later be able to upgrade.

However, why would you want to maintain separate per-type identities? Usually, one identity which is unique across types is as good as multiple ones unique within types, the former being much easier to maintain.

using Lock in Stored Procedure SQL Server 2005

1 Answers1