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

This article mainly explains "what's the difference between mysql optimistic lock and pessimistic lock". Interested friends might as well take a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "what's the difference between mysql optimistic lock and pessimistic lock"!

Difference: pessimistic lock is pessimistic about the processing of data, always thinking that there will be concurrent conflicts, when obtaining and modifying data, others will modify the data; so in the whole process of data processing, the data needs to be locked. Optimistic lock is optimistic about the processing of data, that generally there will be no conflict, only when the data update is submitted, the data conflict will be detected.

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

Pessimistic lock and optimistic lock are concepts defined by people, which you can understand as an idea and a common means of dealing with concurrent resources.

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

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.

At this point, I believe that everyone on the "mysql optimistic lock and pessimistic lock what is the difference" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.