wood burning stoves 2.0*
The moose likes JDBC and the fly likes Lot of locks getting created in SQL Server Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Lot of locks getting created in SQL Server" Watch "Lot of locks getting created in SQL Server" New topic
Author

Lot of locks getting created in SQL Server

Saumya Nair
Ranch Hand

Joined: Apr 25, 2006
Posts: 34
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

Joined: Jul 26, 2004
Posts: 90
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.


Admit nothing. Blame everyone. Be bitter.
Saumya Nair
Ranch Hand

Joined: Apr 25, 2006
Posts: 34
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Lot of locks getting created in SQL Server
 
Similar Threads
No Current Row in the ResultSet when Migrating from SQL 2000 to 2005
Best JDBC drivers?
Optimal number of records in an Array passed to a Stored Procedure
Facing Big Problem with Sql Server 2000 Odbc Driver
I am facing a lot of problems with sql server 2000 odbc drivers.