Very Computer

Group,

I'm currently in a situation where I'm struggling to come to terms
with a strange locking problem in MS SQL Server 6.5. I'm getting quite
desperate and know not what to do. I would appreciate some suggestions
if you could spare the time.

Prerequisites:
(1) I'm not sa for the server, I do have dbo rights in some of the
databases on the server.
(2) There is no available source code for the application that is
working against the database.
(3) The application is an internet application with several
simultaneous users.

The application has performed fairly well over the past year, until
one month ago, when a locking problem started to appear for no
apparent reason. No major changes to the application was rolled out.

After investing some time - using sp_who, sp_who2, sp_lock, sp_lock2
and DBCC INPUTBUFFER(<spid>) manually and by convincing the DBA to run
a script that takes a snapshot on all locks every minute I can say
that I have a somewhat incomplete picture of what is happening.

For no particular reason sleeping processes are holding exclusive page
locks on both user database tables and system tables in tempdb. Those
processes doesn't seem to consume any CPU, they are just holding their
locks for about 5-20 minutes and then mysteriously disappearing. The
user tables that the locks are held against together with the tempdb
system tables are almost always the same.

Since exclusive locks are held on tempdb system tables no one is able
to access the application during time the locks are held, to much
grief.

The following characteristics are true for these lingering locks:
(1) The exist for about 5 to 20 minutes.
(2) They appear to be sleeping.
(3) Nothing can be seen when the DBCC INPUTBUFFER command is issued.
<I have dbo right to the database in question>

I find number (3) particularly disturbing since there has to be some
underlying SQL-statments executed in order to create exclusive locks
on the tables in question. At least, that is what I think.

The makers of the application can't find anything wrong with the
transactions that are updating the user tables that are locked
together with the tempdb tables.

I have investigated the SELECT INTO and INSERT INTO EXECUTE options
for tempdb locking, but none of those statements are issued accoring
to the makers of the application. Since this is a known problem, the
makers use permanent temporary tables, i.e. temporary tables that
always exist.

A question related to this - how does deadlocking appear in the
database? If I have understood deadlocking in 6.5 correctly, a
deadlock victim is selected almost immediately and the victim
terminated, allowing SQL Server to continue with the next task at
hand.

Could the victim of a deadlock still linger around or what can happen?

My next moves will be to have SQL Trace logg all the SQL sent to the
database, but IIRC this will slow down perfomance quite a bit.

Parallell to this I'll try to install some of the software in debug
mode, that will allow us to logg some of the outgoing SQL from the
application.

Does anyone have any input on this issue?
Care to make any suggestions?
Could someone please pity me? :-)

I have read all the sql-server related groups as far back as my USENET
server stores messages (a month or so), the SQL-server FAQ,
www.swynk.com, Inside Microsoft SQL Server 7.0 (I know it's the wrong
version), BOL, sqlserver.superexpert.com, someones *large* archive of
SQL Server discussions, and I haven't found something that is
applicable to my particular problems.

HELP!

Best regards,

Andreas

I have seen a problem like the one you are describing.

I my case the problem is with the response times across the WAN between the
Client and Server.

If there is a problem with the WAN connection(Ex. Frame Chk errs, CRC errs,
ect....) then the server and clients response times slow down.

When a user is in process of accessing a table that generates a temp lock,
and the response time is slow, the lock does not clear up fast enough. When
a second user accesses the same table/page of data, it then  creates a
blocking lock situation.

From this point it snow balls if the blocking SPID is not terminated. This
will at some point bring the server to a stand still with the same kind of
results you have described.

Once the WAN connection is cleaned up, everything works as normal.

Hope this gives you some ideals of what to look for that might help clear
the problem.

On Mon, 17 Jul 2000 14:16:05 -0500, Larry Sanders

Hm... In my case the Webserver and the database server are located
physcially next to each other connected by 100 Mbit Ethernet cards.

Is it the failure to fetch rows in a quick fashion that could cause
this behavior?

Quote:>Once the WAN connection is cleaned up, everything works as normal.

>Hope this gives you some ideals of what to look for that might help clear
>the problem.

This sounds interesting. The application has complained about problems
establishing a connection to the database server.

I haven't thought in those terms before.

Thank you for your input!

/ Andreas

---
Andreas

        Hub, switched, or what?

        If it's a hub, what else is in the same collision domain?