April 24, 2008 at 8:35 am
I issue Read Uncommitted statements prior to calling sp_executeSql in several stored procedures. Can anyone verify that sp_executeSql is not changing that back to Read Committed ?
I ask this question because I have a problem with frequent deadlocks in a desktop application. Of course we have too much code embedded within triggers, but I cannot rip out that code as of yet.
In the meantime I am trying to get around some of these deadlock problems by the following :
1) Gracefully trapping sql deadlocks errors in the client application.
2) Allow dirty reads (uncommited) in various stored procs that only Select data.
Please note that my deadlock errors are showing up on the desktop app even during a simply refresh of the data. This is mostly likely because there's so much going on in the backend due to high volumes of currency rate updates, which in turn needs to constantly recalculate customer orders.
Anyhow, if I can simply allow dirty reads in all the client refreshes I feel I can at least work around the deadlock errors (which crash the app). Again, the client refresh calls sql stored procs which return the dataset.
Thank you,
bob
April 24, 2008 at 8:41 am
Hmm,
I'm not sure. You could use the nolock hint in your dynamic sql statement, which is effectively the same as read uncommitted. That way you can be sure that it is in read uncommitted for that particular statement.
April 24, 2008 at 10:16 am
good point. I've tried that in the past but perhaps I will revisit that idea.
Thanks for your response.
Have a great day.
bob
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply