0

enter image description here

If a stored procedure has multiple statements, will the lock acquired for the first statement be released once its executed or will it hold on till entire stored procedure is complete?

I am running a stored procedure under transaction from Entity Framework. Invoking the same stored procedure multiple times simultaneously is causing deadlock. The stored procedure selects a bunch of records from a table and inserts back into same table and moves on to next table. For some tables, it makes join with previous table to get the list of records to be inserted.

The stored procedure doesn't have any transaction but the call from application routed via Entity Framework has a transaction

closed as unclear what you're asking by mustaccio, RLF, James Anderson, Max Vernon, Kin Shah May 4 '16 at 21:07

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Many variables. Does the procedure have a BEGIN TRAN? Is it called from code that begins a transaction? What Isolation Level are you using? – Chad Mattox May 4 '16 at 17:54
  • 1
    You should post the deadlock graph XML - that will help us to help you. I agree that there are many possibilities and without looking at the deadlock xml, we cant help you much. – Kin Shah May 4 '16 at 19:03
  • Are you using entity framework to wrap the call to the SP in a transaction? You might want to handle the transactions in the SP to get a more granular level of transaction handling. We really need to see the code though – James Anderson May 4 '16 at 19:05
  • @Kin Updated the question with deadlock graph – f444ran May 5 '16 at 4:39
  • @JamesAnderson: Yes the EF call has a transaction for the call. The index in graph above is foreign key constraint index. – f444ran May 5 '16 at 4:41
1

My understanding of this is that, yes, under a transaction your SP will hold locks for the duration of the transaction. (Note there are lots of types of locks - SQL Server could row lock, table lock, page lock etc)

One SP being called simultaneously (with no other activity on your DB) causing deadlocks suggests one thing to me: the SP is not locking the same things in the same order on each call. Do you have IF blocks that perform actions on a table if a condition is met? You may wish to rewrite those to make the IF condition appear in the WHERE condition instead. This will ensure the SP locks the same resources in the same order each time.

Not the answer you're looking for? Browse other questions tagged or ask your own question.