1

I have a stored procedure in SQL Server 2008R2 that does several updates/merges ... if the procedure is called roughly simultaneously by two processes it has a relatively high chance of deadlocking.

I'd like to solve this problem by making my stored procedure not re-entrant. That is I would like all calls to the stored procedure to be serialized. Put a final way, I'd like any call to the stored procedure to block indefinitely until any other running instance of the procedure is complete (and then any blocked calls are allowed to run one at a time).

My calling code is C#... i know how to serialize code on a synchronization object in C# and was hoping to conceptually accomplish the same thing in SQL Server 2008 R2.

What is the best way to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TCC
  • 2,546
  • 1
  • 24
  • 35
  • 1
    Thanks @Martin-Smith ... I really did look for the answer before asking, but sp_getapplock will do the trick nicely – TCC Oct 07 '14 at 20:22
  • Why not just put the entire contents of the procedure inside a transaction, and elevate the isolation level to `SERIALIZABLE`? This seems to be a more straightforward and intuitive approach to the actual problem you're trying to solve. – Aaron Bertrand Oct 07 '14 at 20:48
  • Thanks @AaronBertrand ... I wasn't sure what else would be required in that approach ... a serializable transaction will serialize access to whatever resources are i guess "touched"... but the transaction would have to be sure and touch some particular resource in all cases in order for that to work ... for instance my procedure has some branching logic where some queries might be run in some instances but not others... i wanted a simple "this never runs concurrently" guarantee. Naming a specific synchronization resource is the clearest approach in my mind which is what sp_getapplock does. – TCC Oct 09 '14 at 20:33

0 Answers0