This week's book giveaway is in the Android forum.
We're giving away four copies of Head First Android and have Dawn & David Griffiths on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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

Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Lot of locks getting created in SQL Server" Watch "Lot of locks getting created in SQL Server" New topic

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 ???

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:
subject: Lot of locks getting created in SQL Server
jQuery in Action, 3rd edition