Deadlock on stored procedure

Question

Deadlock on stored procedure

shd.eftekhari

SSC Enthusiast

Points: 108
More actions
December 12, 2012 at 9:25 pm

#286373

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?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

shd.eftekhari (12/12/2012)
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?

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

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shd.eftekhari SSC Enthusiast Points: 108 More actions · Answer 2

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shd.eftekhari SSC Enthusiast Points: 108 More actions · Answer 4

Thank you for your response but the most important issue for me is deadlock scope. do you also have any idea about this?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

shd.eftekhari (12/12/2012)
Thank you for your response but the most important issue for me is deadlock scope. do you also have any idea about this?

Sorry i dont have but

have you considered ?

eliminate unnecessary columns OR are the columns have appropriate indexes (index and filers columns should match)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

shd.eftekhari SSC Enthusiast Points: 108 More actions · Answer 6

Yes I did, Indexes are pretty organized and defragmented

shd.eftekhari SSC Enthusiast Points: 108 More actions · Answer 7

shd.eftekhari

SSC Enthusiast

Points: 108

December 12, 2012 at 11:18 pm

#1568160

Any way thank you