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 issue due to locking on resources Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 3:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 03, 2013 12:38 AM
Points: 91, Visits: 77
In our production environment, we are seeing frequest deadlocks due to locks on the resources. I have attached DeadlockGraphs captured for the same. Need your help on finding the fix to solve the problem.

Thanks in advance.


*******
Sudhakar


  Post Attachments 
Graphs.zip (5 views, 2.06 KB)
Post #1358438
Posted Thursday, September 13, 2012 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, Visits: 852

can you tell me size of tableA and execution time for the below query?


SELECT * from FROM tableA

UPDATE A SET A.IsError=1 FROM Table1 A LEFT JOIN Table2 B ON B.Id=A.Id LEFT JOIN Table3 C ON C.Id = A.Id WHERE (C.Id IS NOT NULL OR B.Id IS NOT NULL) UPDATE A SET A.IsError=1 FROM Table1 A LEFT JOIN Table2 B ON B.Id = A.Id LEFT JOIN Table3 C ON C.Id = A.Id WHERE (C.Id IS NOT NULL OR B.Id IS NOT NULL)


Thanigaivel.
Post #1358536
Posted Friday, September 14, 2012 12:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 03, 2013 12:38 AM
Points: 91, Visits: 77
Below is the size of the tableA

name rows reserved data index_size unused
TableA 49132 19352 KB 8464 KB 10280 KB 608 KB


Select * from tableA - took 00:00:01
Update query - took 00:00:01

Adding additional indexes will fix the issue? or providing locking hint in the queries will be the best option to fix it?






*******
Sudhakar
Post #1359056
Posted Friday, September 14, 2012 12:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:44 AM
Points: 45, Visits: 852
Either we can use lock hint based on sensitive of data or Non Clustered Index which will create addition maintenance work. My choice is Non Clustered index.

Post #1359060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse