Reputation: 5894

Liquibase lock - reasons?

I get this when running a lot of liquibase-scripts against a Oracle-server. SomeComputer is me.

Waiting for changelog lock....
Waiting for changelog lock....
Waiting for changelog lock....
Waiting for changelog lock....
Waiting for changelog lock....
Waiting for changelog lock....
Waiting for changelog lock....
Liquibase Update Failed: Could not acquire change log lock.  Currently locked by SomeComputer (192.168.15.X) since 2013-03-20 13:39
SEVERE 2013-03-20 16:59:liquibase: Could not acquire change log lock.  Currently locked by SomeComputer (192.168.15.X) since 2013-03-20 13:39
liquibase.exception.LockException: Could not acquire change log lock.  Currently locked by SomeComputer (192.168.15.X) since 2013-03-20 13:39
        at liquibase.lockservice.LockService.waitForLock(LockService.java:81)
        at liquibase.Liquibase.tag(Liquibase.java:507)
        at liquibase.integration.commandline.Main.doMigration(Main.java:643)
        at liquibase.integration.commandline.Main.main(Main.java:116)

Could it be that the number of simultaneous sessions/transactions are reached? Anyone has any ideas?

Upvotes: 423

Answers (13)

s.dragos

Reputation: 682

I spent 3 days trying to figure the root cause for this issue. I my case, I had problems running some Postgres backups that contained environments/session variables declaration using "SET".

Solution: Try and comment all session variable declaration commands. It solved the problem for me:

-- SET client_encoding = 'UTF8';
-- SET standard_conforming_strings = on;
-- SET client_min_messages = warning;

Config: Postgres 14.5; Liquibase 4.5 with Maven

Upvotes: 2

Ramya Musunuru

Reputation: 471

Default table for liquibase changelog is DATABASECHANGELOCK, however this can be overriden or set to a custom table by setting the below property.

in application.properties

database-change-log-lock-table=<SOME_CUSTOM_TABLE_NAME>

If the application has this property set then

UPDATE <SOME_CUSTOM_TABLE_NAME> SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1;

or delete the table

drop table <SOME_CUSTOM_TABLE_NAME>

Upvotes: 3

k_o_

Reputation: 6308

It is not mentioned which environment is used for executing Liquibase. In case it is Spring Boot 2 it is possible to extend liquibase.lockservice.StandardLockService without the need to run direct SQL statements which is much cleaner. E.g.:

/**
 * This class is enforcing to release the lock from the database.
 *
 */
 public class ForceReleaseLockService extends StandardLockService {

    @Override
    public int getPriority() {
        return super.getPriority()+1;
    }

    @Override
    public void waitForLock() throws LockException {
        try {
            super.forceReleaseLock();
        } catch (DatabaseException e) {
            throw new LockException("Could not enforce getting the lock.", e);
        }
        super.waitForLock();
    }
}

The code is enforcing the release of the lock. This can be useful in test setups where the release call might not get called in case of errors or when the debugging is aborted.

The class must be placed in the liquibase.ext package and will be picked up by the Spring Boot 2 auto configuration.

Liquibase 4

Note that the extension loading mechanism has changed in Liquibase 4.

Now a file under META-INF/services with the implemented full interface package name must be created and in this file all extension must be listed.

This could mean that in META-INF/services/liquibase.lockservice.LockService

This line must be added:

com.company.liquibase.impl.ForceReleaseLockService

I have not tried it, still using Liquibase 3, please edit and correct.

Upvotes: 16

Sprooose

Reputation: 514

I have previously just truncated the DatabaseChangeLogLock table when a lock remained after failure (and this generally still works for me).

