MySQL pessimistic locking and optimistic locking implementation

Preface

Pessimistic locking and optimistic locking are two ideas used to solve concurrency problems, and they have their own implementations on different platforms. For example, in Java, synchronized can be considered as the implementation of pessimistic locking (not rigorous, there is a lock upgrade process, and it is only considered as a heavyweight lock when upgraded to a heavyweight lock), and the Atomic*** atomic class can be considered as the implementation of optimistic locking.

Pessimistic Lock

It has strong exclusive and exclusive characteristics, and the data is locked during the entire processing process, which is generally achieved through the system's mutex. When other threads try to acquire the lock, they are blocked until the thread holding the lock releases it.

Optimistic Locking

Be optimistic about data modification and access, assuming that no conflicts will occur. Only when data is submitted for update will it be checked for conflicts. If there is no conflict, the update is submitted smoothly. Otherwise, it fails quickly and returns an error to the user, allowing the user to choose what to do next. Generally speaking, it will continue to retry after failure until the update is submitted successfully.

MySQL itself supports the locking mechanism. For example, if we have a requirement of "query first and then write", we hope that the whole process is an atomic operation and cannot be interrupted in the middle. At this time, we can achieve this by adding an "exclusive lock" to the queried data row. As long as the current transaction does not release the lock, MySQL will block other transactions from obtaining exclusive locks until the current transaction releases the lock. This kind of exclusive lock at the bottom of MySQL is called "pessimistic lock".

MySQL itself does not provide optimistic locking function, 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. If the version is consistent, it means that the data has not been modified by other transactions during this period. Otherwise, it means that the data has been modified by other transactions and needs to be retried.

Actual Combat

Assume that the database has two tables: the product table and the order table.

After placing an order, users need to perform two operations:

1. Item table minus inventory.
2. Create a record in the Orders table.

Initial data: The product with ID 1 has 100 in stock, and the order table data is empty.

The client starts 10 threads to place orders concurrently. How do they perform in the lock-free, pessimistic, and optimistic locking scenarios?

The following is the SQL statement to create a 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

-- Order 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

1. No lock

No action is taken.

//Place an order private boolean order(){
    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;
}

The console output is:

2. Pessimistic Lock

When querying products, add FOR UPDATE and add an exclusive lock to the data row. In this way, other threads will be blocked when querying again. Until the transaction of the current thread is committed and the lock is released, other threads can continue to place orders. This method has low concurrency performance.

SQL statement

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

The console output is:

Note: FOR UPDATE must be in a transaction to be effective, and the query and update must be in the same transaction! ! !

3. Optimistic Locking

The implementation idea is: check the version number each time an update is made. If the version number is consistent, it means that the data has not been modified by other threads during the period, and the current thread can submit the update normally. Otherwise, it means that the data has been modified by other threads, and the current thread needs to spin and retry until the business succeeds.

The version number must be incremented when updating data! ! !

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

Business Code

boolean order(){
    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 other threads during the period, and needs to be recursively retried return order();
        }
        // Create an order orderMapper.save(goods.getId());
        success = true;
    }
    return success;
}

The console output is:

Summarize

This is the end of this article about MySQL pessimistic locking and optimistic locking solutions. For more relevant MySQL pessimistic locking and optimistic locking content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!