Deadlock on stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Deadlock on stored procedure

Deadlock on stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

shd.eftekhari

Posted Wednesday, December 12, 2012 9:25 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 10, Visits: 90

I have a stored procedure(parent) which include to many if cases and in each case calls another stored procedure (child).
In child SP I have a transaction with more than 10 insert queries that have joined with other tables.
I receiving deadlock alert every day on parent stored procedure.
I want to know that are locks held in higher or lower scope?
or what is happening if I call parent SP more than once in the same time by application?

Post #1395969

Bhuvnesh

Posted Wednesday, December 12, 2012 10:27 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

shd.eftekhari (12/12/2012)

USe sql profiler. set the filter on involved Sps and deadlock .. you will get clear picture

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1395984

shd.eftekhari

Posted Wednesday, December 12, 2012 10:40 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 10, Visits: 90

I ran the profiler and now I know that which table and index column is involving deadlock, I wanted to know is lock held in higher or lower scope. because the index is in one of sub queries of child SP and I ma wondering that this table and row has not locked any where else except the last insert query which finished before this query. although all them are executing in a same transaction.

Post #1395987

Bhuvnesh

Posted Wednesday, December 12, 2012 10:54 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

shd.eftekhari (12/12/2012)

the index is in one of sub queries of child SP and I ma wondering that this table and row has not locked any where else except the last insert query which finished before this query.

So it means you have exact insert query (with values too) , try to run it individually (in new window with rollback ). is this query is handling large dataset ?

few things

•eliminate unnecessary columns OR are the columns have appropriate indexes (index and filers columns should match)
•add required columns as contained columns to the non-clustered index to make the index covering, again so that the reader does not have look up the clustered index
•avoid updates that have to maintain the non-clustered index

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1395990