Is the application in production while you're trying to fix it? If so,
then I wouldn't just remove readuncommitted from every stored
procedure and then watch as things fall to pieces. Make use of the
Profiler to take a look at the application so you can pinpoint the
problem areas. See if you can target the most critical queries, or the
ones that had the most problems. If the app has such serious problems
that it could only operate using dirty reads, then the problem may be
deeper than simply fixing stored procedures. Is the database
normalized? If not, you may be looking at a complete re-write. I've
seen those kind of apps before in my career, and I've never seen one
yet whose underlying design flaws could be overcome by re-writing code
later. If you determine that the design is the problem, then get
whoever is paying the bills to commit to a re-write now before they
sink more money into a lost cause.
One other cause of deadlocks is not accessing resources in the same
order (process1 starts a transaction and grabs an exclusive lock on
resource1, process2 starts a transaction and grabs an exclusive lock
on resource2. Process 1 needs resource 2 to complete--process 2 needs
resource 1 to complete). That, at least, is fixable in your stored
procedure code.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 30 May 2002 08:16:28 -0700, Jeff Schaefer
>I have the "pleasure" of removing the "with readuncommitted" hint from
>dozens of stored procedures in a database. The client had a previous
>developer who added that query hint as a way to deal with resource
>contention issues. Queries were timing out and deadlocking. So, rather
>than optimizing the queries and taking other reasonable and
>well-documented steps to eliminate blocking and dead locking, the
>previous developer simply added "with readuncommitted" to *every* select
>statement in each of dozens of stored procedures. Now, all kinds of odd
>behaviour is being observed. I have now been given the task of fixing
>things.
>My questions:
>1. What would be the safest way to proceed - simply remove all the
>hundreds of readuncommitted hints at once and see what happens? I am
>inclined to remove them from logically related subsets of the queries,
>monitor performance, and then proceed with the next subset of queries.
>2. Other than looking for deadlocking, blocking and timeouts, what are
>some other beneficial ways to monitor the effects of my changes? What
>should I be looking for?
>3. Any other suggestions would be greatly appreciated.
>Thanks in advance...
by