469,904 Members | 2,486 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

Tables get Locked

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.

Jul 23 '05 #1
2 3077
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

Jul 23 '05 #2
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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Jeff Roughgarden | last post: by
reply views Thread by Matthew Wells | last post: by
reply views Thread by CaptainMcBunnyTickle | last post: by
2 posts views Thread by Matthew Wells | last post: by
5 posts views Thread by breal | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.