CODEWITHSUNDEEP
sqlsql-serverstored-procedures
bseed
bseed

Reputation: 41

How can we avoid Stored Procedures being executed in parallel?

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

Upvotes: 4

Views: 6070

Answers (3)

DanielG
DanielG

Reputation: 1675

You can check if the stored procedure is already running using exec sp_who2. This may be an approach to consider. In your SP, check this first and simply exit if it is. It will run again the next time the job executes.

You would need to filter out the current thread, make sure the count of that SP is 1 (1 will be for the current process, 2 means already running), or have a helper SP that is called first.

Here are other ideas: Check if stored procedure is running

Upvotes: 0

Paul Williams
Paul Williams

Reputation: 17020

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'

Upvotes: 3

Ubercool
Ubercool

Reputation: 1021

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.

Upvotes: 1

Related Questions