Liquibase lock - reasons?

Question

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?

Did you kill the JVM while liquibase held the lock? That's the only case where this occurs for me. — Christoph Leiter, Mar 20 '13 at 16:23
There seems to be another PC involved: Konsultpc74. Maybe you ran liquibase from to different PCs at the same time? If not do you have an explanation for the other PC? — Jens, Mar 20 '13 at 16:57
I edited the logs and I accidently forgot to change that to SomeComputer — Peter Isberg, Mar 21 '13 at 09:48
Are you executing the changesets simultaneously? I thought each file and each changeset in it is executed one by one. At least I use it this way. I have one master changeset file which inlcudes all others and everthing is run one by one. — Jens, Mar 21 '13 at 22:12

Adrian Ber · Accepted Answer · 2020-11-24T10:39:30.337

672

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.

edited Nov 24 '20 at 10:39

answered Sep 29 '13 at 18:20

Adrian Ber

17,162
9
57
99

35

I needed to switch out the `0` for `FALSE`, but other than that, it worked fine. Thanks – mattalxndr Jul 07 '14 at 21:46
9

There is a built-in command in Liquibase called releaseLocks that would execute what @Adrian Ber answered but I think it is database agnostic. – Oct 26 '15 at 11:17
1

I was just getting this error in my development environment. Fixing up the DATABASECHANGELOGLOCK table solved it. – Naymesh Mistry Jul 28 '16 at 18:38
1

I needed to switch out the `FALSE` for `b'0'` – OrangePot Oct 10 '17 at 20:32
2

