SQL Server Performance

Concurrency problem...............

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by samratmail, Aug 9, 2008.

  1. samratmail New Member

    Hello Friend

    I have faced a problem in SQL Server

    I have a stored procedure which works as follows

    Declare @Id int

    Select @id=max(id) from emp
    Set @id=@id+1
    update emp Set id=@id
    return @id

    Now, we call this stored procedure from our ASP.net application. It is working perfecly in single user environment,
    but the problem is that when we are working in multi-user environment some time the procedure returns same id instead of unique id.

    How can I solve it, so that every time the procedure returns unique value for each user.
    Is there any technique to execute this stored procedure in queue.

    Thanks in advance
  2. Adriaan New Member

    That's not so much a concurrency problem as it is a misunderstanding of T-SQL.
    update emp Set id = @id
    This will update the ID column on all rows in table emp.
    T-SQL is not shorthand for moving around resultsets.
  3. satya Moderator

    Is this related to SQL 2008 in any way/
  4. FrankKalis Moderator

    [quote user="satya"]
    Is this related to SQL 2008 in any way/
    Not really. Methinks this is more of a general question. [:)]
  5. preethi Member

    Welcome to the forum!
    The issue is in between your select and update statements, Someone else also could read the Max(ID) and update with row with the same value.You can call the select statement with HoldLock query optimization hint and keep select and Update in one transaction.
    Having them in one stored procedure doesnot mean that it is in one transaction.
    alternatively, You can Set the Transaction Isolation level to Serializable or Repeatable read for this storedprocedure and then move it back. I am not recommending this, unless you have issues with query hints.

    I have a question on the query. Your query reads the Max(ID) from the table and then updates all rows with MaxID+1. Why should you do that? Are you trying to INSERT a row with MaxID+1? If so,Identity property is the best option for it. (you may have some missing entries if your insert fails, but for most of the cases it will work (and also reduce the cost)

Share This Page