[#CORE-3805] Object in use by who? need to know which connection has an object in use ( ie. stored procedure )

Firebird Core

Object in use by who? need to know which connection has an object in use ( ie. stored procedure )

Created: 31/Mar/12 04:02 AM Updated: 24/Dec/12 08:33 AM

Component/s:

Engine

Affects Version/s:

None

Fix Version/s:

None

Time Tracking:

Not Specified

Environment:

All

Planning Status:

Unspecified

Description

« Hide

We are constantly updating our catalogue of stored procedures. However process keeps failing as we get "Object in use" errors. We need to know who has these procedures open so we can disconnect them ( read fire them ) and continue on our merry way. We would like the monitor tables to tell us what nasty people have our objects in use.

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Sean Leyne added a comment - 31/Mar/12 06:42 PM

If this is a common occurance, then it seems appropriate that you use the "shutdown" database function to prevent user connections, while you are applying schema updates.

That way you guarantee that the updates will be successful, without the termination/severance costs related to firing staff and the effort in hiring new staff. ;-)

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 24/Dec/12 08:33 AM

I tend to agree with Sean. If you want to freeze all activity in order to perform a schema upgrade, database shutdown is a proper way to go. Otherwise, monitoring tables will not help much, because as soon as you get a list of "blockers" other sessions may also take a lock on these procedures, so your "kill list" will get outdated immediately.

Also, beware that you need to use WAIT mode for your DML transaction altering the schema, it nearly zeroes a possibility to get "object in use" errors, at least for procedures/triggers.