March 27, 2008 at 1:47 pm
In the application, we have a transaction which raps like 8 tables in it (update or insert). This transaction atkes a minute to complete. This transaction is called like 10 times continuously. As a result deadlock happens. I told them that if there is a lock continuously for 1 min and since it happens again and again, obviously deadlock happens since other process compete for the resource. I know this can be solved if they tune their application code. But they want me to do something on the database side and they are claiming that they cannot tune anything.
Now my question is, is there anything I can do to solve this issue from the database side?
Please let me know.
March 27, 2008 at 2:17 pm
Can you post the SQL queries, the table structure and any indexes on those tables? Also, if you have the deadlock graph that will be very useful (Obtain deadlock graph by switching on traceflag 1204 or 1222, graph will be written to the SQL error log)
Once we have the details of what's causing the deadlock it will be possible to suggest options for you.
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
March 27, 2008 at 2:23 pm
One extreme solution - create a separate database of the 8 tables they have wrapped up in this transaction, and let them run their process in isolation. Once the indexing is properly done, there's not a lot more you can do. If their transaction is still as inefficient as you believe it is, then THEY have to sit down and rethink what they are doing and why.
Yes, fixing the code at its source is better than abusing an entire database.
If you can establish a test environment to run benchmarks in, so much the better.
March 27, 2008 at 2:25 pm
I found out what is causing the deadlock. It is the checkpoint that gets into deadlock with the transaction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply