Deadlock issues

• OnlyOneRJ

  Hall of Fame

  Points: 3894

  More actions

  August 22, 2013 at 6:16 am

  #278934

  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 !!!!! :-D

• RandomEvent

  SSCrazy

  Points: 2963

  More actions

  August 22, 2013 at 6:20 am

  #1643866

  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.

• OnlyOneRJ

  Hall of Fame

  Points: 3894

  More actions

  August 22, 2013 at 6:24 am

  #1643868

  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 !!!!! :-D

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  August 22, 2013 at 6:34 am

  #1643875

  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/

  [font="Verdana"]Markus Bohse[/font]