11

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 '18 at 12:14
13

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
7

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.

7

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
  • 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 '18 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. – Jonathan Fite Oct 7 '18 at 0:35
-1

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.

  • I take it the downvote means somebody investigated whether SQL Server has the SERIALIZABLE isolation level and found out that it doesn't. – juhist Oct 7 '18 at 15:41
-2

I realize the 'real' problem may be more complex.

In case it isn't: if you do your archiving with insert and/or update triggers you can avoid the problem you're trying to solve.

Hope that helps,
-Chris C.

  • 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 '18 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. – underscore_d Oct 6 '18 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. – J. Chris Compton Oct 9 '18 at 19:22
  • @AndriyM I removed the word immediately, replacing with a ref to insert/update triggers. Sorry for the confusion. – J. Chris Compton Oct 10 '18 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 '18 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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