Alexander Korovyev (ko******@rambler.ru) writes:
I have a bunch of stored procedures which are run for some background
processing. In effect they are launched at random. At any given time
point only one of the stored procedures is allowed to execute. If a
stored procedure is launched while another one is still running the
launched stored procedure must not wait for the running one to finish
and return immediately. What are the possible ways to achieve this?
Application locks are probably the best way. Look at sp_getapplock in
Books Online
Beware that you need to avoid the situation where a stored procedure aborts
because of an error without the lock being released. This is simple in
two csaes:
o The stored procedure starts a transaction, and you take the application
on transaction level, and care is taken that there is a rollback in
case of error.
o You take the transaction lock on session level, and once the stored
procedure exits, the client application disconects. (Beware of that
connection pooling in this case, and can cause the lock to linger
until the connection is reused or dropped from the pool.)
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
