title: Lock Stored Procedure in SQL
body: <p>I have a stored procedure which inserts info into multiple tables and gets the IDs by <code>SCOPE_IDENTITY()</code>. I would like to prevent multiple users from executing it at the same time, so that my IDs don't get mixed up. </p>
  
  <p>How do I lock it? I have read about <code>sp_getapplock</code> and <code>sp_releaselock</code>, but there is no clear explanation how to use it. Below I put my procedure.</p>
  
  <pre><code>create procedure AddPerson(
      @Name nvarchar(255), 
      @LastName nvarchar(255), 
      @City nvarchar(255), 
      @Address nvarchar(255)
  )
  as
  BEGIN TRY
      BEGIN TRANSACTION
         insert into Location(Address, City)
         values(@Address, @City)
  
         declare @LocationID int
         set @LocationID = SCOPE_IDENTITY()
  
         insert into PersonalInfo(Name, LastName)
         values (@Name, @LastName)
  
         declare @PersonInfoID int
         set @PersonInfoID = SCOPE_IDENTITY()
  
         insert into Teacher
         values(@LocationID, @PersonInfoID)
      COMMIT
  END TRY 
  BEGIN CATCH
      ROLLBACK
  END CATCH
  </code></pre>