|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Procedure Lock?Is there a way to keep a stored procedure from executing more than once
simultaneously? That is, if three commands to execute the procedure arrive nearly simultaneously on different connections, how can I make the requests queue up so that the procedure executes one at a time with no overlap, the second starting when the first completes? It would help if you could provide more information about 'why' you would
want to do that. It seems very odd to want to purposefully 'handicap' the server. You must have a goal. If you share that, perhaps we can help you find an appropriate way to accomplish your goal. -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Lionstone" <HIDElionst***@HIDEhushmail.com> wrote in message news:ecXnFMOrGHA.4960@TK2MSFTNGP04.phx.gbl... > Is there a way to keep a stored procedure from executing more than once > simultaneously? That is, if three commands to execute the procedure > arrive nearly simultaneously on different connections, how can I make the > requests queue up so that the procedure executes one at a time with no > overlap, the second starting when the first completes? > Each instance of the procedure first finds the next record that needs to be
sent to our application for processing by a human being ("next" is defined by a rather nasty query). It retrieves the ID, then updates the record to indicate that it has been sent to the application. The problem is we sometimes have several dozen connections request a new record within a few milliseconds of each other. All of them then retrieve the same ID and make the same subsequent update. If the procedure executions were to queue up, then the last connection would probably be delayed less than half a second, and the duplicate retrievals would stop. Show quote "Arnie Rowland" <ar***@1568.com> wrote in message news:eNxZXUOrGHA.352@TK2MSFTNGP02.phx.gbl... > It would help if you could provide more information about 'why' you would > want to do that. It seems very odd to want to purposefully 'handicap' the > server. You must have a goal. If you share that, perhaps we can help you > find an appropriate way to accomplish your goal. > > -- > Arnie Rowland > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Lionstone" <HIDElionst***@HIDEhushmail.com> wrote in message > news:ecXnFMOrGHA.4960@TK2MSFTNGP04.phx.gbl... >> Is there a way to keep a stored procedure from executing more than once >> simultaneously? That is, if three commands to execute the procedure >> arrive nearly simultaneously on different connections, how can I make the >> requests queue up so that the procedure executes one at a time with no >> overlap, the second starting when the first completes? >> > > Lionstone wrote:
> Each instance of the procedure first finds the next record that needs to be I just had another very long conversation with a fellow in this group > sent to our application for processing by a human being ("next" is defined > by a rather nasty query). It retrieves the ID, then updates the record to > indicate that it has been sent to the application. The problem is we > sometimes have several dozen connections request a new record within a few > milliseconds of each other. All of them then retrieve the same ID and make > the same subsequent update. If the procedure executions were to queue up, > then the last connection would probably be delayed less than half a second, > and the duplicate retrievals would stop. > about how to do this very thing. Look back through the posts from this week, you'll find it... "Lionstone" <HIDElionst***@HIDEhushmail.com> wrote in message Start a transaction and acquire an appropriate lock, perform the work of the news:ecXnFMOrGHA.4960@TK2MSFTNGP04.phx.gbl... > Is there a way to keep a stored procedure from executing more than once > simultaneously? That is, if three commands to execute the procedure > arrive nearly simultaneously on different connections, how can I make the > requests queue up so that the procedure executes one at a time with no > overlap, the second starting when the first completes? procedure and commit. You can use sp_getapplock to get an application-wide lock that functions like a Mutex or Critical Section. David "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in That sounds promising. I'll open up BOL and see what I can see.message news:u4ifhWOrGHA.1852@TK2MSFTNGP03.phx.gbl... > Start a transaction and acquire an appropriate lock, perform the work of > the procedure and commit. > > You can use sp_getapplock to get an application-wide lock that functions > like a Mutex or Critical Section. > > > David Thanks for the nudge in the right direction. :) Thank you again.
I would never have suspected anything like application locks existed. It worked exactly as I hoped. Show quote "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:u4ifhWOrGHA.1852@TK2MSFTNGP03.phx.gbl... > > "Lionstone" <HIDElionst***@HIDEhushmail.com> wrote in message > news:ecXnFMOrGHA.4960@TK2MSFTNGP04.phx.gbl... >> Is there a way to keep a stored procedure from executing more than once >> simultaneously? That is, if three commands to execute the procedure >> arrive nearly simultaneously on different connections, how can I make the >> requests queue up so that the procedure executes one at a time with no >> overlap, the second starting when the first completes? > > Start a transaction and acquire an appropriate lock, perform the work of > the procedure and commit. > > You can use sp_getapplock to get an application-wide lock that functions > like a Mutex or Critical Section. > > > David >  |
|||||||||||||||||||||||
Other interesting topics