SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 05:32 AM
| ||||
| ||||
 stored procedures synchronization 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?      |
|
02-29-2008, 05:32 AM
| ||||
| ||||
| Re: stored procedures synchronization Alexander Korovyev (korovyev@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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
Linear Mode
