By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

stored procedures synchronization

P: n/a
hello,

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?
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.