File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL LOCK Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL LOCK" Watch "SQL LOCK" New topic
Author

SQL LOCK

Arun Kumar
Ranch Hand

Joined: Jan 21, 2005
Posts: 129

I have rather a strange problem when i try to execute a stored procedure from within my application sql server applies a lock on the table and my program stops there

no exception nothing it simply stops

but when i try to execute the same stored proc from a normal javaclass using same connection class it works

anybody has any idea why sql is locking the table -- its just a select statement which retuns two records
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

What makes you think you are getting a table lock? Its true that SQL Server does aquire a lock when issuing a select statement, but this is a shared lock, so should only block delete statements. How did you determine a lock is your problem?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Arun Kumar
Ranch Hand

Joined: Jan 21, 2005
Posts: 129

well what happens is till

the line executeQuery() my program works fine but in tha line the program stops or is stuck

what i did is from query analyser i executed sp_lock and found ann object to be in waiting state

and using spid of that object i selected the object name and using

sp_who spid i found out that the call that was waiting was from my machine itself

exec sp_lock
SELECT object_name(1902629821)
EXEC sp_who 55

this is how i concluded that it was lock am i wrong
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Yes, it sounds like you have diagnosed this correctly (I had to ask - people oftem mistakenly assume something is a lock without checking ).

You say the process that is causing the block is in the waiting state? Of the top of my head, I think this means that SQL Server is waiting for the client to do something, rather than it is waiting for another resource it manages to be freed (this is worth double-checking, since I only vaguely remember all the various process states). The only time I've seen similar behaviour is when I'm debugging (so pausing in a debug session is also causing a block in SQL Server) or when a client transaction is not properly ended. Are you using JTA with your applciation?
[ February 15, 2007: Message edited by: Paul Sturrock ]
Arun Kumar
Ranch Hand

Joined: Jan 21, 2005
Posts: 129

Thanks paul thaks for your time and help

I was maintaining na application and an aditional feature had to be developed which was making the database call

the whole process was a transaction and a connection was created alreday with autocommit set to false and i was trying to create a new connection and this created the lock situation

I solved the problem by using the same connection again
once again thanks for the time
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL LOCK
 
Similar Threads
Error in getting data from database
Transaction or Table Locking?
problem insering a record in database
Record Set closing
Insert into db does not work, no errors doh !?