com.github.blagerweij.sessionlock.MySQLLockService Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of liquibase-sessionlock Show documentation
Replaces the default DATABASECHANGELOGLOCK with a RDBMS lock, which is automatically released when the container is stopped unexpectedly
The newest version!
/*
 * This module, both source code and documentation,
 * is in the Public Domain, and comes with NO WARRANTY.
 */
package com.github.blagerweij.sessionlock;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Locale;

import com.github.blagerweij.sessionlock.util.StringUtils;
import liquibase.database.Database;
import liquibase.database.core.MySQLDatabase;
import liquibase.exception.LockException;
import liquibase.lockservice.DatabaseChangeLogLock;

import static com.github.blagerweij.sessionlock.util.StringUtils.toUpperCase;
import static com.github.blagerweij.sessionlock.util.StringUtils.truncate;

/**
 * Employs MySQL user-level (a.k.a. application-level or advisory) locks.
 *
 * 
 *
 * A lock obtained with GET_LOCK() is released explicitly by executing 
 * RELEASE_LOCK() or implicitly when your session terminates (either normally or abnormally).
 * Locks obtained with GET_LOCK() are not released when transactions commit or roll
 * back.
 *
 * 
 *
 * @see Locking Functions (MySQL 5.7 Reference Manual)
 * @see Locking Functions (MySQL 8.0 Reference Manual)
 */
public class MySQLLockService extends SessionLockService {

  static final String SQL_GET_LOCK = "SELECT get_lock(?, ?)";
  static final String SQL_RELEASE_LOCK = "SELECT release_lock(?)";
  static final String SQL_LOCK_INFO =
      "SELECT l.processlist_id, p.host, p.time, p.state"
          + " FROM (SELECT is_used_lock(?) AS processlist_id) AS l"
          + " LEFT JOIN information_schema.processlist p"
          + " ON p.id = l.processlist_id";

  @Override
  public boolean supports(Database database) {
    return (database instanceof MySQLDatabase);
  }

  protected String getChangeLogLockName() {
    // MySQL 5.7 and later enforces a maximum length on lock names of 64 characters.
    return toUpperCase(truncate((database.getDefaultSchemaName() + "." + database.getDatabaseChangeLogLockTableName()), 64));
  }

  private static Integer getIntegerResult(PreparedStatement stmt) throws SQLException {
    try (ResultSet rs = stmt.executeQuery()) {
      rs.next();
      Number locked = (Number) rs.getObject(1);
      return (locked == null) ? null : locked.intValue();
    }
  }

  /**
   * @see GET_LOCK (Locking Functions)
   */
  @Override
  protected boolean acquireLock(Connection con) throws SQLException, LockException {
    try (PreparedStatement stmt = con.prepareStatement(SQL_GET_LOCK)) {
      stmt.setString(1, getChangeLogLockName());
      final int timeoutSeconds = 5;
      stmt.setInt(2, timeoutSeconds);

      Integer locked = getIntegerResult(stmt);
      if (locked == null) {
        throw new LockException("GET_LOCK() returned NULL");
      } else if (locked == 0) {
        return false;
      } else if (locked != 1) {
        throw new LockException("GET_LOCK() returned " + locked);
      }
      return true;
    }
  }

  /**
   * @see RELEASE_LOCK (Locking Functions)
   */
  @Override
  protected void releaseLock(Connection con) throws SQLException, LockException {
    try (PreparedStatement stmt = con.prepareStatement(SQL_RELEASE_LOCK)) {
      stmt.setString(1, getChangeLogLockName());

      Integer unlocked = getIntegerResult(stmt);
      if (!Integer.valueOf(1).equals(unlocked)) {
        throw new LockException(
            "RELEASE_LOCK() returned " + toUpperCase(String.valueOf(unlocked)));
      }
    }
  }

  /**
   * Obtains information about the database changelog lock.
   *
   * @see IS_USED_LOCK (Locking Functions)
   * @see The INFORMATION_SCHEMA PROCESSLIST Table (MySQL Reference Manual)
   */
  @Override
  protected DatabaseChangeLogLock usedLock(Connection con) throws SQLException, LockException {
    try (PreparedStatement stmt = con.prepareStatement(SQL_LOCK_INFO)) {
      stmt.setString(1, getChangeLogLockName());

      try (ResultSet rs = stmt.executeQuery()) {
        if (!rs.next() || rs.getObject("PROCESSLIST_ID") == null) {
          return null;
        }

        long timestamp = rs.getInt("TIME");
        if (timestamp > 0) {
          // This is not really the time the lock has been obtained but gives
          // insight on how long the owning session is doing what it is doing.
          timestamp = System.currentTimeMillis() - timestamp * 1000;
        }
        return new DatabaseChangeLogLock(1, new Date(timestamp), lockedBy(rs));
      }
    }
  }

  private static String lockedBy(ResultSet rs) throws SQLException {
    String host = rs.getString("HOST");
    if (host == null) {
      return "connection_id#" + rs.getLong("PROCESSLIST_ID");
    }

    int colonIndex = host.lastIndexOf(':');
    if (colonIndex > 0) {
      host = host.substring(0, colonIndex);
    }
    return host + " (" + rs.getString("STATE") + ")";
  }
}