SQL Server - How to Lock a Table Until a Stored Procedure Finishes

How to lock a SELECted rows in Sql Server until transaction is COMMITTED in a Stored Procedure

Your code should work fine.

Suppose, you are selecting rows with UPDLOCK/XLOCK.

First Transaction

BEGIN TRAN
SELECT ID FROM EMPLOYEES WITH(UPDLOCK, XLOCK) WHERE CITY='ISTANBUL'
--COMMIT TRAN (Stopping commit to keep the lock running)

Now try run following in another window.

Second Transaction

BEGIN TRAN
SELECT ID FROM EMPLOYEES WITH(UPDLOCK, XLOCK) WHERE CITY='ISTANBUL'
COMMIT TRAN

Your second transaction will not be able to select until you commit your first transaction. Because multiple UPDLOCK or XLOCK can't be applied together on a resource.

Now, if you read rows without locking with uncommitted first transaction then second transaction will not be prevented by the first.

Second Transaction

BEGIN TRAN
SELECT ID FROM EMPLOYEES WHERE CITY='ISTANBUL'
COMMIT TRAN

Because in second transaction no lock applied so it will not be prevented by first transactions UPDLOCK or XLOCK.

Now to prevent any read with another read you need to change your ISOLATION LEVEL TO SERIALIZABLE.

First Transaction

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
SELECT ID FROM EMPLOYEES WHERE CITY='ISTANBUL'
--COMMIT TRAN (Stopping commit to keep the lock running)

Second Transaction

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
SELECT ID FROM EMPLOYEES WHERE CITY='ISTANBUL'
COMMIT TRAN

Now second transaction will be blocked by first transaction. Though no lock applied during read but in transaction isolation level SERIALIZABLE a read transaction will block read of another transaction over same resource.

Now If you select with NOLOCK then there is no transaction lock or isolation level exists to block you.

Hope these helps :)

SQLServer lock table during stored procedure

You guys have between you answered my question. I'm putting in my own reply to collate the working solution I've got into one post. The key seems to have been the transaction approach, with locking hints on the last_auto_id table. Setting the transaction isolation to serializable seemed to create deadlock problems.

Here's what I've got (edited to show the full code so hopefully I can get some further answers...):

DECLARE @Pointer AS INT

BEGIN TRANSACTION

-- Check what the next ID to use should be
SELECT @NextId = LastId + 1 FROM Last_Auto_Id WITH (TABLOCKX) WHERE Name = 'CustomerNo'

-- Now check if this next ID already exists in the database
IF EXISTS (SELECT CustomerNo FROM Customer
           WHERE ISNUMERIC(CustomerNo) = 1 AND CustomerNo = @NextId)
BEGIN
  -- The next ID already exists - we need to find the next lowest free ID
  CREATE TABLE #idtbl ( IdNo int )

  -- Into temp table, grab all numeric IDs higher than the current next ID
  INSERT INTO #idtbl
  SELECT CAST(CustomerNo AS INT) FROM Customer
  WHERE ISNUMERIC(CustomerNo) = 1 AND CustomerNo >= @NextId
  ORDER BY CAST(CustomerNo AS INT)

  -- Join the table with itself, based on the right hand side of the join
  -- being equal to the ID on the left hand side + 1.  We're looking for
  -- the lowest record where the right hand side is NULL (i.e. the ID is
  -- unused)
  SELECT @Pointer = MIN( t1.IdNo ) + 1 FROM #idtbl t1
  LEFT OUTER JOIN #idtbl t2 ON t1.IdNo + 1 = t2.IdNo
  WHERE t2.IdNo IS NULL
END

UPDATE Last_Auto_Id SET LastId = @NextId WHERE Name = 'CustomerNo'

COMMIT TRANSACTION

SELECT @NextId

This takes out an exclusive table lock at the start of the transaction, which then successfully queues up any further requests until after this request has updated the table and committed it's transaction.

I've written a bit of C code to hammer it with concurrent requests from half a dozen sessions and it's working perfectly.

However, I do have one worry which is the term locking 'hints' - does anyone know if SQLServer treats this as a definite instruction or just a hint (i.e. maybe it won't always obey it??)

How can I set a lock inside a stored procedure?

This is a valid opportunity to use an Application Lock (see sp_getapplock and sp_releaseapplock) as it is a lock taken out on a concept that you define, not on any particular rows in any given table. The idea is that you create a transaction, then create this arbitrary lock that has an indetifier, and other processes will wait to enter that piece of code until the lock is released. This works just like lock() at the app layer. The @Resource parameter is the label of the arbitrary "concept". In more complex situations, you can even concatenate a CustomerID or something in there for more granular locking control.

DECLARE @LastChecked DATETIME,
        @LastResult NUMERIC(18,2);
DECLARE @ReturnValue NUMERIC(18,2);

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'check_timing', @LockMode = 'Exclusive';

SELECT TOP 1 -- not sure if this helps the optimizer on a 1 row table, but seems ok
       @LastChecked = LastRunTime,
       @LastResult = LastResult
FROM LastResult;

IF (DATEDIFF(MINUTE, @LastChecked, GETDATE()) >= 10 OR @LastResult <> 0)
BEGIN 
   SELECT @ReturnValue = ABS(ISNULL(SUM(ISNULL(Amount, 0)), 0))
   FROM   Transactions
   WHERE  DeletedFlag = 0
   OR     DeletedFlag IS NULL;

   UPDATE LastResult
   SET    LastRunTime = GETDATE(),
          LastResult = @ReturnValue;
END;
ELSE
BEGIN
   SET @ReturnValue = @LastResult; -- This is always 0 here
END;

SELECT @ReturnValue AS [ReturnValue];

EXEC sp_releaseapplock @Resource = 'check_timing';
COMMIT TRANSACTION;

You need to manage errors / ROLLBACK yourself (as stated in the linked MSDN documentation) so put in the usual TRY / CATCH. But, this does allow you to manage the situation.

If there are any concerns regarding contention on this process, there shouldn't be much as the lookup done right after locking the resource is a SELECT from a single-row table and then an IF statement that (ideally) just returns the last known value if the 10-minute timer hasn't elapsed. Hence, most calls should process rather quickly.

Please note: sp_getapplock / sp_releaseapplock should be used sparingly; Application Locks can definitely be very handy (such as in cases like this one) but they should only be used when absolutely necessary.

---

Related Topics