September 9, 2004 at 1:09 pm
Our production system is a quad-processor server with 2gb of ram dedicated to SQL. It runs SQL7 in 6.0 emulation mode due to the old software package we use.
Our web server is running SQL 2k with 3gb of ram and quad processors. Connections are TCP/IP over 10/100 Ethernet (soon to be gigabit).
A program on the web server, written in VB6 and ADO, provides detailed data from our production system to our intranet. Another similar one is written in VB.NET and ADO.NET.
Occasionally, Enterprise Manager on our production system is unable to provide process activity details, returning an error showing a timeout waiting for locks.
Our diagnostic program has shown processes running from the web server with Shared table locks on some of our critical tables. I am assured that
the program is read only (except for temp tables I noticed), so why is it locking tables? I have assumed that the query comes to the production server without optimization, and because of the way it is written,
it isn't optimized there and so starts doing table locks while it retrieves data.
I have thought of having the web server program and its stored procedures changed to do SELECT .... WITH NO LOCK to try any ease this problem. DTS would take care of update
transactions, but these are inquiry only.
I am looking for any ideas to get out of this hole.
September 10, 2004 at 3:09 am
"Readers block writers"
Try SELECT * FROM ATable (TABLOCKX) and you lock your server forever.
You can write near each table (NOLOCK) or better write SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED on start of transaction
September 10, 2004 at 2:11 pm
Thanks for the response.
Unfortunately, the person who coded the queries does not use transactions becuase they are convinced that they are only reading data and so will not cause any problems. I guess I'll just have to change their code to NOLOCKs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply