• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL LOCK

 
Ranch Hand
Posts: 136
Eclipse IDE Chrome Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Arun Kumar
Ranch Hand
Posts: 136
Eclipse IDE Chrome Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 136
Eclipse IDE Chrome Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
reply
    Bookmark Topic Watch Topic
  • New Topic