Is there a configuration for removing LiquiBase DATABASE CHANGELOGLOCK automatically after a certain time or on app restart?

huangapple go评论89阅读模式
英文:

Is there a configuration for removing LiquiBase DATABASE CHANGELOGLOCK automatically after a certain time or on app restart?

问题

我们的SpringBoot 2驱动的HA Java应用程序中使用了PostgreSQL作为底层数据库。

由于某些原因,比如意外崩溃或异常,Liquibase最终会出现一个未释放的DATABASECHANGELOGLOCK。

这导致应用程序的后续部署失败,应用程序等待更改锁,然后以以下方式退出:

2020-03-04T11:10:31.78+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
2020-03-04T11:10:31.78+0200 Waiting for changelog lock....
2020-03-04T11:10:32.87+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
2020-03-04T11:10:32.87+0200 Waiting for changelog lock....
2020-03-04T11:10:41.78+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
2020-03-04T11:10:41.78+0200 Waiting for changelog lock....
2020-03-04T11:10:42.87+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
2020-03-04T11:10:42.87+0200 Waiting for changelog lock....
2020-03-04T11:10:51.79+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
2020-03-04T11:10:51.79+0200 Waiting for changelog lock....
2020-03-04T11:10:52.88+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
2020-03-04T11:10:52.88+0200 Waiting for changelog lock....
2020-03-04T11:10:54.00+0200 ERR 2020-03-04 09:10:54.010 UTC
2020-03-04T11:10:55.88+0200 [HEALTH/0] ERR Failed to make TCP connection to port 8080: connection refused
2020-03-04T11:10:55.88+0200 [CELL/0] ERR Failed after 1m0.626s: readiness health check never passed.
2020-03-04T11:10:55.89+0200 [CELL/SSHD/0] OUT Exit status 0
2020-03-04T11:10:55.89+0200 info    [native] Initiating shutdown sequence for Java agent
2020-03-04T11:10:55.89+0200 info    [] Connection Status (120 times 300s)      : 0909

是否有一种配置方式可以在一定时间后自动删除Liquibase DATABASECHANGELOGLOCK,或者在应用程序启动时如果它的存在时间超过了5分钟或预定义的时间段可以在应用程序启动前以编程方式完成这个操作,以避免Postgres寻找更改锁。

英文:

We have SpringBoot 2 driven HA java application in which we use PostgreSQL underneath.

For certain reasons like unexpected crashes or Exceptions, Liquibase ends up with a stale DATABASECHANGELOGLOCK which was never released.

This results in subsequent deployments of the app failing with app waiting for the change lock and then exiting as follows:

   2020-03-04T11:10:31.78+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
   2020-03-04T11:10:31.78+0200 Waiting for changelog lock....
   2020-03-04T11:10:32.87+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
   2020-03-04T11:10:32.87+0200 Waiting for changelog lock....
   2020-03-04T11:10:41.78+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
   2020-03-04T11:10:41.78+0200 Waiting for changelog lock....
   2020-03-04T11:10:42.87+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
   2020-03-04T11:10:42.87+0200 Waiting for changelog lock....
   2020-03-04T11:10:51.79+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
   2020-03-04T11:10:51.79+0200 Waiting for changelog lock....
   2020-03-04T11:10:52.88+0200 SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
   2020-03-04T11:10:52.88+0200 Waiting for changelog lock....
   2020-03-04T11:10:54.00+0200 ERR 2020-03-04 09:10:54.010 UTC
   2020-03-04T11:10:55.88+0200 [HEALTH/0] ERR Failed to make TCP connection to port 8080: connection refused
   2020-03-04T11:10:55.88+0200 [CELL/0] ERR Failed after 1m0.626s: readiness health check never passed.
   2020-03-04T11:10:55.89+0200 [CELL/SSHD/0] OUT Exit status 0
   2020-03-04T11:10:55.89+0200 info    [native] Initiating shutdown sequence for Java agent
   2020-03-04T11:10:55.89+0200 info    [] Connection Status (120 times 300s)      : 0909

Is there a configuration for removing Liquibase DATABASECHANGELOGLOCK automatically after a certain time or removing it on application start if it is older than let's say 5 mins or a predefined time period.
Or can this be done programatically at App Start before Postgres starts looking for the change lock.

答案1

得分: 3

以下是您要翻译的内容:

So I was able to achieve this via the following approach:

We initialise LiquiBase using a SpringLiquibase bean.

