Best practice to handle locked database objects

1

Hi,
sometimes during deployment a changeset fails when an object is locked and the deployment therefore cannot happen at that time.
I was wondering if there is a best practice how to handle these occasions and get a successful deployment instead.
Thats probably in general the topic of online/offline deplyoment strategies.
e.g.

  • could I use preconditions or else to check for existing locks and kill sessions if needed
  • could I set a number of automatic retries
  • or else?
2

In Oracle we add the following in an alwaysRun changeset at the top of the changelog. That will ensure that all other statements wait for 60 seconds before they eventually fail, and we have hardly ever experienced problems due to such locks.

I don’t know if similar methods are available for other databases?

ALTER SESSION SET ddl_lock_timeout=60;

3

Hi @roberthorn,

We don’t have any best practice for lock handling. What we do is we acquire lock before proceding with an update command and then release it at the end of the execution. If a changeset deployement fails then execution will fail (unless we have a different configuration for onFail property, like for example CONTINUE), and then lock will be released.

Thanks,
Daniel.