1 based on unique index (insert) implementation

An optimistic locking scheme for record locks. Based on database is implemented the core idea is: to create a table in database table contains the method name and other fields, and create a unique index on method name field, want to execute a method, use this method to insert the data in the table, successful insertion is acquiring a lock, after completion of execution to delete the corresponding row data releases the lock.

1.1 the advantages and disadvantages

advantages

  • Simple implementation and easy to understand

disadvantages

  • There is no thread to wake up, and a fetch failure is thrown away
  • Without timeout protection, if the unlock operation fails, the lock record will remain in the database and no other thread can acquire the lock
  • This lock is strongly dependent on the availability of the database. The database is a single point. Once the database fails, the service system becomes unavailable
  • When the number of concurrent requests is large, the lock acquisition interval is small, which will cause pressure to the system and database
  • The lock must be non-blocking, because if the insert fails, an error will be reported. Threads that have not acquired the lock are not queued. If they want to acquire the lock again, they must trigger the lock again
  • The lock is non-reentrant; the same thread cannot acquire the lock again without releasing it because the data already exists in the data
  • This lock is an unfair lock, and all waiting threads compete for the lock by chance

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(a)COMMENT 'Lock timeout'.`remarks` varchar(255) NOT NULL COMMENT 'Note 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';
Copy the code

① Acquire a lock: To execute a method, insert data into the table using the method name

INSERT INTO method_lock (lock_key, lock_timeout, remarks) VALUES ('methodName'.'the 2021-07-19 18:20:00'.'Test methodName');
Copy the code

② Release the lock: when the lock is released, the record is deleted

DELETE FROM method_lock WHERE lock_key ='methodName';
Copy the code

1.3 Problems and solutions

  • Strongly dependent on database availability, is a single point (deploy dual instances)
  • No expiration time, if unlocking fails, deadlock will occur (add scheduled task scan table)
  • If the insert fails, an error will be reported, not queued. (Use a while loop and return on success.)
  • Non-reentrant lock, the same thread cannot obtain the lock again before releasing the lock (add field to record machine and thread information, query the same is directly allocated)
  • Non-fair lock (create an intermediate table to record the threads waiting for the lock, sorted according to the creation time and processed in sequence)
  • Use unique index conflict to prevent weight, which may cause table lock phenomenon in the case of high concurrency (use program production primary key to prevent weight)

2 Based on the version number of table fields

Version number compared to the updated optimistic locking scheme. This is typically done by adding a version field to the database table to read the version number along with the data. During the update process, the system compares the version numbers. If the version numbers are consistent with each other and have not changed, the system successfully performs this operation. If the version numbers are inconsistent, the update fails. It’s actually a CAS process.

2.1 the advantages and disadvantages

disadvantages

  • In this mode, the one-time update operation must be changed to 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 a business scenario, then if optimistic locking based on database resource tables is used, each resource must have a resource table, which is certainly not satisfied in actual use scenarios. And these are based on database operations, with high concurrency requirements, the overhead of the database connection must be unbearable
  • Optimistic locking mechanisms are often based on the data storage logic in the system, which may cause dirty data to be updated to the database

3 based on exclusive lock (for update) implementation

A pessimistic locking scheme based on exclusive locking. By adding for update after the SELECT statement to acquire the lock, the database adds an exclusive lock to the database table during the query. When an exclusive lock is added to a record, other threads can no longer add an exclusive lock to the record, so we can think of the thread that acquired the exclusive lock as acquiring a distributed lock. Release the lock via connection.com MIT (); Operation, commit transaction to achieve.

3.1 the advantages and disadvantages

advantages

  • Simple implementation and easy to understand

disadvantages

  • Exclusive locks can tie up connections and cause connection overcrowding problems
  • If the table is small, row locking may not be used
  • There are also single point problems, concurrency problems

3.2 Implementation Scheme

支那

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(a)COMMENT 'Lock timeout'.`remarks` varchar(255) NOT NULL COMMENT 'Note 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';
Copy the code

Add unlock operation

/**
 * 加锁
 */
public boolean lock(a) {
        // Start the transaction
        connection.setAutoCommit(false);
        // The loop blocks waiting for the lock to be acquired
        while (true) {
            // Execute the SQL to acquire the lock
            String sql = "select * from methodLock where lock_key = xxx for update";
             // Create a prepareStatement object to execute SQL
            ps = conn.prepareStatement(sql);
            // Obtain the query result set
            int result = ps.executeQuery();
            // The result is not null
            if(result ! =null) {
                return true; }}// Failed to lock
        return false;
}

/** * Unlock */
public void unlock(a) {
        // Commit the transaction and unlock it
        connection.commit();
}
Copy the code

More on JAVA, high concurrency, microservices, architectures, solutions, middleware at github.com/yu120/lemon…