MySQL as a distributed lock

Distributed lock mysql implementation
Method 1: unique index

  • Create a lock table with internal fields representing the resource name and resource description, and the same resource name uses the uniqueness restriction of the database.
  • Multiple processes write the occupancy record of a resource to the database lock table at the same time. When a process writes successfully, it means that it has successfully acquired the lock
  • Other processes get stuck in spin and fail to retry when the insertion fails due to the uniqueness of the resource field.
  • After executing the business, the process holding the lock deletes the record in the table, and returns to step 1 at this time.

    table data
create table `database_lock`(
`id` BIGINT NOT NULL AUTO_INCREMENT,
`resource` INT NOT NULL COMMENT 'lock resource',
`description` varchar(1024) NOT NULL DEFAULT "" COMMENT 'description',
PRIMARY KEY (`id`),
UNIQUE KEY `resource` (`resource`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Database distributed lock table';

db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/distribute_lock?useUnicode=true &characterEncoding=utf-8 &useSSL=true &serverTimezone=Asia/Shanghai
user=root
password=123456
  • PropertiesReader
@Slf4j
public class PropertiesReader {<!-- -->

    // Properties cache file
    private static final Map<String, Properties> propertiesCache = new HashMap<String, Properties>();

    public static Properties getProperties(String propertiesName) throws IOException {<!-- -->
        if (propertiesCache. containsKey(propertiesName)) {<!-- -->
            return propertiesCache. get(propertiesName);
        }
        loadProperties(propertiesName);
        return propertiesCache. get(propertiesName);
    }

    private synchronized static void loadProperties(String propertiesName) throws IOException {<!-- -->
        FileReader fileReader = null;
        try {<!-- -->
            // Create the Properties collection class
            Properties pro = new Properties();
            // Get the file under the src path--->ClassLoader class loader
            ClassLoader classLoader = PropertiesReader. class. getClassLoader();
            URL resource = classLoader. getResource(propertiesName);
            // Get configuration path
            String path = resource. getPath();
            // read the file
            fileReader = new FileReader(path);
            // load the file
            pro.load(fileReader);
            // initialization
            propertiesCache. put(propertiesName, pro);
        } catch (IOException e) {<!-- -->
            log.error("Failed to read the Properties file, the Properties name is:" + propertiesName);
            throw e;
        } finally {<!-- -->
            try {<!-- -->
                if (fileReader != null) {<!-- -->
                    fileReader. close();
                }
            } catch (IOException e) {<!-- -->
                log.error("fileReader failed to close!", e);
            }
        }
    }
}
  • JDBCUtils
@Slf4j
public class JDBCUtils {<!-- -->
    private static String url;
    private static String user;
    private static String password;

    static {<!-- -->
        // read the file and get the value
        try {<!-- -->
            Properties properties = PropertiesReader. getProperties("db. properties");
            url = properties. getProperty("url");
            user = properties. getProperty("user");
            password = properties. getProperty("password");
            String driver = properties. getProperty("driver");
            //4. Register the driver
            Class. forName(driver);
        } catch (IOException | ClassNotFoundException e) {<!-- -->
            log.error("Failed to initialize jdbc connection!", e);
        }
    }

    /**
     * get connection
     * @return connection object
     */
    public static Connection getConnection() throws SQLException {<!-- -->
        return DriverManager. getConnection(url, user, password);
    }

    /**
     * Release resources
     * @param rs
     * @param st
     * @param conn
     */
    public static void close(ResultSet rs, Statement st, Connection conn) {<!-- -->
        if (rs != null) {<!-- -->
            try {<!-- -->
                rs. close();
            } catch (SQLException e) {<!-- -->
                e.printStackTrace();
            }
        }
        if (st != null) {<!-- -->
            try {<!-- -->
                st. close();
            } catch (SQLException e) {<!-- -->
                e.printStackTrace();
            }
        }
        if (conn != null) {<!-- -->
            try {<!-- -->
                conn. close();
            } catch (SQLException e) {<!-- -->
                e.printStackTrace();
            }
        }
    }
}

Database operation class

/**
 * MySQL lock operation class (lock + release lock)
 */
@Slf4j
public class MySQLDistributedLockService {<!-- -->

    private static Connection connection;
    private static Statement statement;
    private static ResultSet resultSet;

    static{<!-- -->
        try {<!-- -->
            connection = JDBCUtils. getConnection();
            statement = connection. createStatement();
            resultSet = null;
        } catch (SQLException e) {<!-- -->
            log.error("Database connection failed!");
        }
    }

    /**
     * lock table - get a lock
     * @param resource resource
     * @param description lock description
     * @return whether the operation was successful
     */
    public static boolean tryLock(int resource,String description){<!-- -->
        String sql = "insert into database_lock (resource,description) values (" + resource + ", '" + description + "');";
        //Get database connection
        try {<!-- -->
            int stat = statement. executeUpdate(sql);
            return stat == 1;
        } catch (SQLException e) {<!-- -->
            return false;
        }
    }
    /**
     * Lock table - release lock
     * @return
     */
    public static boolean releaseLock(int resource) throws SQLException {<!-- -->
        String sql = "delete from database_lock where resource = " + resource;
        //Get database connection
        int stat = statement. executeUpdate(sql);
        return stat == 1;
    }
    /**
     * close the connection
     */
    public static void close(){<!-- -->
        log.info("Current thread: " + ManagementFactory.getRuntimeMXBean().getName().split("@")[0] +
                ", close the database connection!");
        JDBCUtils. close(resultSet, statement, connection);
    }
}

LockTable

/**
 * mysql distributed lock
 * Execution process: Multi-process seizes a certain resource of the database, then executes the business, and releases the resource after execution
 * Lock mechanism: When a single process acquires a lock, other processes fail to submit
 */
@Slf4j
public class LockTable extends Thread {<!-- -->
    @Override
    public void run() {<!-- -->
        super. run();
        // Get the process ID of the Java virtual machine
        String pid = ManagementFactory.getRuntimeMXBean().getName().split("@")[0];
        try{<!-- -->
            while(true){<!-- -->
                log.info("Current process PID:" + pid + ", trying to acquire lock resources!");
                if(MySQLDistributedLockService.tryLock(1,"Test lock")){<!-- -->
                    log.info("Current process PID:" + pid + ", acquire lock resource successfully!");
                    //sleep simulates the business process
                    log.info("Start processing business!");
                    Thread. sleep(10*1000);
                    log.info("Business processing completed!");
                    MySQLDistributedLockService. releaseLock(1);
                    log.info("Current process PID: " + pid + ", the lock resource is released!");
                    break;
                }else{<!-- -->
                    log.info("Current process PID: " + pid + ", failed to acquire lock resource!");
                    Thread. sleep(2000);
                }
            }
        }catch (Exception e){<!-- -->
            log.error("An error occurred in preempting the lock!",e);
        }finally {<!-- -->
            MySQLDistributedLockService. close();
        }
    }
    // program entry
    public static void main(String[] args) {<!-- -->
        new LockTable().start();
    }
}

Test
Enable parallel execution options at runtime, and run three or more processes at a time. Allow parallel run runs parallel execution
image.png
image.png
Precautions:

  • The lock is non-blocking
  • When a process holds a lock and hangs to death, resources will not be released all the time, resulting in a deadlock. Therefore, it is necessary to maintain a regular cleanup task to clean up long-held locks
  • Pay attention to the single-point problem of the database, it is best to set up a backup database to further improve reliability
  • The lock is a non-reentrant lock. If you want to set it as a reentrant lock, you need to add a database field to record the information of the device holding the lock and the number of locks

Method 2: Based on optimistic locking

  • Before executing the business each time, first perform a database query to query the current resource value (or version number) that needs to be modified.
  • Modify the resource, and compare the resource (or version number) before modifying, and compare whether the value in the database at this time is the same as the query result in the previous step.
  • If the query results are the same, modify the corresponding resource value; if the query results are different, return to the first step.
    image.png

Example: Set the basic information of a product in the database (named surgical masks, the quantity is 10), and multiple processes will rush to buy the product, and the rush will end when the quantity of the product is 0.

Code

 /**
     * Optimistic lock - get resources
     * @param id resource ID
     * @return result
     */
    public static ResultSet getGoodCount(int id) throws SQLException {<!-- -->

        String sql = "select * from database_lock_2 where id = " + id;

        //Query data
        resultSet = statement. executeQuery(sql);
        return resultSet;
    }


    /**
     * Optimistic lock - modify resource
     * @param id resource ID
     * @param goodCount resource
     * @return Modified state
     */
    public static boolean setGoodCount(int id, int goodCount) throws SQLException {<!-- -->

        String sql = "update database_lock_2 set good_count = good_count - 1 where id =" + id + " and good_count = " + goodCount;

        int stat = statement. executeUpdate(sql);
        return stat == 1;
    }

    /**
     * Optimistic lock - enable automatic transaction commit
     */
    public static void AutoCommit(){<!-- -->
        try {<!-- -->
            connection.setAutoCommit(true);
        } catch (SQLException e) {<!-- -->
            log.error("Enable automatic submission!",e);
        }
    }

OptimisticLock test class

/**
 * mysql distributed lock - optimistic lock
 * Execution process: Multiple processes snap up the same product, each time the number of successfully snapped up products is -1, and exit when the data volume of the product is 0
 * Lock mechanism: When a single process acquires a lock, other processes fail to submit
 */
@Slf4j
public class OptimisticLock extends Thread{<!-- -->

    @Override
    public void run() {<!-- -->
        super. run();
        String pid = ManagementFactory.getRuntimeMXBean().getName().split("@")[0];

        ResultSet resultSet = null;
        String goodName = null;
        int goodCount = 0;
        try {<!-- -->
            while(true){<!-- -->
                log.info("Current thread: " + pid + ", start buying goods!");
                //Get the current product information
                resultSet = MySQLDistributedLockService. getGoodCount(1);
                while (resultSet. next()){<!-- -->
                    goodName = resultSet. getString("good_name");
                    goodCount = resultSet. getInt("good_count");
                }
                log.info("Obtained the inventory successfully, the current product name is: " + goodName + ", the current remaining inventory is: " + goodCount);
                //Simulate the execution of business operations
                Thread. sleep(2*3000);
                if(0 == goodCount){<!-- -->
                    log.info("Purchasing failed, the current stock is 0!");
                    break;
                }
                //Modify inventory information, inventory -1
                if(MySQLDistributedLockService.setGoodCount(1,goodCount)){<!-- -->
                    log.info("Current thread: " + pid + " Buying goods: " + goodName + "successful, the remaining stock is: " + (goodCount -1));
                    //Simulate the delay to prevent the lock from being acquired by the same process every time
                    Thread. sleep(2 * 1000);
                }else{<!-- -->
                    log.error("Purchasing goods: " + goodName + "failed, the quantity of goods has been modified");
                }
            }
        }catch (Exception e){<!-- -->
            log.error("An error occurred in buying the product!",e);
        }finally {<!-- -->
            if(resultSet != null){<!-- -->
                try {<!-- -->
                    resultSet. close();
                } catch (SQLException e) {<!-- -->
                    e.printStackTrace();
                    log.error("Failed to close Result!", e);
                }
            }
            MySQLDistributedLockService. close();
        }
    }

    public static void main(String[] args) {<!-- -->
        new OptimisticLock().start();
    }
}

code testing
Open three processes to check the execution
image.png
image.png
image.png
Notes:

  • The lock is non-blocking
  • The lock is intrusive to the business. If the version number check is set, an additional field is added, which increases database redundancy.
  • When the concurrency is too high, there will be a large number of requests to access a row of records in the database, which will cause a lot of writing pressure on the database
  • Therefore, optimistic locking is suitable for scenarios with low concurrency and infrequent write operations

Method 3: Pessimistic lock implementation method (using transactions plus row/table locks)

Implementation ideas

  • Close jdbc connection automatic commit property
  • Use the query statement followed by for update to lock the row data before each execution of the business (note that if the query condition does not hit the primary key or index, it will change from row lock to table lock at this time)
  • Execute business process modification table resource
  • Execute the commit operation

Code Implementation
MySQLDistributedLockService

    /**
     * Pessimistic lock - get resources
     * @param id resource ID
     * @return result
     */
    public static ResultSet getGoodCount2(int id) throws SQLException {<!-- -->
        String sql = "select * from database_lock_2 where id = " + id + "for update";
        //Query data
        resultSet = statement. executeQuery(sql);
        return resultSet;
    }

    /**
     * Pessimistic lock - modify resource
     * @param id resource ID
     * @return Modified state
     */
    public static boolean setGoodCount2(int id) throws SQLException {<!-- -->

        String sql = "update database_lock_2 set good_count = good_count - 1 where id =" + id;
        int stat = statement. executeUpdate(sql);
        return stat == 1;
    }

    /**
     * Pessimistic lock - turn off transaction autocommit
     */
    public static void closeAutoCommit(){<!-- -->
        try {<!-- -->
            connection.setAutoCommit(false);
        } catch (SQLException e) {<!-- -->
            log.error("Failed to turn off automatic submission!",e);
        }
    }

    /**
     * Pessimistic lock - commit transaction
     */
    public static void commit(String pid,String goodName,int goodCount) throws SQLException {<!-- -->
        connection.commit();
        log.info("Current thread: " + pid + "Purchasing goods: " + goodName + "Successful, the remaining stock is: " + (goodCount-1));
    }

    /**
     * Pessimistic lock - rollback
     */
    public static void rollBack() throws SQLException {<!-- -->
        connection. rollback();
    }

PessimisticLock

/**
 * mysql distributed lock-pessimistic lock
 * Execution process: multiple processes preempt the same commodity, and release the lock through connection.commit() after executing the business
 * Lock mechanism: when a single process acquires a lock, other processes will block and wait
 */
@Slf4j
public class PessimisticLock extends Thread {<!-- -->
    @Override
    public void run() {<!-- -->
        super. run();
        ResultSet resultSet = null;
        String goodName = null;
        int goodCount = 0;
        String pid = ManagementFactory.getRuntimeMXBean().getName().split("@")[0];
        // turn off autocommit
        MySQLDistributedLockService. closeAutoCommit();
        try{<!-- -->
            while(true){<!-- -->
                log.info("Current thread:" + pid + "");
                // get inventory
                resultSet = MySQLDistributedLockService.getGoodCount2(1);
                while (resultSet. next()) {<!-- -->
                    goodName = resultSet. getString("good_name");
                    goodCount = resultSet. getInt("good_count");
                }
                log.info("Get the inventory successfully, the current commodity name is:" + goodName + ", the current inventory remaining is:" + goodCount);
                // Simulate the execution of business events
                Thread. sleep(2 * 1000);
                if (0 == goodCount) {<!-- -->
                    log.info("Purchasing failed, the current inventory is 0!");
                    break;
                }
                // snap up items
                if (MySQLDistributedLockService.setGoodCount2(1)) {<!-- -->
                    // Simulate the delay to prevent the lock from being acquired by the same process every time
                    MySQLDistributedLockService.commit(pid, goodName, goodCount);
                    Thread. sleep(2 * 1000);
                } else {<!-- -->
                    log.error("Buying goods:" + goodName + "Failed!");
                }
            }
        }catch (Exception e){<!-- -->
            //Buying failed
            log.error("An error occurred in buying the product!",e);
            try {<!-- -->
                MySQLDistributedLockService.rollBack();
            } catch (SQLException ex) {<!-- -->
                log.error("Rollback failed!",e);
            }
        }finally {<!-- -->
            if(resultSet != null){<!-- -->
                try {<!-- -->
                    resultSet. close();
                } catch (SQLException e) {<!-- -->
                    log.error("Result close failed!",e);
                }
            }
            MySQLDistributedLockService. close();
        }
    }

    public static void main(String[] args) {<!-- -->
        new PessimisticLock().start();
    }
}

Test Results
image.png
image.png
image.png
Notes:

  • The lock is a blocking lock
  • There is an additional locking overhead for each request
  • In the case of high concurrency, there will be a large number of blocked requests in the system, which will affect the availability of the system
  • Therefore, pessimistic locking is suitable for writing scenarios with low concurrency and infrequent read operations

Summary:

  • In actual use, due to performance and stability constraints, distributed locks implemented for relational databases are generally rarely used. However, for some single business scenarios where the concurrency is not high and the system is only provided to internal personnel, you can consider using a relational database distributed lock, because its complexity is low and its reliability can be guaranteed.