Welcome to weblogs.com.pk Sign in | Join | Help

Disallowing Running the Stored Procedure in Parallel

I wrote a stored procedure a while back; it manipulates the database of a legacy and proprietary application. Few assumptions were taken like the stored procedure will always get invoked by the job and job doesn't run in parallel. Few weeks back there was a requirement to let the user do that operation from the web on demand; which triggered the concurrency problem later. You can control this using sp_getapplock and sp_releaseapplock.

Here is a reference code using the concept, first lets create a test table with test data!

create table test (i int, updatedby nvarchar(50));
insert into test (i) values (1);

Now the code that we want to prevent to run in parallel

declare @result int;
declare @counter int;
set @counter = 60;
begin transaction;
exec @result = sp_getapplock @resource = 'Test', @lockmode = 'Exclusive', @locktimeout = 0;
if @result >= 0
begin
    while @counter > 0
    begin
        update test set i = 1, updatedby = 'Test';
        set @counter = @counter - 1;
        waitfor delay '0:0:1';
    end
    exec sp_releaseapplock  @resource = 'Test';
    commit transaction;
end
else
begin
    rollback transaction;
    print 'Lock couldnt acquired..';
end

With @lockmode = ‘Exclusive’; we are trying to get an exclusive lock and with @locktimeout = 0; we want immediate response i-e we dont want to wait for getting the lock. The @resource should be a unique name; for this simple proof of concept; ‘Test’ is used. waitfor is used and in a loop the update operation is performed; this loop will take a minute to complete; and during this time; the same batch can be run in a seperate session with update test set i = 2, updatedby = ‘Test2’ for distinction. You will immediate get the Lock couldnt acquired message there!

If you omit the @locktimeout above; the second session will get blocked and as soon as the first session releases the lock the second session will go into the loop. You can check this running the following select query periodically after you have running the two sessions. You will notice that first it will show 1, Test and then after a minute it will start showing 2, Test2

set transaction isolation level read uncommitted
go
select *
from test
Published Tuesday, October 20, 2009 3:58 PM by khurram
Filed under: ,

Comments

No Comments

New Comments to this post are disabled