1 implementation based on unique index (insert)

Record the optimistic locking scheme of the lock. The core idea of the implementation method based on database is to create a table in the database, which contains fields such as method name, and create a unique index on the method name field. If you want to execute a method, you can use this method name to insert data into the table. If you insert successfully, you will get the lock, and delete the corresponding row data after execution to release the lock.

1.1 advantages and disadvantages

advantage

• The implementation is simple and easy to understand

shortcoming

• If there is no thread wake-up, the acquisition failure is lost
• There is no timeout protection. Once the unlocking operation fails, the lock record will always be in the database, and other threads can no longer obtain the lock
• This lock strongly depends on the availability of the database. The database is a single point. Once the database hangs, the business system will be unavailable
• When the concurrency is large, the number of requests is large, and the lock acquisition interval is small, which will put pressure on the system and database
• This lock can only be non blocking, because once the insert operation of data fails, it will directly report an error. The thread that does not obtain the lock will not enter the queue. To obtain the lock again, it is necessary to trigger the obtain lock operation again
• The lock is non reentrant. The same thread cannot obtain the lock again before releasing the lock, because the data in the data already exists
• This lock is unfair. All threads waiting for the lock compete for the lock by luck

1.2 implementation scheme

DROP TABLE IF EXISTS `method_lock`;
CREATE TABLE `method_lock` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `lock_key` varchar(64) NOT NULL DEFAULT '' COMMENT 'Key value of lock',
  `lock_timeout` datetime NOT NULL DEFAULT NOW() COMMENT 'Lock timeout',
  `remarks` varchar(255) NOT NULL COMMENT 'Remark information',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_lock_key` (`lock_key`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='Methods in locking';

① Acquire lock: to execute a method, use the method name to insert data into the table

INSERT INTO method_lock (lock_key, lock_timeout, remarks) VALUES ('methodName', '2021-07-19 18:20:00', 'Tested methodName');

② Release lock: records are deleted when the lock is released

DELETE FROM method_lock WHERE lock_key ='methodName';

1.3 problems and Solutions

• Strongly dependent on database availability, it is a single point of deployment (dual instance)
• There is no expiration time. Once unlocking fails, it will lead to deadlock (add scheduled task scan table)
• Once the insertion fails, it will directly report an error and will not enter the queue (use the while loop and return after success)
• It is a non reentrant lock. The same thread cannot obtain the lock again before releasing the lock (add a field to record the machine and thread information. If it is the same during query, it will be allocated directly)
• Unfair lock (create an intermediate table to record the threads waiting for the lock, and process them in turn after sorting according to the creation time)
• Using unique index conflict to prevent duplication may cause table locking in case of large concurrency (using program production primary key to prevent duplication)

2. Implementation based on table field version number

The version number is compared with the updated optimistic locking scheme. Generally, a version field is added to the database table to read out the data. When reading out the data, this version number is read out together. When updating later, add 1 to this version number. During the update process, the version number will be compared. If it is consistent and has not changed, the operation will be successfully performed; If the version numbers are inconsistent, the update fails. It's actually a CAS process.

2.1 advantages and disadvantages

shortcoming

• In this way, the original update operation must be changed into two operations: select the version number once and update once. Increased the number of database operations
• If multiple resources need to be used to ensure data consistency in a business process in the business scenario, if all optimistic locks based on database resource tables are used, each resource must have a resource table, which is certainly not satisfied in the actual use scenario. And these are based on database operations. Under the requirements of high concurrency, the overhead of database connection must be unbearable
• Optimistic locking mechanism is often based on the data storage logic in the system, so it may cause dirty data to be updated to the database

3. Implementation based on exclusive lock (for update)

Pessimistic lock scheme based on exclusive lock. By adding for update after the select statement to obtain the lock, the database will add an exclusive lock to the database table during the query process. When an exclusive lock is added to a record, other threads cannot add an exclusive lock to the record. We can think that the thread that obtains the exclusive lock can obtain the distributed lock. Release the lock through connection commit(); Operation, commit transaction to implement.

3.1 advantages and disadvantages

advantage

• The implementation is simple and easy to understand

shortcoming

• The exclusive lock will occupy the connection and cause the problem of full connection
• If the table is small, row locks may not be used
• There are also single point problems and concurrency problems

3.2 implementation scheme

Table creation script

CREATE TABLE `methodLock` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
      `lock_key` varchar(64) NOT NULL DEFAULT '' COMMENT 'Key value of lock',
      `lock_timeout` datetime NOT NULL DEFAULT NOW() COMMENT 'Lock timeout',
      `remarks` varchar(255) NOT NULL COMMENT 'Remark information',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY ( `id` ),
    UNIQUE KEY `uidx_lock_key` ( `lock_key ` ) USING BTREE 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = 'Methods in locking';

Add unlock operation

/**
 * Lock
 */
public boolean lock() {
        // Open transaction
        connection.setAutoCommit(false);
        // Loop blocking, waiting to acquire lock
        while (true) {
            // Execute sql to acquire lock
            String sql = "select * from methodLock where lock_key = xxx for update";
             // Create a prepareStatement object to execute SQL
            ps = conn.prepareStatement(sql);
            // Get query result set
            int result = ps.executeQuery();
            // The result is not empty, and locking is successful
            if (result != null) {
                return true;
            }
        }
    
        // Locking failed
        return false;
}

/**
 * Unlock
 */
public void unlock() {
        // Commit transaction, unlock
        connection.commit();
}

More JAVA, high concurrency, microservices, architectures, solutions and middleware are summarized in: https://github.com/yu120/lemon-guide