Understanding and application analysis of mysql pessimistic locking and optimistic locking

This article uses examples to describe MySQL pessimistic locking and optimistic locking. Share with you for your reference, the details are as follows:

Pessimistic locking and optimistic locking are concepts defined by people. You can understand them as a kind of thought, which is a common means of handling concurrent resources.

Do not confuse them with the locking mechanisms provided in MySQL (table locks, row locks, exclusive locks, shared locks).

1. Pessimistic Lock

As the name suggests, it means being pessimistic about data processing, always believing that concurrency conflicts will occur, and that others will modify the data when obtaining and modifying data. Therefore, the data needs to be locked during the entire data processing process.

The implementation of pessimistic locking usually relies on the locking mechanism provided by the database, such as MySQL's exclusive lock, select .... for update to implement pessimistic locking.

Example: During a flash sale, the inventory quantity is reduced to avoid overselling.

CREATE TABLE `tb_goods_stock` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT 'Product ID',
 `nums` int(11) unsigned DEFAULT '0' COMMENT 'Product inventory quantity',
 `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
 `modify_time` datetime DEFAULT NULL COMMENT 'Update time',
 PRIMARY KEY (`id`),
 UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product inventory table';

Set the product inventory quantity nums field type to unsigned to ensure that negative numbers do not occur at the database level.

Note that to use pessimistic locking, you need to turn off the autocommit function of MySQL, set autocommit = 0;

Note that row-level locks in MySQL are based on indexes. If SQL does not use indexes, table-level locks will be used to lock the entire table.

1. Start a transaction, query the products to be sold, and lock the record.

begin;
select nums from tb_goods_stock where goods_id = {$goods_id} for update;

2. Determine whether the quantity of goods is greater than the purchase quantity. If not satisfied, roll back the transaction.

3. If the conditions are met, reduce the inventory and commit the transaction.

update tb_goods_stock set nums = nums - {$num} 
where goods_id = {$goods_id} and nums >= {$num};
commit;

The locks held during the transaction are released when the transaction is committed.

Pessimistic locking adopts a conservative strategy of locking first and then processing data in concurrency control. Although it ensures the security of data processing, it also reduces efficiency.

2. Optimistic Locking

As the name suggests, it means taking an optimistic attitude towards data processing, optimistically believing that data will generally not conflict. Only when submitting data updates will data conflicts be detected.

If a conflict is found, an error message is returned to the user, allowing the user to decide how to proceed.

The implementation of optimistic locking does not rely on the locking mechanism provided by the database, but requires us to implement it ourselves. The implementation method is generally to record the data version, one is through the version number, and the other is through the timestamp.

Add a version number or timestamp field to the table. When reading data, read the version number together. When the data is updated, increase the version number by 1.

When we submit data updates, we determine whether the current version number is equal to the version number read for the first time. If they are equal, they are updated; otherwise, the data is considered expired and the update is rejected, requiring the user to re-operate.

CREATE TABLE `tb_goods_stock` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT 'Product ID',
 `nums` int(11) unsigned DEFAULT '0' COMMENT 'Product inventory quantity',
 `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
 `modify_time` datetime DEFAULT NULL COMMENT 'Update time',
 `version` bigint(20) unsigned DEFAULT '0' COMMENT 'Version number',
 PRIMARY KEY (`id`),
 UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='Product inventory table';

1. Query the product to be sold and obtain the version number.

begin;
select nums, version from tb_goods_stock where goods_id = {$goods_id};

2. Determine whether the quantity of goods is greater than the purchase quantity. If not satisfied, roll back the transaction.

3. If the conditions are met, reduce inventory. (When updating, determine whether the current version is the same as the version obtained in step 1)

update tb_goods_stock set nums = nums - {$num}, version = version + 1 
where goods_id = {$goods_id} 
and version = {$version} and nums >= {$num};

4. Determine whether the update operation is executed successfully. If successful, commit it, otherwise roll back.

Optimistic locking is implemented based on the program, so there is no deadlock and it is suitable for read-heavy application scenarios. If conflicts occur frequently, the upper-level application will constantly ask users to re-operate, which will reduce performance. In this case, pessimistic locking is more applicable.

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Database Lock-Related Skills", "Summary of MySQL Stored Procedure Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills" and "Summary of MySQL Transaction Operation Skills".

I hope this article will be helpful to everyone's MySQL database design.