How To: Remove lock from DATABASECHANGELOGLOCK table?
Overview
When a Datical/Liquibase Enterprise/Liquibase Business deployment process is aborted or killed before it finishes, its database process lock table (DATABASECHANGELOGLOCK) is left with a lock record that prevents subsequent packaging or deployment jobs from being able to run. In order to allow future processes to proceed, the table needs to be cleared out by manually updating the column to 0 (zero). This can happen with any of the supported database platforms.
If the target is an Oracle database the DATICAL_SPERRORLOG table might also be held open. The DATICAL_SPERRORLOG table is used to capture output for scripts executed using sqlplus during a deployment.
Newer versions of Datical have better ways of handling the locks, so we recommend upgrading to Datical DB version 7.3 or higher for the smoothest handling of the locks. Please see below for different instructions for different versions of Datical.
Sample Error
Below is sample text you may see in the Deploy Report if there is a lock on the databasechangeloglock table:
An error occurred deploying to '[dbDef]': liquibase.exception.LockException: Could not acquire change log lock. Currently locked by [user] ([user_ip]) since 2/27/20 9:44 AM
Hammer Command for releasing DATABASECHANGELOGLOCK
There is a hammer command to release the lock. The instructions for using releaseLocks are different for older versions of Datical (7.0 and lower), or version 7.2, or newer versions of Datical (7.3 and higher).
Instructions for Datical versions 7.3 and higher:
The instructions for 7.3 and higher are simpler, because the hammer command works for both the DATABASECHANGELOGLOCK table and the Oracle DATICAL_SPERRORLOG table. We recommend upgrading to 7.3 or higher if you sometimes need to interrupt deploys and then use the releaseLocks command before running another deploy.
hammer releaseLocks <dbDef>
Instructions for Datical version 7.2:
With version 7.2 the releaseLocks command no longer requires a separate properties file for drivers.
hammer releaseLocks <dbDef>
If you are using Oracle with Datical version 7.2 and you need to manually terminate a packager job or a deploy job, the DATICAL_SPERRORLOG table may be held open on your Datical tracking schema. You may need to disconnect the session for the user that runs Datical. The DATICAL_SPERRORLOG table is used to capture output for scripts executed using sqlplus during an Oracle deployment.
Here is how you can disconnect the session:
- Run a query to find the process
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id;
- Disconnect the session with the following statement.
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
Instructions for Datical versions 7.0 and lower:
The instructions for 7.0 and lower requires a daticaldb.properties file to indicate the path to the drivers.
hammer groovy releaseLocks.groovy [dbDef]
NOTE: You will need to specify path to your driver (jar file) in a new file called daticaldb.properties file within your project directory. Place the file in the same directory as the datical.project. Here's how we recommend setting up your daticaldb.properties file. Uncomment the relevant line before using:
If you are using Oracle with Datical version 7.0 and you need to manually terminate a packager job or a deploy job, the DATICAL_SPERRORLOG table may be held open on your Datical tracking schema. You may need to disconnect the session for the user that runs Datical. The DATICAL_SPERRORLOG table is used to capture output for scripts executed using sqlplus during an Oracle deployment.
Here is how you can disconnect the session:
- Run a query to find the process
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id;
- Disconnect the session with the following statement.
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
Known issue with Oracle: When running this command to clear locks with Datical versions 7.0 or lower in an Oracle database you may see the following warning message relating to "MBeans":
Alternate Solution for releasing DATABASECHANGELOGLOCK
Update the DATABASECHANGELOGLOCK table manually:
UPDATE DATABASECHANGELOGLOCK SET LOCKED=0;
Note that if you update the DATABASECHANGELOGLOCK table manually, for Oracle you may also need to disconnect the session for the user that runs Datical. The DATICAL_SPERRORLOG table is used to capture output for scripts executed using sqlplus during an Oracle deployment.
Here is how you can disconnect the session:
- Run a query to find the process
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id;
- Disconnect the session with the following statement.
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
Copyright © Datical 2012-2020 - Proprietary and Confidential