Issue Details (XML | Word | Printable)

Key: CORE-3805
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Robert Gilland
Votes: 1
Watchers: 1

If you were logged in you would be able to see more operations.
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: Ascending order - Click to sort in descending order
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. ;-)

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.