Another one about blocking

This is weird, but I hope someone can help me out:
MSSQLServer 2000, SP 2, W2K Server
Dedicated server with one DB, 60 concurrent users
Locktimeout is set
The DB is accesses via my app only.
About once a week the DB runs into a blocking situation, wich locks up all
other users. Although the lock timeout is set, no timeout occurs. I
monitored the DB for the blockees and got grip of the blocking statement:
select count(*) as c FROM TBLMWTRACE M WITH (NOLOCK) WHERE m.datum = Convert(datetime, '20031120', 112) and ((GELESEN IS NULL) or (GELESEN = 'N')) AND (M.HAUSCD = 'BRI')
I researched my app and I am quite sure, that there are no transactions left
open. If lengthy select statements were the cause, they would have been
captured by the monitor.
My assumption is, that if SQLServer gets under load, he starts issuing
x-locks without releasing them. But this can´t be true, of course.
What can I do to track down what the causes the blocks?
Thanks BerndHi Bernd !
Seems, that the option "with (NOLOCK)" does not work as expected. This
option is identical to transaction mode set to uncommmitted, that means,
that no locks are set, incoinsitencies may occurr. Have you considered
the "lock escalation" ? SQL Server 2000 automatically switches to table
lock, whenever too many users try to access a database.
My advice. Use databases with MVTO, MVRO, your problems will disappear.
look for it in google groups.
regards, Guido Stepken
Bernd Maierhofer (dato) wrote:
> This is weird, but I hope someone can help me out:
> MSSQLServer 2000, SP 2, W2K Server
> Dedicated server with one DB, 60 concurrent users
> Locktimeout is set
> The DB is accesses via my app only.
> About once a week the DB runs into a blocking situation, wich locks up all
> other users. Although the lock timeout is set, no timeout occurs. I
> monitored the DB for the blockees and got grip of the blocking statement:
> select count(*) as c FROM TBLMWTRACE M WITH (NOLOCK) WHERE m.datum => Convert(datetime, '20031120', 112) and ((GELESEN IS NULL) or (GELESEN => 'N')) AND (M.HAUSCD = 'BRI')
> I researched my app and I am quite sure, that there are no transactions left
> open. If lengthy select statements were the cause, they would have been
> captured by the monitor.
> My assumption is, that if SQLServer gets under load, he starts issuing
> x-locks without releasing them. But this can´t be true, of course.
> What can I do to track down what the causes the blocks?
> Thanks Bernd
>