Shulou(Shulou.com)05/31 Report--

How to use pessimistic lock and optimistic lock in mysql, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. Pessimistic lock

As the name implies, it is pessimistic about the processing of data, always thinking that concurrency conflicts will occur, and when obtaining and modifying data, others will modify the data. So in the whole process of data processing, the data needs to be locked.

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

Example: in the process of second kill of goods, the amount of inventory 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 'merchandise ID', `nums`int (11) unsigned DEFAULT' 0' COMMENT 'merchandise inventory', `create_ time`COMMENT 'creation time', `modify_ time`stock`', PRIMARY KEY (`id`), UNIQUE KEY `timeid` (`goods_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' merchandise inventory table'

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

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

Note that row-level locks in mysql are index-based, and if sql does not walk out of the index, table-level locks will be used to lock the entire 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 quantity of goods is larger than the quantity purchased. If not, 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

Locks during the transaction are released when the transaction is committed.

Pessimistic lock adopts the conservative strategy of locking and then processing data in concurrency control, which ensures the security of data processing, but also reduces the efficiency.

Optimistic lock

As the name implies, it is optimistic about the processing of the data, optimistic that the data will not conflict in general, and that 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 and the user is left to decide what to do.

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

Add a version number or timestamp field to the table, read the version number together when reading the data, and add 1 to the version number when the data is updated.

When we submit a data update, we determine whether the current version number is equal to the version number read for the first time. If it is equal, it will be updated, otherwise the data is considered to be out of date, the update is rejected and the user is allowed to re-operate.

CREATE TABLE `goods_ stock` (`id`bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `goods_ id` bigint (20) unsigned DEFAULT' 0' COMMENT 'merchandise ID', `nums`int (11) unsigned DEFAULT' 0' COMMENT 'merchandise inventory', `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=' goods inventory table'

1. Inquire about the goods to be sold and get the version number.

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

2. Judge whether the quantity of goods is larger than the quantity purchased. If not, roll back the transaction.

3. If the conditions are met, then reduce the 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 was executed successfully. If it is successful, submit it, otherwise roll back.

Optimistic lock is based on the program, so there is no deadlock, so it is suitable for read-much application scenarios. If there are frequent conflicts and the upper application constantly asks the user to re-operate, which degrades the performance, pessimistic lock is more suitable in this case.

This is the answer to the question about how to use pessimistic lock and optimistic lock in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.