Resource Locks not being released

[simonm]

I'm using SQL Server 2000 and am having the intermittant problem of resources getting locked and not being released after they have been finished with.

Obviously, this could happen if an explicit transaction was started and not committed/rolled back but does anybody know of any other ways in which this would happen?

[jim mcnamara]

If someone goes into an update and then goes to lunch without exiting, you get the same effect. If you do a select for update.
Users also start up mutliple screens. Then minimize one and forget it. Don't rule out users.

It depends on how you design your screens.

Do you completely shutdown all DB connections at night, and then still have locked resources? Then it's possible that an app has terminated abnormally, leaving the locks behind. And the user never mentions it because.... who knows.

[Arc]

You should always have your Apps write to an error log file when it shuts down abnormally or pretty much when any error occurs. This way you can read the error log any time you want and see if something is going on there first.


Never depend on the user to report errors

[simonm]

Well, we do have automatic error logging but if we shut down the program everytime an error occured, they would spend more time logging into it than using it!

However, there does not appear to be any run-time error occuring with these resources getting locked so we can't actually see why or when it happens.

As for updating, that can't be an issue since we either use stored procedures to update the database or disconnected recordsets in batch update mode.