locks, blocks and crashes

We have a multi-user (~300 users) client-server application which recently began to crash in a most emphatic manner. The system would start to slow down, one user's display would "freeze", and then all other users would be dead within minutes. Not a very productive way to run a business.

By "freeze" I mean that the next form would not fully display and the keyboard and mouse would not get a response. We could see through Enterprise Manager that there were many blocks.

All users are doing read/write to the same tables, but not using the same keysets -- no two users will have the same set of key variables at a time.

There are 3 or 4 tables which are most commonly used. One of the tables has a couple million records, the others are a couple hundred thousand or less.

We have resolved the problem for now by adding WITH (NOLOCK) and WITH (ROWLOCK) on select and update/insert queries, respectively. For the last 48 hours, we haven't had any detectable blocks and no crashes at all.

However, I would like to understand more about lock escalation, blocking, and other people's experiences with such stuff for large systems.

Thanks.
--r

Running sp_who2 or sp_who will show a list of active connections to your server. There will be several columns, but what you're really interested in is the blkby column. If the value is 0, it indicates that the connection isn't blocked, but if there is a number here, it indicates that the SQL statement is being blocked by another SQL statement from another connection. The number indicates the spid of the SQL statement that caused the block (the spid is the first column in the result set). All you have to do is trace the numbers, till you find the spid of the SQL statement that started the block (it will have 0 in the blkby column naturally).

You can find the exact statement that is running for that spid by typing
dbcc inputbuffer(xxx)
where xxx is the spid of that SQL connection.

Thanks. sp_who2 gives lots of good info -- do you know if the same stuff is available from systables? It would be nice to be able to query with conditions.

Can't try dbcc inputbuffer, unfortunately, because I'm dbo not dba. Only our server staff are dba, but the dbo's get to solve the problems

Appreciate the tip -- it will be helpful.

BTW, we have not had any crashes on the problem-child db since putting in WITH (ROWLOCK), over a week ago, on all updates and inserts that would affect a small number of records.

--r

I had the same problem with SQL Server 2K. We tried using nolock and rowlock, but MS-SQL just ignored it and proceeded with massive numbers of escalating locks which killed the system with deadlocks due to the escalations. We got MS involved and their only advice was to use retry code for transactions!! We finally got things to work OK by using tablock on everything...sounds crazy, but MS was NO help. I would also be interested to hear other's experiences with this problem.

In case anyone is interested, what appeared to be happening was Process1 would take a row lock on Row1, then Process2 would take a row lock on Row2, then Process1 would want to escalate to a table lock, but was blocked by Process2's lock on Row2. Process2 then wanted to escalate to a table lock, but couldn't get it because it was blocked by Process1's lock on Row1. Process1 blocking Process2 when Process2 is blocking Process1 equals a deadlock, and we got lots of them because we couldn't stop the crazy, unnecessary escalations, and MS couldn't stop them either. Go figure.