However, this project (https://github.com/blagerweij/liquibase-sessionlock) now exists, which uses native database session locks for locking Liquibase when using Oracle, PostgreSQL and MySQL.

The JAR file can be dropped into an existing Liquibase 4.x+ application, and will be automatically detected by Liquibase. This will use session locks for locking.. which are automatically released if the application crashes or is shutdown whilst the lock is held. This will potentially stop the locked state from occuring (if you are using one of the supported databases)

Upvotes: 2

malek yahyaoui

Reputation: 409

If the solution posted by @Adrian didn't work, you may try to do the same in the table INTERFACE_DATABASECHANGELOGLOCK which contains additional informations regarding the lock.

UPDATE INTERFACE_DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1;

or simply clear the table

TRUNCATE TABLE INTERFACE_DATABASECHANGELOGLOCK

Upvotes: 0

Danny

Reputation: 171

Please let Liquibase handle its own tables. The correct thing to do, as user1434769 mentions, is to use Liquibase's releaseLocks command.

When using Gradle, this would be: gradlew releaseLocks

Upvotes: 3

e18r

Reputation: 8201

Edit june 2020

Don't follow this advice. It's caused trouble to many people over the years. It worked for me a long time ago and I posted it in good faith, but it's clearly not the way to do it. The DATABASECHANGELOCK table needs to have stuff in it, so it's a bad idea to just delete everything from it without dropping the table.

Leos Literak, for instance, followed these instructions and the server failed to start.

Original answer

It's possibly due to a killed liquibase process not releasing its lock on the DATABASECHANGELOGLOCK table. Then,

DELETE FROM DATABASECHANGELOGLOCK;

might help you.

Edit: @Adrian Ber's answer provides a better solution than this. Only do this if you have any problems doing his solution.

Upvotes: 81

g.momo

Reputation: 566

In postgres 12 I needed to use this command:

UPDATE DATABASECHANGELOGLOCK SET LOCKED=false, LOCKGRANTED=null, LOCKEDBY=null where ID=1;

Upvotes: 1

Adrian Ber

Reputation: 21390

Sometimes if the update application is abruptly stopped, then the lock remains stuck.

Then running

UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1;

against the database helps.

You may also need to replace LOCKED=0 with LOCKED=FALSE.

Or you can simply drop the DATABASECHANGELOGLOCK table, it will be recreated.

Upvotes: 921

Mykhaylo Adamovych

Reputation: 20986

You can safely delete the table manually or using query. It will be recreated automatically.

DROP TABLE DATABASECHANGELOGLOCK;

Upvotes: 13

Koushik Ravulapelli

Reputation: 1138

Sometimes truncating or dropping the table DATABASECHANGELOGLOCK doesn't work. I use PostgreSQL database and came across this issue a lot of times. What I do for solving is to rollback the prepared statements running in background for that database. Try to rollback all the prepared statements and try the liquibase changes again.

SQL:

SELECT gid FROM pg_prepared_xacts WHERE database='database_name';

If above statement returns any record, then rollback that prepared statement with following SQL statement.

ROLLBACK PREPARED 'gid_obtained_from_above_SQL';

Upvotes: 3

DarthPablo

Reputation: 156

I appreciate this wasn't the OP's issue, but I ran into this issue recently with a different cause. For reference, I was using the Liquibase Maven plugin (liquibase-maven-plugin:3.1.1) with SQL Server.

Anyway, I'd erroneously copied and pasted a SQL Server "use" statement into one of my scripts that switches databases, so liquibase was running and updating the DATABASECHANGELOGLOCK, acquiring the lock in the correct database, but then switching databases to apply the changes. Not only could I NOT see my changes or liquibase audit in the correct database, but of course, when I ran liquibase again, it couldn't acquire the lock, as the lock had been released in the "wrong" database, and so was still locked in the "correct" database. I'd have expected liquibase to check the lock was still applied before releasing it, and maybe that is a bug in liquibase (I haven't checked yet), but it may well be addressed in later versions! That said, I suppose it could be considered a feature!

Quite a bit of a schoolboy error, I know, but I raise it here in case anyone runs into the same problem!

Upvotes: 3

Peter

Reputation: 5894

The problem was the buggy implementation of SequenceExists in Liquibase. Since the changesets with these statements took a very long time and was accidently aborted. Then the next try executing the liquibase-scripts the lock was held.

  <changeSet author="user" id="123">
    <preConditions onFail="CONTINUE">
      <not><sequenceExists sequenceName="SEQUENCE_NAME_SEQ" /></not>
    </preConditions>
    <createSequence sequenceName="SEQUENCE_NAME_SEQ"/>
  </changeSet>

A work around is using plain SQL to check this instead:

  <changeSet author="user" id="123">
    <preConditions onFail="CONTINUE">
            <sqlCheck expectedResult="0">
              select count(*) from user_sequences where sequence_name = 'SEQUENCE_NAME_SEQ';
            </sqlCheck>
    </preConditions>
    <createSequence sequenceName="SEQUENCE_NAME_SEQ"/>
  </changeSet>

Lockdata is stored in the table DATABASECHANGELOCK. To get rid of the lock you just change 1 to 0 or drop that table and recreate.

Upvotes: 29

Liquibase lock - reasons?

Answers (13)

Liquibase 4

Edit june 2020

Original answer

Related Questions