[database] 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?

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

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!

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.

In postgres 12 I needed to use this command:

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

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 set-ups 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.