Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Lot of locks getting created in SQL Server

 
Saumya Nair
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all,

I have an application developed in jsp.Using SQL Server as the backend.But sometimes the application creates a lot of lock in the database, and due to this blocking my application srops working.I checked the database connection.Each function being called,opens the connection and also closses it after the process.Not able to trace the reason behind these DB blockings.Can anyone suggest what can be the possible reason ???



Regards,
Saumya
 
Mattias Arthursson
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are a multitude of different reasons why there might be locks held in the database. First of all, are you absolutely sure that all resources are properly closed, no matter what happens? If an error is encountered, do you still close all resources? Always?

Another possible reason might be that you are running queries that take a long time to execute. For a reasonable amount of data with a fairly complicated query it's quite possible that the queries are running for a couple of minutes (if your indexes are not set up properly). If not explicitly stated otherwise the query will hold a lock on the table until it's finished, preventing updates in the meantime. In SQL server you can specify that no locks should be held by a query using the "WITH(NOLOCK)" option.
 
Saumya Nair
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i checked the files.. All the connections are properly handled. Tried the NOLOCK option too... But no result


any other solution to avoid this problem ??? Locks gets created from the login page itself. Even for all select n inserts that affects only a few rows.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic