kevinmkr1
asked on
What is the best way to track down/eliminate/prevent Deadlock Transaction issues with CF7 and SQL Server 2005?
While I consider myself fairly experienced with CF and SQL syntax, I am not terribly well-versed in SQL Server. Therefore, I do not (have not) used stored procedures or triggers, etc. I would prefer not to, at this point, until I have time to fully learn SQL Server.
My current project involves hospital patients (stored in one table) and their treatment events (stored in another table). The treatment table contains 56 columns of varying type and, since it contains every unique patient treatment event, contains hundreds of thousands of records. The table can be accessed from dozens of pages and reports and can be updated in a half-dozen other places.
Long story short, it's a big table that has multiple avenues of entry and, as a result, is constantly being viewed and altered by users.
Slowly, over time (years), I've been noticing more and more of the "Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim." errors and they all seem to involve using a SELECT on the treatment table.
I've tried to consider and repair most "best practice" SQL issues (there are no "SELECT * FROM", for example) but it has not helped.
I've read in many places that I should consider using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" before my SELECTS (and, presumably, my INSERTS and UPDATES?). I'm not sure if this is the best solution to the issue or if it simply glosses over a bigger issue.
So, considering the following:
1) Pure SQL only (no triggers or stored procedures)
2) Large (and growing) table (indexed by SQL Query Analyzer)
3) Can be accessed from many places through the system
4) We have several installations -- all meet a minimum system requirement of CF7 and MS SQL 2005.
What would be the best solution or way to attack this?
My current project involves hospital patients (stored in one table) and their treatment events (stored in another table). The treatment table contains 56 columns of varying type and, since it contains every unique patient treatment event, contains hundreds of thousands of records. The table can be accessed from dozens of pages and reports and can be updated in a half-dozen other places.
Long story short, it's a big table that has multiple avenues of entry and, as a result, is constantly being viewed and altered by users.
Slowly, over time (years), I've been noticing more and more of the "Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim." errors and they all seem to involve using a SELECT on the treatment table.
I've tried to consider and repair most "best practice" SQL issues (there are no "SELECT * FROM", for example) but it has not helped.
I've read in many places that I should consider using "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" before my SELECTS (and, presumably, my INSERTS and UPDATES?). I'm not sure if this is the best solution to the issue or if it simply glosses over a bigger issue.
So, considering the following:
1) Pure SQL only (no triggers or stored procedures)
2) Large (and growing) table (indexed by SQL Query Analyzer)
3) Can be accessed from many places through the system
4) We have several installations -- all meet a minimum system requirement of CF7 and MS SQL 2005.
What would be the best solution or way to attack this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's a really great point -- I'm sure you're right (regarding the CF vs SQL tagging). My thought process was, I am not prepared for any extremely heavy SQL at this point and so an answer like, "move all of your SQL inserts to Stored Procedures and then set a trigger here...etc" would be far too much for me at this point. I was rather hoping somebody with CF experience would have some magical solution. Considering that I've been coding for 10 years in CF and this is my first time posting on Experts-Exchange, I should have realized that I would not be receiving a simple answer -- otherwise I would have been able to find it by my traditional avenues. :)
Do you have any experience with 'READ_COMMITTED_SNAPSHOT'?
As for finishing out this question, I'm not sure what the common Experts Exchange protocol is, but I would like to leave this open for another few days just in case a true dba shows up. I intend to award you partial (or complete) points, in all cases. I would even take your recommendation as to how to distribute them (if you do not end up receiving them all).
Thanks for your assistance.
Do you have any experience with 'READ_COMMITTED_SNAPSHOT'?
As for finishing out this question, I'm not sure what the common Experts Exchange protocol is, but I would like to leave this open for another few days just in case a true dba shows up. I intend to award you partial (or complete) points, in all cases. I would even take your recommendation as to how to distribute them (if you do not end up receiving them all).
Thanks for your assistance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It doesn't look like I'm going to get any other action here so I think I'll close this up.
Thanks again for the links and advice. It should get me well on my way towards diagnosing these issues.
Thanks again for the links and advice. It should get me well on my way towards diagnosing these issues.
ASKER
As this doesn't seem to be a "one size fits all" situation, I'm not sure how the answer provided could have been improved upon.
It might be worth it to open a new question - only in the MS SQL-type zones - about the READ_COMMITTED_SNAPSHOT issue. As for some opinions/experiences of people who've used it. Then at least you'll have a better sense of how viable an option it is (or isn't) for your particular setup.
Best of Luck
Best of Luck
> As for some
Correction: ASK for some ..
Correction: ASK for some ..
ASKER
So, I guess I should ask you -- would cutting the table down to 50% (or 25%) of its width, without reducing the quantity of accesses, speed the transaction execution time enough to make the effort worthwhile, based on your experience?