W
William Ryan
Out of nowhere, our primary database recently started seeing deadlocks on a
regular basis. For over a year, I may have seen 10 of them, and then about
a month ago we started seeing between 4-15 a day on a regular basis.
Nothing about the DB schema had changed and looking through source safe,
nothing notable about our DataAccess layer had changed. However, we did add
some code incrementally that called our DataAccess layer. But there were no
transactions whatsoever involved, before or after these extra calls. About
the same time, we started seeing multiple Command Timeouts as well. So I
verified that Index fragmentation wasn't a problem ran a trace on many of
the problem queries and everythign looked fine. However, some weird stuff
did pop up. If I ran the same query as the deadlock victim a few minutes
later, most of the time it'd deadlock again the first time I ran it.
Thereafter it would run in under a second.
So, my two questions are this:
1) I think I remember someone mentioning that DataReaders use RowLocks as
they iterate the record set. Is that true?
2) Only two out of a few hundred queries were ever involved in the
deadlock, so just to try a temp fix I used Optimizer Hints (NO LOCK) on them
and the problem went away.
I'm well aware of the potential problems associated with NO LOCK, but I
think in this case it's not a big deal. BUT, is this a bandaid for a
problem or a legitimate fix? Since there weren't any transactions per se,
I'm thinking that long running SELCT Statement implemented via a DA.FILL
(which uses a datareader behind the scenes, right?) could be causing it if
the successive reads are implemented as a transaction. Since no
transactions are being used at all, and only two queries (both selects) ever
show up in the error messages, i'm guessing this is the problem.
Any ideas?
--
Cordially,
W.G. Ryan
(e-mail address removed)
www.devbuzz.com
www.knowdotnet.com
regular basis. For over a year, I may have seen 10 of them, and then about
a month ago we started seeing between 4-15 a day on a regular basis.
Nothing about the DB schema had changed and looking through source safe,
nothing notable about our DataAccess layer had changed. However, we did add
some code incrementally that called our DataAccess layer. But there were no
transactions whatsoever involved, before or after these extra calls. About
the same time, we started seeing multiple Command Timeouts as well. So I
verified that Index fragmentation wasn't a problem ran a trace on many of
the problem queries and everythign looked fine. However, some weird stuff
did pop up. If I ran the same query as the deadlock victim a few minutes
later, most of the time it'd deadlock again the first time I ran it.
Thereafter it would run in under a second.
So, my two questions are this:
1) I think I remember someone mentioning that DataReaders use RowLocks as
they iterate the record set. Is that true?
2) Only two out of a few hundred queries were ever involved in the
deadlock, so just to try a temp fix I used Optimizer Hints (NO LOCK) on them
and the problem went away.
I'm well aware of the potential problems associated with NO LOCK, but I
think in this case it's not a big deal. BUT, is this a bandaid for a
problem or a legitimate fix? Since there weren't any transactions per se,
I'm thinking that long running SELCT Statement implemented via a DA.FILL
(which uses a datareader behind the scenes, right?) could be causing it if
the successive reads are implemented as a transaction. Since no
transactions are being used at all, and only two queries (both selects) ever
show up in the error messages, i'm guessing this is the problem.
Any ideas?
--
Cordially,
W.G. Ryan
(e-mail address removed)
www.devbuzz.com
www.knowdotnet.com