locked
Sql Statement LOCK RRS feed

  • Question

  • Is there any way to Lock a table for two SQL statements. I understand that there is TABLOCK/ROWLOCK or Transaction to lock a table. But my requirement is different. I have to select the Max(ID) from a table & do some custom coding & then insert this ID in the same table. I have to make sure that more than one user cannot access the table until I finish the SQL Insert statement. Pseudo code would be –

     

    Declare @TempID as int

    START LOCK

                Select @TempID  = Max(ID) from T1

                ….do something with the @TempID

                Insert into T1(ID,Name,Age) values(@TempID,@Name, @Age)

    END LOCK

     

    If you notice carefully, I need to lock the Select statement as well as Insert statement at the same time, basically block of statement. If I use just Transaction/TABLOCK, it will lock for only one statement. But I need to lock for both Select & Insert. At the end of the Insert, 2nd user will have access to the table so that he will get a different max value.

     

    Is there any way to achieve this in SQL Server?

     

    Thanks in Advance

    Bhaskar

    Friday, July 20, 2007 5:39 AM

Answers

  • Yes we have it on SQL Server...

    Note:

    If the more than one user executes the same statements (or sP) as below (ie, same lock name), the first user will be successfully lock the statement and others will be put on hold to complete the first users execution.

    It wont lock the objects which you are accessing inside the lock, it only searlize the request in the queue when users exectes the statement (or Sp)

     

    Code Snippet

    Declare @TempID as int

    EXEC @result = sp_getapplock @Resource = 'Your Lock Name',  @LockMode = 'Exclusive'

     

                Select @TempID  = Max(ID) from T1

               

                .do something with the @TempID

               

                Insert into T1(ID,Name,Age) values(@TempID,@Name, @Age)

     

    EXEC @result = sp_releaseapplock @Resource = 'Your Lock Name'

     

     

    Friday, July 20, 2007 5:47 AM