How can we avoid Stored Procedures being executed in parallel?

Question

We have the following situation:

A Stored Procedure is invoked by a middleware and is given a XML file as parameter. The Procedure then parses the XML file and inserts values into temporary tables inside a loop. After looping, the values inside the temporary tables are inserted into physical tables.

Problem is, the Stored Procedure has a relatively long run-time (about 5 Minutes). In this period, it is likely that it is being invoked a second time, which would cause both processes to be suspended.

Now my question: How can we avoid a second execution of a Stored Procedure if it is already running?

Best regards

Answer 1

I would recommend designing your application layer to prevent multiple instances of this process being run at once. For example, you could move the logic into a queue that is processed 1 message at a time. Another option would be locking at the application level to prevent the database call from being executed.

SQL Server does have a locking mechanism to ensure a block of code is not run multiple times: an "app lock". This is similar in concept to the lock statement in C# or other semaphores you might see in other languages.

To acquire an application lock, call sp_getapplock. For example:

begin tran
exec sp_getapplock @Resource = 'MyExpensiveProcess', @LockMode = 'Exclusive', @LockOwner = 'Transaction'

This call will block if another process has acquired the lock. If a second RPC call tries to run this process, and you would rather have the process return a helpful error message, you can pass in a @LockTimeout of 0 and check the return code.

For example, the code below raises an error if it could not acquire the lock. Your code could return something else that the application interprets as "process is already running, try again later":

begin tran
declare @result int
exec @result = sp_getapplock @Resource = 'MyExpensiveProcess', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0

if @result < 0
begin
    rollback
    raiserror (N'Could not acquire application lock', 16, 1)
end

To release the lock, call sp_releaseapplock.

exec sp_releaseapplock @Resource = 'MyExpensiveProcess'

Answer 2

Stored procedures are meant to be run multiple times and in parallel as well. The idea is to reuse the code. If you want to avoid multiple run for same input, you need to take care of it manually. By implementing condition check for the input or using some locking mechanism.

If you don't want your procedure to run in parallel at all (regardless of input) best strategy is to acquire lock using some entry in DB table or using global variables depending on DBMS you are using.