June 16, 2013 at 8:56 am
Hi,
I am facing few problems with deadlocks. we have few tables and each table is having more than 6 million rows and we have done proper indexing on those tables. but when two different users are accessing the same table for different rows, ex: One user is retrieving the data from that table and another user is doing insert / update on one particular row in that time we are getting deadlocks.
we are using SQL server 2008, but the compatibility level of database is 80. that we can't change right now because if we change that then our application will not work.
so please help me on how to avoid the deadlocks.
Regards,
BSL
June 16, 2013 at 12:11 pm
bslchennai (6/16/2013)
so please help me on how to avoid the deadlocks.
In general, write efficient queries and create indexes to support the app.
In specifics, turn traceflag 1222 on and post the deadlock graph here (written to error log)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2013 at 8:35 am
bslchennai (6/16/2013)
Hi,I am facing few problems with deadlocks. we have few tables and each table is having more than 6 million rows and we have done proper indexing on those tables. but when two different users are accessing the same table for different rows, ex: One user is retrieving the data from that table and another user is doing insert / update on one particular row in that time we are getting deadlocks.
we are using SQL server 2008, but the compatibility level of database is 80. that we can't change right now because if we change that then our application will not work.
so please help me on how to avoid the deadlocks.
Regards,
BSL
The two main culprits of deadlocks are bad code (SELECT/UPDATE in different orders on different threads) and suboptimal indexing - or both.
In addition to what Gail requested, please also post up the complete table definitions for both tables, with all keys and indexes included. Also since you seem to know the code that is causing the deadlock, please post the code you think is involved too.
You can also go here for some guidance on Deadlock Troubleshooting (note there are 2 additional posts in this series):
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply