Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlock issues Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 6:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Getting Deadlock issues on regular basis..where Select insert or select update is involved in SP

Can UPDLOCK & HOLDLOCK solve the issue in select query??
or else suggest something you have comeacross








************************************
Every Dog has a Tail !!!!!
Post #1487197
Posted Thursday, August 22, 2013 6:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:54 AM
Points: 492, Visits: 135
Have you tried optimising the queries to improve performance, and what Isolation level are you using as this can have a dramatic impact if your for example trying to report off a table using Serializable Isolation.
Post #1487202
Posted Thursday, August 22, 2013 6:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
That i am not sure.. coz the developers need help & so they asked us whether we can provide the solution to them..
They have optimized the query .. still deadlock comes..

When i see the Stored procedure i saw that Select form Table 1 & next query is Update to Table 1....
& many users are using & executing the same sp for fetching the report & many users are updating the records..








************************************
Every Dog has a Tail !!!!!
Post #1487204
Posted Thursday, August 22, 2013 6:34 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:27 AM
Points: 4,432, Visits: 4,155
Using locking hints should only be done as a last resort.

Optimizing the query and indexes should come first. Also using READ_COMMITTED_SNAPSHOT isolation level can help.

This article may help you on the way http://www.sqlservercentral.com/articles/deadlock/65614/


Markus Bohse
Post #1487211
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse