Liquibase lock - reasons?

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.

---

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.

---

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.

---

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.

---

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

DROP TABLE DATABASECHANGELOGLOCK;

---

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!

---

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

---

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

---

In postgres 12 I needed to use this command:

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

---

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)

---

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

---