Detailed explanation of MySQL pessimistic lock and optimistic lock

Pessimistic lock and optimistic lock are concepts defined by people. You can understand them as a kind of thought and are common means to deal with concurrent resources.

Don't confuse them with the locking mechanisms provided in mysql (table lock, row lock, exclusive lock, shared lock).

1, Pessimistic lock

As the name suggests, it is pessimistic about data processing. It always thinks that concurrency conflicts will occur. When data is obtained and modified, others will modify the data. Therefore, in the whole data processing process, the data needs to be locked.

The implementation of pessimistic lock usually depends on the lock mechanism provided by the database, such as the exclusive lock of mysql and select... for update.

Example: during the second kill of goods, the inventory quantity is reduced to avoid oversold.

CREATE TABLE `tb_goods_stock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT 'commodity ID',
  `nums` int(11) unsigned DEFAULT '0' COMMENT 'Commodity 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='Commodity inventory table';

Set the item inventory quantity num field type to unsigned to ensure that negative numbers will not occur at the database level.

Note that when using pessimistic lock, you need to turn off the automatic submission function of MySQL and set autocommit = 0 (generally, you can explicitly turn on the transaction without modifying the MySQL configuration);

Note that the row level lock in mysql is based on the index. If the sql does not take the index, the table level lock will be used to lock the whole table.

1. Open the transaction, query the goods to be sold, and lock the record.

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

2. Judge whether the commodity quantity is greater than the purchase quantity. If not, roll back the transaction.

3. If the conditions are met, the inventory is reduced and the transaction is committed.

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

When the transaction is committed, the lock in the transaction process will be released.

Pessimistic lock 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 the efficiency.

2, Optimistic lock

As the name suggests, it is optimistic about the processing of data. It is optimistic that the data will not conflict under normal circumstances. Whether the data conflict will be detected only when the data update is submitted.

If a conflict is found, an error message is returned to the user, so that the user can decide how to operate.

The implementation of optimistic lock does not rely on the lock mechanism provided by the database, but needs to be implemented by ourselves. The implementation method is generally to record the data version, one through the version number and the other through the timestamp.

For the field that adds a version number or time stamp to the table, the version number will be read out together when reading data, and the version number will be added by 1 when updating data.

When we submit data updates, we judge whether the current version number is equal to the version number read for the first time. If they are equal, they will be updated. Otherwise, the data will be considered expired, the update will be rejected and the user will operate again.

CREATE TABLE `tb_goods_stock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT 'commodity ID',
  `nums` int(11) unsigned DEFAULT '0' COMMENT 'Commodity 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='Commodity inventory table';

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

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

2. Judge whether the commodity quantity is greater than the purchase quantity. If not, roll back the transaction.

3. If the conditions are met, reduce the inventory( When updating, judge 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. Judge whether the update operation is successfully executed. If it is successful, submit it, otherwise roll back.

Optimistic locking is based on program implementation, so there is no deadlock. It is suitable for application scenarios with multiple reads. If conflicts often occur, the upper layer application constantly asks the user to re operate, which reduces the performance. In this case, pessimistic locking is more applicable.

Reprint: zhuanlan.zhihu.com/p/100703597

Tags: Laravel

Posted on Tue, 07 Sep 2021 19:36:48 -0400 by jesse_james