Should I avoid using sp_getAppLock?

Question

I have a stored procedure, and I want to ensure it cannot be executed concurrently.

My (multi-threaded) application does all necessary work on the underlying table via this stored procedure.

IMO, locking the table itself is an unnecessarily drastic action to take, and so when I found out about sp_GetAppLock, which essentially enforces a critical section, this sounded ideal.

My plan was to encase the stored procedure in a transaction and to set up spGetAppLock with transaction scope. The code was written and tested successfully.

The code has now been put forward for review and I have been told that I should not call this function. However when asking the obvious question "why not?", the only reasons I am getting are highly subjective, to do with any form of locking being complicated.

I don't necessarily buy this, but I was wondering whether anyone had any objective reasons why I should avoid this construct. Like I say, given my circumstances a critical section sounds an ideal approach to me.

Further info: An application sits on top of this with 2 threads T1 and T2. Each thread is waiting for a different message M1 and M2. The business logic involved says that processing can only happen once both M1 and M2 have arrived. The stored procedure logs that Mx has arrived (insert) and then checks whether My is present (select). The built-in locking is fine to make sure the inserts happen serially. But the selects need to happen serially too and I think I need to do something over and above the built-in functionality here.

Just for clarity, I want the "processing" to happen exactly once. So I can't afford for the stored procedure to return either false positives or false negatives. I'm worried that if the stored proc runs twice in very quick succession, then both "selects" might return data which indicates that it is appropriate to perform processing.

Answer 1

What is the procedure doing that you cannot rely on SQL Servers built-in concurrency control mechanisms? Often queries can be rewritten to allow real concurrency.

But if this procedure indeed has to be executed "alone", locking the table itself on first access is most likely going to be a lot faster than using the call to sp_GetAppLock. It sounds like this procedure is going to be called often. If that is the case you should look for a way to achieve the goal with minimal impact.

---

If the table contains no other rows besides of M1 and M2 a table lock is still your best bet.

If you have multiple threads sending multiple messages you can get more fine-grained by using "serializable" as transaction level and check if the other message is there before you do the insert but within the same transaction. To prevent deadlocks in this case make sure you check for both messages for example like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
BEGIN TRAN;
 SELECT 
 @hasM1 = MAX(CASE WHEN msg_type='M1' THEN 1 ELSE 0 END), 
 @hasM2 = MAX(CASE WHEN msg_type='M2' THEN 1 ELSE 0 END)
 FROM messages WITH(UPDLOCK)
 WHERE msg_type IN ('M1','M2')

 INSERT ...

 IF(??) EXEC do_other_stuff_and_delete_messages;
COMMIT

In the IF statement before(!) the COMMIT you can use the information collected before the insert together with the information that you inserted to decide if additional processing is necessary.

In that processing step make sure to either mark those messages as processed or to delete them all still within the same transaction. That will make sure that you will not process those messages twice.

SERIALIZABLE is the only transaction isolation level that allows to lock rows that do not exist yet, so the first select statement with the WITH(UPDLOCK) effectively prevents the other row being inserted while the first execution is still running.

Finally, these are a lot of things to be aware of that could go wrong. You might want to have a look at service broker instead. you could use three queues with that. one for type M1 and one for type M2. Every time a message arrives within those queues a procedure can automatically be called to insert a token into the third queue. The third queue then could activate a process to check if both messages exist and do work. That would make the entire process asynchronous but for that it would be easy to restrict the queue 3 response to always only do one check at a time.

Service broker on msdn, also look at "activation" for the automatic message processing.

Answer 2

We use sp_getapplock all the time, due to the fact that we support some legacy applications that have been re-worked to use a SQL back-end, and the SQL Server locking model is not an exact match for our application logic.

We tend to go for a 'pessimistic' locking model, where we lock an entity before allowing a user to edit it, and use the (NOLOCK) hint extensively when reading data to bypass any blocking from the native locks on the actual tables. sp_getapplock is a good match for this. We also use it to enforce critical paths in large multi-user systems. You have to be systematic about what you call the locks you place.

We've found no performance problems with large numbers of user/locks via this route, so I see no reason why it wouldn't work well for you. Just be aware that you can get blocking and deadlocks if you have processes that place the same named locks, but not necessarily in the same order.

Answer 3

sp_GetAppLock is just like many other tools and as such it can be misused, overused, or correctly used. It is an exact match for the type of problem described by the original poster. This is a good MSSQL Tips post on the usage Prevent multiple users from running the same SQL Server stored procedure at the same time http://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/

Answer 4

You can create a table with a flag for each set of messages, so if one of the threads is first to start processing it will mark the flag as processing.

To make sure that record blocked properly once one of threads reaches it use:

   SELECT ... FROM WITH(XLOCK,ROWLOCK,READCOMMITTED) ... WHERE ...

This peace of code will put Exclusive lock on the record meaning who first got to it owns the row. Then you do your changes and update flag, other thread will get updated value because it will be blocked by Exclusive lock until first thread commmits or rollbacks transaction.

For this to work you always need to select records from table with XLOCK this way it will work as expected.

Hope this helps.

Exclusive lock prove:

    USE master
    GO

    IF OBJECT_ID('dbo.tblTest') IS NOT NULL
        DROP TABLE dbo.tblTest

    CREATE TABLE tblTest ( id int PRIMARY KEY )

    ;WITH cteNumbers AS (
        SELECT 1 N
        UNION ALL
        SELECT N + 1 FROM cteNumbers WHERE N<1000
    )
    INSERT INTO
        tblTest
    SELECT
        N 
    FROM
        cteNumbers
    OPTION (MAXRECURSION 0)

    BEGIN TRANSACTION

    SELECT * FROM dbo.tblTest WITH(XLOCK,ROWLOCK,READCOMMITTED) WHERE id = 1

    SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('master')

    ROLLBACK TRANSACTION