Extreme number of locks takes down server

[tombos]

We've experienced a situation on more than one occasion where a given process on our SQL Server ends up locking 1000-1200 different records and/or pages in a database. The last T-SQL statement that executed was SELECT * FROM vw_ViewName.

That statement was issued from a VB6 application using ADO access techniques and a READONLY recordset. We can find no reason that any locks should be issued since the client-side cursor is a readonly cursor.

Unfortunately, when we try to kill the rogue process, nothing happens, and the process never goes away or lets go of its locks. We end up having to reboot the server to get rid of it.

Question #1: Does this make sense to anyone?

Question #2: Is there a way to issue a SELECT statement so that it performs no locking? The WITH NOLOCK clause doesn't seem to be exactly what I'm after (I don't think).

Question #3: Is there a way to kill the process when it doesn't "want" to be killed. Right-clicking and picking KILL PROCESS has no effect.

 

[JNC73]

First of all
When you change a record in a table it is possible that de Delete and Insert trigger are executed and all the checks that are made there will be executed as one transaction. This means that when these triggers take a long time to run even a select statement may not read the content of all the tables. This is what your problem is I think.

To solve it make sure your triggers and SQL statements only update one record at a time and not a subset of records of one table.
So use

Update <table> set field1=&quot;aaa&quot; where id = 1

and NOT

Update <table> set field1=&quot;aaa&quot; where field2 like &quot;bbb&quot;

The first statement will only lock 1 page of your table and the second will lock all the pages of the table so no one can access the data of the table untill you are finished with your query.

Hope this helps