{"id":53,"date":"2020-01-07T14:53:57","date_gmt":"2020-01-07T04:53:57","guid":{"rendered":"http:\/\/data-engineer.net\/?p=53"},"modified":"2023-02-04T18:10:46","modified_gmt":"2023-02-04T08:10:46","slug":"prevent-concurrent-runs-for-a-sql-stored-procedure","status":"publish","type":"post","link":"https:\/\/data-engineer.net\/?p=53","title":{"rendered":"Prevent Concurrent runs for a SQL Stored Procedure"},"content":{"rendered":"\n<h2>Problem<\/h2>\n\n\n\n<p>Suppose you have a SQL Stored Procedure and need to prvenet users or processes to run it concurrently. If an instance of SP is called and there exists another instance of this SP currently running, we want the SP to wait. The wait may time out after a certain amout of time<\/p>\n\n\n\n<h2>Solution<\/h2>\n\n\n\n<p>To this end, the SP code needs to be put inside a transaction. Transactions are capable of monitoring their concurrency. I am calling the transaction, MyLock here. Note the name of stored procedure, MyStoredProc is also passed in sp_getapplock SP. Below code, waits 15 seconds before it retries for another SP call attempt.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER PROCEDURE MyStoredProc\nAS\nBEGIN\ndeclare @msg nvarchar(100), @result int = -1, @retry_counter int = 0, @retry_MAX int = 15*4*10 \/* 10 mins *\/\n\nbegin tran MyLock\n\nwhile @result = -1 and @retry_counter &lt; @retry_MAX\nbegin\n\texec @result = sp_getapplock @Resource = 'MyStoredProc', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0\n\tif @result = -1\n\t\twaitfor delay '00:00:15'\n\tset @retry_counter = @retry_counter + 15\nend<\/code><\/pre>\n\n\n\n<p>Now we want the SP to time out and break the SP if @retry_MAX is met.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>if @result &lt; -1 or @retry_counter >= @retry_MAX\nbegin\n\trollback tran MyLock\n\tset @msg = N'Error Code= ' + cast(@result as nvarchar) + N' Could not acquire application lock' raiserror(@msg, 0, 1) with nowait\n\treturn -2\nend<\/code><\/pre>\n\n\n\n<p>Then comes the actual code that MyStoredProc is supposed to do<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select 'Do the Job...'\nselect 'Do the Job...'<\/code><\/pre>\n\n\n\n<p>Finally the lock is release and this is the case the SP has normally finished.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>exec sp_releaseapplock @Resource = 'MyStoredProc'\ncommit tran MyLock<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Suppose you have a SQL Stored Procedure and need to prvenet users or processes to run it concurrently.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2],"tags":[11,10,9,8],"_links":{"self":[{"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/posts\/53"}],"collection":[{"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/data-engineer.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=53"}],"version-history":[{"count":1,"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/posts\/53\/revisions"}],"predecessor-version":[{"id":54,"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/posts\/53\/revisions\/54"}],"wp:attachment":[{"href":"https:\/\/data-engineer.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=53"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-engineer.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=53"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-engineer.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=53"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}