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