/
How To: Remove lock from DATABASECHANGELOGLOCK table?

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.



releaseLocks
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.  



releaseLocks
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.  

releaseLocks.groovy was deprecated in v8.3.



releaseLocks.groovy
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:

daticaldb.properties
## Oracle driver (Linux) #drivers=/opt/datical/DaticalDB/repl/plugins/com.datical.db.drivers.oracle.common_1.0.12.jar ## Oracle driver (Windows) #drivers=C:\\Users\\Administrator\\DaticalDB\\repl\\plugins\\com.datical.db.drivers.oracle.win_x86_64_1.0.12.jar ## MS SQL Server driver #drivers=C:\\Users\\Administrator\\DaticalDB\\repl\\plugins\\com.datical.db.drivers.mssql_1.0.12.jar



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":

Warning when running releaseLocks against an Oracle database
WARNING: Error while registering Oracle JDBC Diagnosability MBean. java.security.AccessControlException: access denied ("javax.management.MBeanTrustPermission" "register") at java.security.AccessControlContext.checkPermission(AccessControlContext.java:472) at java.lang.SecurityManager.checkPermission(SecurityManager.java:585) at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.checkMBeanTrustPermission(DefaultMBeanServerInterceptor.java:1848) at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerMBean(DefaultMBeanServerInterceptor.java:322) at com.sun.jmx.mbeanserver.JmxMBeanServer.registerMBean(JmxMBeanServer.java:522) at oracle.jdbc.driver.OracleDriver.registerMBeans(OracleDriver.java:416) at oracle.jdbc.driver.OracleDriver$1.run(OracleDriver.java:248) at java.security.AccessController.doPrivileged(Native Method) at oracle.jdbc.driver.OracleDriver.<clinit>(OracleDriver.java:244) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:348) at liquibase.database.DatabaseFactory.openConnection(DatabaseFactory.java:197) at liquibase.database.DatabaseFactory.openDatabase(DatabaseFactory.java:151) at liquibase.integration.commandline.CommandLineUtils.createDatabaseObject(CommandLineUtils.java:86) at liquibase.integration.commandline.Main.doMigration(Main.java:960) at liquibase.integration.commandline.Main.run(Main.java:190) at liquibase.integration.commandline.Main.main(Main.java:105) at com.datical.hammer.repl.LiquibaseCliCommand.runLiquibase(LiquibaseCliCommand.java:271) at com.datical.hammer.repl.LiquibaseCliCommand.runLiquibase(LiquibaseCliCommand.java:211) at com.datical.hammer.repl.LiquibaseCliCommand.run(LiquibaseCliCommand.java:45) at com.datical.hammer.repl.DaticalDBHelper.executeCommand(DaticalDBHelper.java:3151) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoCachedMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:233) at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:56) at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:141) at releaseLocks.run(releaseLocks.groovy:12) at groovy.util.GroovyScriptEngine.run(GroovyScriptEngine.java:605) at com.datical.hammer.repl.GroovyScriptCommand.run(GroovyScriptCommand.java:255) at com.datical.hammer.repl.Main.runCommandFromArgs(Main.java:437) at com.datical.hammer.repl.Main.main(Main.java:94) at com.datical.hammer.repl.app.Application.start(Application.java:19) at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110) at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:344) at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:622) at org.eclipse.equinox.launcher.Main.basicRun(Main.java:577) at org.eclipse.equinox.launcher.Main.run(Main.java:1410) at org.eclipse.equinox.launcher.Main.main(Main.java:1386) Successfully released all database change log locks for DATICAL_USER@jdbc:oracle:thin:@//demo-db1-rhel6.datical.net:1521/CAPS_REF1.datical.net Liquibase 'releaseLocks' Successful





Alternate Solution for releasing DATABASECHANGELOGLOCK

Update the DATABASECHANGELOGLOCK table manually:

SQL
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;

 

Assigning GRANT ALTER SYSTEM so that Liquibase can kill the session

When using the releaseLocks command, Liquibase does attempt to kill the session. If the Liquibase user does not have access to kill sessions you will see an error as below:

Table: DATICAL_SPERRORLOG can't be removed because resource is busy by sessions (SID, SERIAL): nnnn,nnnnn Sessions can't be removed because of insufficient privileges. Try to [GRANT ALTER SYSTEM TO <liquibase_user>]

When this error occurs, you can either:

  1. Add this permission for the user: GRANT ALTER SYSTEM TO <liquibase_user> and run the releaseLocks command again.

  2. Run the following statements:

    1. "SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME=<liquibase_user> AND LOWER(PROGRAM) like '%sqlplus%'"; alter system kill session '<sid from select query>,<serial from select query>'

b. Run the releaseLocks command again.

Killing a Session with AWS Oracle RDS

When using AWS and Oracle RDS, it is not possible to grant the ALTER SYSTEM privilege to the Liquibase user.

Instead AWS provides a utility for killing sessions. See Terminating a session - Amazon Relational Database Service.

  1. Find the sid and serial number from either the error message or by running this query:

    SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = '<liquibase_user>';
  2. Kill the session with:

    BEGIN rdsadmin.rdsadmin_util.kill( sid => sid, serial => serial_number, method => 'PROCESS'); END; / -- Example: -- begin -- rdsadmin.rdsadmin_util.kill(40,62092); -- end;
  3. Run the releaseLocks command again.

Related content

Copyright © Datical 2012-2020 - Proprietary and Confidential