What is the difference between optimistic locking and pessimistic locking in mysql

Difference: Pessimistic locking is a pessimistic attitude towards data processing. It always believes that concurrency conflicts will occur. When acquiring and modifying data, others will modify the data; therefore, the data needs to be locked during the entire data processing process. Optimistic locking is optimistic about data processing and believes that conflicts will generally not occur. Only when data updates are submitted, will data conflicts be detected.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Pessimistic locking and optimistic locking are concepts defined by people. You can understand them as an idea and a common method for dealing with concurrent resources.

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

1. Pessimistic lock

As the name suggests, it is a pessimistic attitude towards data processing. It always believes that concurrency conflicts will occur. When data is obtained and modified, others will modify the data. . Therefore, the data needs to be locked during the entire data processing process.

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

Example: During the product flash sale process, the inventory quantity is reduced to avoid oversold situations.

CREATE TABLE `tb_goods_stock` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT '商品ID',
  `nums` int(11) unsigned DEFAULT '0' COMMENT '商品库存数量',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `modify_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';

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

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

Note that the row-level locks in mysql are based on the index. If the sql does not use the index, , which will lock the entire table using table-level locks.

1. Open the 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 the product is greater than the purchase quantity. If not satisfied, roll back the transaction.

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

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

The lock during the transaction will be released when the transaction is committed.

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

2. Optimistic locking

As the name suggests, it is optimistic about the processing of data. Optimistically, it is believed that data will not conflict under normal circumstances, and only submission Data conflicts will only be detected when the data is updated.

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

The implementation of optimistic locking does not rely on the locking mechanism provided by the database. We need 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, add 1 to the version number.

When we submit the data update, we determine whether the current version number is equal to the first read version number. If they are equal, update them. Otherwise, the data will be considered expired, the update will be rejected, and the user will be asked 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 '商品ID',
  `nums` int(11) unsigned DEFAULT '0' COMMENT '商品库存数量',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `modify_time` datetime DEFAULT NULL COMMENT '更新时间',
  `version` bigint(20) unsigned DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';

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

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

2. Determine whether the quantity of the product 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 successfully executed. If successful, submit it, otherwise roll back.

Optimistic locking is based on program implementation, so there is no deadlock and is suitable for application scenarios with many reads. If conflicts often occur, the upper-layer application will constantly ask users to re-operate, which will actually reduce performance. In this case, pessimistic locking is more suitable.

(Recommended tutorial: mysql video tutorial)

The above is the detailed content of What is the difference between optimistic locking and pessimistic locking in mysql. For more information, please follow other related articles on the PHP Chinese website!