This is the first day of my participation in the Gwen Challenge in November. Check out the details: the last Gwen Challenge in 2021.

Pessimistic locking and optimistic locking are two ideas used to solve concurrency problems and have their own implementations on different platforms. In Java, for example, synchronized can be considered an implementation of pessimistic locking (i.e., not rigorous, but upgraded to heavyweight locking), and Atomic classes can be considered an implementation of optimistic locking.

Pessimistic lock has strong characteristics of exclusivity and exclusivity. Data is locked in the whole process, usually through the system’s mutex to achieve. When another thread tries to acquire the lock, it blocks until the thread holding the lock releases it.

Optimistic locking to modify data and access optimistic, assume that will not conflict, only when the data submitted to update to test the data conflict or not, if there is no conflict successfully submitted to update, otherwise fail fast, return an error to the user, let the user choose what to do next, in general will continue to try again after failure, Until the update is committed successfully.

MySQL itself supports locking. For example, if we have a “search first, write later” requirement, we want the whole process to be an atomic operation without interruption. This can be achieved by adding “exclusive lock” to the query row. As long as the current transaction does not release the lock, other transactions will block to acquire the exclusive lock until the current transaction releases the lock. This exclusive lock underlying MySQL is called a “pessimistic lock”.

MySQL itself does not provide optimistic locking, so developers need to implement it themselves. A common practice is to add a version column to the table to mark the version of the data row. When we need to update the data, we must compare the version version. The consistent version indicates that the data has not been modified by other transactions during this period.

In actual combat

Suppose the database has two tables: an item table and an order table. After placing an order, the user needs to perform two operations:

  1. List of goods minus inventory.
  2. The order table creates a record.

Initial data: 100 in stock for item ID 1, order table data is empty. The client starts 10 threads to place orders concurrently. What are their performances in the scenarios of no lock, pessimistic lock and optimistic lock?

SQL > create table;

- goods table
CREATE TABLE `goods` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(50) NOT NULL,
  `price` decimal(10.2) NOT NULL,
  `stock` int(11) DEFAULT '0',
  `version` int(10) unsigned NOT NULL DEFAULT '0'.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

- orders table
CREATE TABLE `t_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `goods_id` bigint(20) NOT NULL,
  `order_time` datetime NOT NULL.PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
Copy the code

1, no lock

You don’t do anything.

/ / order
private boolean order(a){
    Goods goods = goodsMapper.selectById(1L);
    boolean success = false;
    if (goods.getStock() > 0) {
        goods.setStock(goods.getStock() - 1);
        // Update inventory
        goodsMapper.updateById(goods);
        // Create an order
        orderMapper.save(goods.getId());
        success = true;
    }
    return success;
}
Copy the code

Console output:

2. Pessimistic lock

A “FOR UPDATE” lock is added to a row so that it blocks any further query until the current thread commits and releases the lock. This method has low concurrency performance.

The SQL statement

@Select("SELECT * FROM goods WHERE id = #{id} FOR UPDATE")
Goods selectForUpdate(Long id);
Copy the code

Console output: Note: FOR UPDATE must be valid in a transaction, the query and UPDATE must be in the same transaction!!

3. Optimistic Locking

If the version numbers are the same, the current thread can submit the update normally. If the version numbers are the same, the current thread can submit the update normally. Otherwise, the data has been modified by another thread, and the current thread needs to spin again until the service succeeds. Update data at the same time the version number must be increased!!

@Update("UPDATE goods SET stock = #{stock},version = version+1 WHERE id = #{id} AND version = #{version}")
int updateByVersion(Long id, Integer stock, Integer version);
Copy the code

Business code

boolean order(a){
    Goods goods = goodsMapper.selectById(1L);
    boolean success = false;
    if (goods.getStock() > 0) {
        goods.setStock(goods.getStock() - 1);
        // Update inventory with version number
        int result = goodsMapper.updateByVersion(goods.getId(), goods.getStock(), goods.getVersion());
        if (result <= 0) {
            // Update failed, indicating that the data has been modified by another thread and needs to be recursively retried
            return order();
        }
        // Create an order
        orderMapper.save(goods.getId());
        success = true;
    }
    return success;
}
Copy the code

Console output: