How do I limit a SQL stored procedure to be run by one person at a time?

Question

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

I also tried finding the procedure with sys.dm_exec_requests and blocking the procedure, while this does work, i think it's not optimal because on some servers I don't have the permissions to run sys.dm_exec_sql_text(sql_handle).

What is the best way for me to do this?

Answer 1

To add to @Tibor-Karaszi's answer, setting a lock timeout doesn't actually produce an error (I've submitted a PR against the docs). sp_getapplock just returns -1, so you have to check the return value. So like this:

create or alter procedure there_can_be_only_one 
as
begin
begin transaction

  declare @rv int
  exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
  if @rv < 0
   begin
      throw 50001, 'There is already an instance of this procedure running.', 10
   end

  --do stuff
  waitfor delay '00:00:20'


commit transaction
end

Answer 2

Use sp_getapplock in the beginning of the proc, and set a lock timeout to a very low value. This way you get an error when you are blocked.

Answer 3

Another option is to build a table to control access to the procedure. the example below shows a possible table as well as a procedure that could use it.

CREATE TABLE dbo.ProcedureLock
    (
    ProcedureLockID INT NOT NULL IDENTITY(1,1)
    , ProcedureName SYSNAME NOT NULL
    , IsLocked BIT NOT NULL CONSTRAINT DF_ProcedureLock_IsLocked DEFAULT (0)
    , UserSPID INT NULL
    , DateLockTaken DATETIME2(7) NULL
    , DateLockExpires DATETIME2(7) NULL
    , CONSTRAINT PK_ProcedureLock PRIMARY KEY CLUSTERED (ProcedureLockID)
    )

CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_ProcedureLock_ProcedureName
    ON dbo.ProcedureLock (ProcedureName)

INSERT INTO dbo.ProcedureLock
    (ProcedureName, IsLocked)
VALUES ('dbo.DoSomeWork', 0)

GO

CREATE PROCEDURE dbo.DoSomeWork
AS
BEGIN

    /** Take Lock */
    UPDATE dbo.ProcedureLock
    SET IsLocked = 1
        , UserSPID = @@SPID
        , DateLockTaken = SYSDATETIME()
        , DateLockExpires = DATEADD(MINUTE, 10, SYSDATETIME())
    WHERE ProcedureName = 'dbo.DoSomeWork'
        AND (IsLocked = 0
            OR (IsLocked = 1 AND DateLockExpires < SYSDATETIME())
            )

    IF COALESCE(@@ROWCOUNT, 0) = 0
    BEGIN
        ;THROW 50000, 'This procedure can only be run one at a time, please wait', 1;
    END

    /** DO WHATEVER NEEDS TO BE DONE */

    /** Release the lock */
    UPDATE dbo.ProcedureLock
    SET IsLocked = 0
        , UserSPID = NULL
        , DateLockTaken = NULL
        , DateLockExpires = NULL
    WHERE ProcedureName = 'dbo.DoSomeWork'

END

Answer 4

OP described the desired process as a "sort of archiving"
This answer assumes that OP was describing auditing tasks, not archiving, and recommends a solution to OP's root problem (as described) not OP's actual question.

You don't need complex schemes with stored procs to do this, because triggers are well suited to handle auditing type tasks.
As a bonus, triggers entirely avoid the multiple people at the same time problem OP mentioned.
Triggers also should be easier to code, test, and maintain than a custom solution because triggers are a database feature.

In general:

• Archiving generally refers to the process of moving data that is no longer actively used into a separate storage location for long-term retention.
  Triggers would not usually be used for archiving.
• Auditing can describe a number of things, in your case it seems as though you want to maintain a partial history of changes made to rows.

Note that current versions of SQL Server support auditing and 'auditing like' features at a system level. There are multiple options from full auditing with system-versioned Temporal Tables to Change Data Capture (CDC), to Change Tracking.

Thanks to @Andriy M for mentioning "auditing" in comments.

Answer 5

I think you are trying to solve the problem in an incorrect way. What you want is utmost protection of database consistency. If two persons run a stored procedure at the same time, the database consistency may be violated.

To protect against various kinds of database inconsistencies, the SQL standard has four transaction isolation levels:

• READ UNCOMMITTED where basically transactions lose their value, other transactions seeing dirty data. Don't use this!
• READ COMMITTED where transactions see only committed data, but there may be inconsistencies where two transactions can step over each other's toes
• REPEATABLE READ where one kind of inconsistency, non-repeatable read, is solved
• SERIALIZABLE which guarantees that there exists some virtual order in which executing the transactions would lead to the results that their execution resulted in

However, the SQL standard has a locking based approach for these database inconsistencies, and for performance reasons many databases take a snapshot isolation based approach that basically has these levels:

• READ COMMITTED which is the same it's in locking based databases
• SNAPSHOT ISOLATION where database sees a snapshot of all data and if it tries to update a row that has been updated by some other transaction, it is cancelled, yet there are some well-known anomalies that can take place
• SERIALIZABLE which is the same as it's in locking based databases, but this time implemented in a different manner, not by taking locks but by ensuring there are no serialization violations, and if such a violation is detected, cancelling a transaction

The transaction cancellations in these snapshot isolation based databases may sound worrying, but then again every single database will cancel a transaction due to a deadlock, so any reasonable application needs anyway to be able to re-try a transaction.

What you want is the SERIALIZABLE isolation level: it ensures that if transactions executed independently one after another result in a good state, any parallel execution of the transactions also results in a good state. Fortunately, Michael Cahill has in his doctoral dissertation found out how SERIALIZABLE isolation level can be supported by snapshot isolated databases with little effort.

If using a SERIALIZABLE isolation level in a snapshot isolated database, if two people try to run the stored procedure concurrently and they would step on each other's toes, one of the transactions would be cancelled.

Now, does SQL Server genuinely support the SERIALIZABLE isolation level (instead of masquerading snapshot isolation behind the SERIALIZABLE keyword)? Quite frankly, I don't know: the only database I know that supports it is PostgreSQL.

Even though I failed to give SQL Server specific advice, I'm still posting this answer nevertheless, as users of PostgreSQL and users of other databases that can consider switching to PostgreSQL can benefit from my answer. Also, users of non-PostgreSQL databases who can't switch to PostgreSQL can pressure their favorite database vendor to offer genuine SERIALIZABLE isolation level.