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?

  • 3
    Can you take one step back, and provide some more info on what the procedure is doing, and why you want to avoid multiple people running it at the same time? There may be a coding technique that eliminates this requirement, or some sort of queuing you could implement to handle things.
    – AMtwo
    Oct 5, 2018 at 12:14

5 Answers 5


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 
begin transaction

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

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

commit transaction
  • 1
    Checking SP return values seems to have gone out of fashion, devs these days look at me like I’m asking them to turn on the wireless Nov 30, 2023 at 9:21

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.


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)
    , DateLockTaken DATETIME2(7) NULL
    , DateLockExpires DATETIME2(7) NULL

    ON dbo.ProcedureLock (ProcedureName)

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



    /** 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())

        ;THROW 50000, 'This procedure can only be run one at a time, please wait', 1;


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

  • 1
    This is very similar to (or perhaps the same as) what I immediately thought of after reading the question, but I had an issue with that idea that I wasn't entirely sure how to address and can't see it addressed in your answer either. My concern is, what if something happens during the "do whatever needs to be done" part? How would you reset the IsLocked state to 0 in that case? I'm also curious about your use of COALESCE here. Can @@ROWCOUNT be null after statements like UPDATE? Finally, just a minor nitpick, why put a semicolon in front of the THROW statement in that specific case?
    – Andriy M
    Oct 6, 2018 at 7:35
  • The lock expiration is one way of handling that. It would need to be set to a reasonable timeframe, I've set it to 10 minutes in my example. You could encapsulate your work logic in a try/catch block and unlock in the catch if you wanted as well. I use COALESCE out of habit, but no @@ROWCOUNT cannot be NULL. the leading semi-colon comes from working with Visual Studio database projects, it complains if it's not there. no harm either way. Oct 7, 2018 at 0:35

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.

  • 1
    What do you mean by "immediately"? Immediately after what? After insert? So as soon as a new row arrives, it immediately is sent to archive? Or did you mean after update? So any data change triggers archiving? Perhaps you should be more specific about which scenario you have in mind suggesting this.
    – Andriy M
    Oct 6, 2018 at 7:40
  • The archival may be too costly and/or too rarely desired to be worth doing on every insert, especially if the source table is frequently inserted and/or transactional safety between it and the archive would require expensive locks. Oct 6, 2018 at 12:35
  • @underscore_d Yes, it may be too costly or not always required. This is why I started my answer with the statement that the 'real' problem may be more complex. In the event that it isn't, triggers are a good solution. Plus it will probably be easier to test and maintain because it is a feature of the database instead of a custom solution. Oct 9, 2018 at 19:22
  • @AndriyM I removed the word immediately, replacing with a ref to insert/update triggers. Sorry for the confusion. Oct 10, 2018 at 13:26
  • 1
    I've re-read the question and I think I can see the source of my confusion. What you are suggesting here is more akin to auditing than archiving. As I understand, archiving data implies moving the data (e.g. from one table to another). However, even though the OP summarised their procedure's function as "a sort of archiving", they never said that the data would be removed from the source, only that it would be selected from it and inserted into the target. So I'm guessing you assumed that the OP needs to copy, rather than move, their data, in which case using triggers probably makes sense.
    – Andriy M
    Oct 10, 2018 at 19:56

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.