Within this bean, before the Liquibase instance is constructed, I called a method which uses Statements to query the database for a lock, and if there are any locks older than 5 minutes, we delete them.

@Bean
public SpringLiquibase liquibase(DataSource dataSource) {
    // Added a hook to check for locks before LiquiBase initialises.
    removeDBLock(dataSource);
    //
    SpringLiquibase liquibase = new SpringLiquibase();
    liquibase.setChangeLog(Constants.DDL_XML);
    liquibase.setDataSource(dataSource);
    return liquibase;
}

private void removeDBLock(DataSource dataSource) {
   
    //Timestamp, currently set to 5 mins or older.

    final Timestamp lastDBLockTime = new Timestamp(System.currentTimeMillis() - (5 * 60 * 1000));
    
    final String query = format("DELETE FROM DATABASECHANGELOGLOCK WHERE LOCKED=true AND LOCKGRANTED<'%s'", lastDBLockTime.toString());
    

    try (Statement stmt = dataSource.getConnection().createStatement()) {

        int updateCount = stmt.executeUpdate(query);
        if(updateCount>0){
            log.error("Locks Removed Count: {} .",updateCount);
        }
    } catch (SQLException e) {
        log.error("Error! Remove Change Lock threw and Exception. ",e);
    }
}

请注意,我保留了代码部分并没有翻译。

英文:

So I was able to achieve this via the following approach:

We initialise LiquiBase using a SpringLiquibase bean.

Within this bean, before the Liquibase instance is constructed, I called a method which uses Statements to query the database for a lock, and if there are any locks older than 5 minutes, we delete them.

 @Bean
 public SpringLiquibase liquibase(DataSource dataSource) {
        // Added a hook to check for locks before LiquiBase initialises.
        removeDBLock(dataSource);
        //
        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setChangeLog(Constants.DDL_XML);
        liquibase.setDataSource(dataSource);
        return liquibase;
    }



 private void removeDBLock(DataSource dataSource) {
   
    //Timestamp, currently set to 5 mins or older.

    final Timestamp lastDBLockTime = new Timestamp(System.currentTimeMillis() - (5 * 60 * 1000));
    
    final String query = format("DELETE FROM DATABASECHANGELOGLOCK WHERE LOCKED=true AND LOCKGRANTED<'%s'", lastDBLockTime.toString());
    

    try (Statement stmt = dataSource.getConnection().createStatement()) {

        int updateCount = stmt.executeUpdate(query);
        if(updateCount>0){
            log.error("Locks Removed Count: {} .",updateCount);
        }
    } catch (SQLException e) {
        log.error("Error! Remove Change Lock threw and Exception. ",e);
    }
}

答案2

得分: 2

Liquibase提供的默认锁实现使用一个名为'DATABASECHANGELOGLOCK'的数据库表。一旦获取了锁的进程意外终止,唯一的恢复方法是手动释放该锁(使用Liquibase CLI或使用SQL语句)。请查看此Liquibase扩展,它替换了StandardLockService,使用数据库锁:https://github.com/blagerweij/liquibase-sessionlock

此扩展使用MySQL或Postgres用户锁语句,这些语句在数据库连接关闭时(例如容器意外停止时)会自动释放。使用该扩展的唯一要求是添加库的依赖项。Liquibase将自动检测到改进后的LockService。

我不是该库的作者,但在寻找解决方案时偶然发现了该库。我通过将该库发布到Maven中央库来帮助作者。当前支持MySQL和PostgreSQL,但支持其他关系型数据库应该相对容易。

英文:

The default lock implementation provided by Liquibase uses a database table called 'DATABASECHANGELOGLOCK'. Once a process that has acquired the lock is unexpectedly terminated, the only way to recover is to manually release that lock (using the Liquibase CLI or using a SQL statement). Please take a look at this Liquibase extension, which replaces the StandardLockService, by using database locks: https://github.com/blagerweij/liquibase-sessionlock

This extension uses MySQL or Postgres user lock statements, which are automatically released when the database connection is closed (e.g. when the container is stopped unexpectedly). The only thing required to use the extension is to add a dependency to the library. Liquibase will automatically detect the improved LockService.

I'm not the author of the library, but I stumbled upon the library when I was searching for a solution. I helped the author by releasing the library to Maven central. Currently supports MySQL and PostgreSQL, but should be fairly easy to support other RDBMS.

huangapple
  • 本文由 发表于 2020年8月5日 04:46:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/63254808.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定