| MrMcHenry 2005-03-30, 7:03 pm |
| We can reproduce this problem now with just QA and EM with very simple
queries and a modest sized database. Seems there is no solution but to do
dirty reads to prevent the locks occuring in the first place (thanks MS for
that suggestion). KB323630 was interesting. Looks very much like KB250345
(SQL7) is a problem in SQL2K too! Haven't tried later versions of SQL Server
yet.
Hope someone finds this useful. We wasted ages on this.
Ian
"MrMcHenry" <MrMcHenry@dial.pipex.com> wrote in message
news:42321931$0$2674
6$cc9e4d1f@news.dial.pipex.com...
> We have a production database that has worked fine in many installations.
> Several of our sites have larger databases with more activity and have no
> problems. One rouge site has recently encountered a blocking problem that
> we cannot explain.
>
> The problem site has about 20 clients which are XPsp2 running a C++
> client. The problem always seem to be caused by certain specific clients.
> These clients opens a snapshot recordset using ODBC. The query used is
> nothing fancy, just a straightforward join across 4 tables. The query can
> return up to about 8k records and the clients tend to leave the recordset
> open for a period of time. Some of the tables involved get regular updates
> from other machines, sometimes several per second.
>
> On this particular system we get a blocking situation where the updates
> are blocked by the clients that have opened these recordsets. So far we
> have used sp_lock2 and Erland's excellent aba_lockinfo to glean this
> information so far. I have the output from these sp's if anyone's
> interested and some jpegs from EM.
>
> The blocking spid appears to be waiting on network I/O (waittype =
> 0x0800). The blocking spid still has IS TAB locks on the tables the
> updates are trying to access. The blocking spid has an eXclusive PAG lock
> on tempdb - everything grinds to a halt. Sometimes the block appears to
> clear itself. Mostly the users can't wait for that and someone has to kill
> the errant spid.
>
> Server is SQL2KSP3a (have recently also tried build 880) running on W2K.
> We've tried reinstalling the entire server and we still have the problem.
> We've tried totally disabling the client's XPSP2 firewalls to no effect.
> The only solution so far has been to modify the query used to populate the
> recordsets to do dirty reads (NOLOCK) , but we're not happen with the
> solution as we want to understand why the problem occurs in the first
> place.
>
> Any suggestions/explanations greatly appreciated.
>
> Ian
>
>
|