This is the correct solution, don't try emptying the table as that won't help. Either DROP it or UPDATE the LOCKED flag to 'FALSE' – Aditya T Nov 23 '17 at 08:57
No False in SQL Server if that is what you are using UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1; – Trujllo Mar 07 '18 at 16:32
This seems like a SQL injection – Sham Fiorin Apr 16 '19 at 14:07
Why SQL injection? – Adrian Ber Apr 17 '19 at 15:12
the [logs](https://docs.liquibase.com/concepts/basic/databasechangeloglock-table.html) – spen.smith Oct 16 '20 at 00:41
Finally this helped me, thanks. My issue was I had multiple schemas in MySQL Workbench and occasionally looked at the wrong one. Once I figured out the correct scheme, I was able to figure out the lock is indeed acquired, and reset it using `false` instead of `0` as mentioned several times above. – RAM237 Nov 23 '20 at 23:30
LOCKED=0 works for me. – Daniel Rch. Dec 29 '20 at 20:56

score 65 · Answer 2 · edited Feb 23 '21 at 19:36

65

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.

edited Feb 23 '21 at 19:36

aarowman

73
1
7

answered May 22 '15 at 09:34

e18r

5,364
2
38
38

4

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Rachcha May 22 '15 at 10:17
@Rachcha I explained it better. Hope you like it more like this. – e18r May 22 '15 at 10:23
12

Do not follow the advice above. DATABASECHANGELOGLOCK must contain rows without any rows you will get an exception – odedsh Sep 16 '15 at 13:54
1

This does not help, I tried this rather than dropping the table or update locked state to 'false'. It didn't worked. – Aditya T Nov 23 '17 at 08:49
If you follow this answer there is a good chance future scripts will not run because they expect the lock to exist. If you have done this already you can add an empty lock to fix the issue `INSERT INTO yourdb.DATABASECHANGELOGLOCK VALUES (1, 0, null, null);` – Rudi Kershaw Nov 28 '17 at 11:39
https://stackoverflow.com/questions/19119115/liquibase-update-error/26870086 – Mike Apr 14 '20 at 14:41
I made a mistake and followed these instructions and the server failed to start. I fixed it with: insert into DATABASECHANGELOGLOCK values(1,false,null,null); – Leos Literak Jun 18 '20 at 11:46
@LeosLiterak I'm sorry my answer caused you trouble. Over the years it seems to have caused more harm than good. I'm gonna edit it to reflect that. Thanks for your feedback. – e18r Jun 18 '20 at 14:20
Deleting the row is bad, but dropping the entire table should work. Liquibase just doesn't like an empty table – aarowman Feb 17 '21 at 15:54

score 27 · Answer 3 · edited Mar 22 '13 at 12:34

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 liquibase 3.0.2 (the version I am using), do not remove the one line from the lock table, or you will have a different error when running liquibase the next time, because liquibase expects that one row to be there (or the whole table missing). Exactly as Peter said, just wanted to add that info, because in older versions it seems to have worked to also remove the row. — Kariem, Aug 16 '13 at 09:45

score 10 · Answer 4 · answered Apr 04 '19 at 21:39

10

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.

answered Apr 04 '19 at 21:39

k_o_

2,597
19
26

Could you please provide a more detailed description of your solution? We use Spring Boot 2 and liquibase and do not want to delete the lock-state in the db each time manually. But I did not understand how do you inject the ForceReleaseLockService to the liquibase. Don`t I have to put a Service/Component annotation over this class, that Spring choose it as a primary bean? – Andrej Tihonov Jun 03 '19 at 08:56
1

It is mentioned in the last sentence: "The class must be placed in the liquibase.ext package and will be picked up by the Spring Boot 2 auto configuration." – k_o_ Jun 04 '19 at 00:41
How do you place the class in `liquibase.ext`, do I need to define that package in my project? – akuma8 Aug 28 '19 at 16:21
I defined that package in my project, it seems to work but I can't verify it. I defined a `@PostConstruct` method with a log message but I don't see it printed. – akuma8 Aug 28 '19 at 17:18
@akuma8: Yes, just create a package in your project with that name. Where did you define the `@PostConstruct` method? In the ForceReleaseLockService? This is not a Spring service, so this will not get invoked. – k_o_ Aug 30 '19 at 22:36
Yes in the ForceReleaseLockService. Do you think that we can use a such hack in production with microservices architecture? If we have multiple instances, one instance could force the first one getting the lock to free it even it has not finished DB update. – akuma8 Aug 31 '19 at 07:06
I'm only using this for unit tests and not in the production code. In a production environment I would not expect to see this often, only in cases when the container is not starting up correctly, but this is usually already detected for the most time in a staging environment. In a production environment if this happens unlock the table manually. – k_o_ Sep 01 '19 at 00:32
@к_о_: I put the specified class in the liquibase.ext package in the root of the test loop project. but it doesn't work. How should SpringBoot know about this ? – skyho Feb 19 '21 at 14:17
@skyho: Spring boot is by default checking this package. I don't know what a test loop project is. Maybe here the test loop project is referencing to a test execution? I have never tried to just use this during a test, but actually the classpath should still pick it up. – k_o_ Feb 20 '21 at 05:28
@к_о_: and yet it is not clear how to use it. I created a package and put it there. I don't see the SpringBootTest context seeing this class. How to do everything right. someone please post an example. I put the class in the root : test/java/my_root_test_contour/liquibase/ext – skyho Feb 24 '21 at 13:59
`test/java/my_root_test_contour/liquibase/ext` is a standard path of you build tool? Maven and Gradle are using `src/test/java/...` – k_o_ Feb 25 '21 at 15:11

score 3 · Answer 5 · answered Jun 19 '14 at 14:48

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!

score 3 · Answer 6 · edited Apr 01 '16 at 01:53

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

score 3 · Answer 7 · answered Apr 14 '20 at 17:14

3

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

DROP TABLE DATABASECHANGELOGLOCK;

answered Apr 14 '20 at 17:14

Mike

17,033
22
85
113

score 1 · Answer 8 · answered Feb 11 '21 at 13:23

1

In postgres 12 I needed to use this command:

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

answered Feb 11 '21 at 13:23

g.momo

484
2
6

score 0 · Answer 9 · answered Apr 22 '21 at 08:53

0

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

answered Apr 22 '21 at 08:53

Danny

106
1
5

Liquibase lock - reasons?

9 Answers9

Edit june 2020

Original answer

Linked