Geek Answers Handbook

Prevent simultaneous execution of stored procedures at the same time

I have a stored procedure for SQL Server 2000 that can only execute one instance at a time. Is there a way to verify and verify that the procedure is not currently running?

Ideally, I would like the code to be autonomous and efficient (fast). I also do not want to do something like creating a global temp table that checks its existence, because if for any reason the procedure fails, it will always be considered running ...

I searched, I don’t think it was asked yet. If it was, sorry.

+6
sql-server stored-procedures sql-server-2000
source share
4 answers

yes there is a way. use the so-called SQL Server application locks .

EDIT: yes, this also works in SQL Server 2000 .

+10
source share

You can use sp_getapplock sp_releaseapplock , as in the example found in Lock a Stored procedure for single use only .

But is that what you are really trying to do? Are you trying to get a transaction with a high level of isolation ? You will probably be much better off handling the concurrency type at the application level, as all higher-level languages ​​have much better primitives for this kind of thing.

+5
source share

how about locking a dummy table? This will not cause deadlocks in the event of a failure.

+1
source share

At the beginning of the procedure, check if a piece of data is locked, if it is not locked

At the end of the procedure, unlock a piece of data.

t

SELECT @IsLocked=IsLocked FROM CheckLockedTable Where spName = 'this_storedProcedure' IF @IsLocked = 1 RETURN ELSE UPDATE CheckLockedTable SET IsLocked = 1 Where spName = 'this_storedProcedure' . . . -- At end of Stored Procedure UPDATE CheckLockedTable SET IsLocked = 0 Where spName = 'this_storedProcedure'
0
source share

More articles:


All Articles