Tables get Locked

[Bardolator]

Hello

I have a stand alone Java application that uses SQL Server 2000 on the
same machine. Sometimes, when we run the applications and follow a
certain steps, the SQL server locks some of the table. At that point of
time, if we try to query Select * on those table, in Query Analyzer, it
just keep spinning and doesn't return anything. That tells me that the
tables are locked. If I close the application then it frees up the
connection and those tables are accessible again.
My question is how do I find out how and what query/update causes the
tables to be locked. Is there a tool (some kind of a profiler) that can
tell me what was the last insert/update/delete quesry was run before it
got locked?

Please help me.

[rudianus]

Yes, "sql profiler" comes with sql 2000. You can also look at the
current activity by executing the procedures sp_who2 and sp_lock.
sp_who2 shows the connections and if one is blocked by another process,
column "BlkBy". and sp_lock is more involved and will tell you the type
of locks and on what tables.
Just start sql profiler and then follow those "certain steps" you
mentioned to find the locking code.

-scott

[Erland Sommarskog]

Bardolator (Ba********@gmail.com) writes:

I have a stand alone Java application that uses SQL Server 2000 on the
same machine. Sometimes, when we run the applications and follow a
certain steps, the SQL server locks some of the table. At that point of
time, if we try to query Select * on those table, in Query Analyzer, it
just keep spinning and doesn't return anything. That tells me that the
tables are locked. If I close the application then it frees up the
connection and those tables are accessible again.
My question is how do I find out how and what query/update causes the
tables to be locked. Is there a tool (some kind of a profiler) that can
tell me what was the last insert/update/delete quesry was run before it
got locked?

For a convenient packaging of everything, you could try my aba_lockinfo,
see http://www.sommarskog.se/sqlutil/aba_lockinfo.html

But note that the problem may be more complex than just looking at
currently executing queries. Improper error handling or similar can
have left a process with a stray transaction.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp