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 ???
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.
Joined: Apr 25, 2006
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.
subject: Lot of locks getting created in SQL Server