MySQL optimistic lock and pessimistic lock specific implementation

This article brings you relevant knowledge about mysql, which mainly introduces the specific implementation of MySQL optimistic lock and pessimistic lock. The article introduces the detailed content around the theme. Let’s take a look. ,I hope everyone has to help.

Recommended learning: mysql video tutorial

Many developers may not know about optimistic locking and pessimistic locking in MySQL. Very familiar, but don't know how it is implemented. This article will give a practical case demonstration on this issue so that you can fully understand the difference between the two locks.

Lock classification

MySQL's locks are mainly divided into table locks, row locks and page locks according to their scope. The myisam storage engine only supports table locks, while InnoDB not only supports row locks, but also table locks to a certain extent. According to behavior, it can be divided into shared locks (read locks), exclusive locks (write locks) and intention locks. According to their ideas, they are divided into optimistic locks and pessimistic locks.

Today’s article demonstrates how optimistic locking and pessimistic locking operate in practice.

Table structure

The following SQL statement is the structure of the table:

CREATE TABLE `demo`.`user` (
`id` int(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`sex` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`version` int(1) NULL DEFAULT 1 COMMENT '数据版本号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

Insert simulated data:

BEGIN;
INSERT INTO `user` VALUES (0000000001, '张三', 0, '18228937997@163.com', '18228937997', 1);
INSERT INTO `user` VALUES (0000000002, '李四', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000003, '李四1', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000004, '李四2', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000005, '李四3', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000006, '李四4', 0, '1005349393@163.com', '15683202302', 1);
INSERT INTO `user` VALUES (0000000007, '李四55', 0, '1005349393@163.com', '15683202302', 1);
COMMIT;

Data in the table.

mysql root@127.0.0.1:demo> select * from user;
+----+--------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+--------+-----+---------------------+-------------+---------+
| 1 | 张三 | 0 | 18228937997@163.com | 18228937997 | 2 |
| 2 | 李四 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 3 | 李四1 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 4 | 李四2 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 5 | 李四3 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 6 | 李四4 | 0 | 1005349393@163.com | 15683202302 | 1 |
| 7 | 李四55 | 0 | 1005349393@163.com | 15683202302 | 1 |
+----+--------+-----+---------------------+-------------+---------+
7 rows in set
Time: 0.011s

Pessimistic lock

Pessimistic lock is a relatively negative lock handling method. Seize the lock directly when operating data. Other transactions in progress will wait until the transaction holding the lock releases the lock.

This processing method can ensure the maximum consistency of data, but it can easily lead to problems such as lock timeout and low concurrency. First, we start transaction one and update the data with id=1. At this time, we do not submit the transaction.

mysql root@127.0.0.1:demo> begin;
Query OK, 0 rows affected
Time: 0.002s
mysql root@127.0.0.1:demo> update `user` set name = '张三111111'where id = 1;
Query OK, 1 row affected
Time: 0.004s

Then we start transaction two and update the data with id=1 to see what will happen at this time?

mysql root@127.0.0.1:demo> begin;
Query OK, 0 rows affected
Time: 0.002s
mysql root@127.0.0.1:demo> update `user` set sex = 1 where id = 1;

After we execute the update statement, we are in a waiting state, and the SQL statement will not be executed immediately. This is because once the transaction is not committed, the write lock corresponding to the data with id=1 is not released.

The effect is as follows:

##Through the above example, we can be more intuitive Feel the implementation process of pessimistic locking.

Optimistic lock

Optimistic lock believes that data will not cause conflicts under normal circumstances. Data conflicts will be handled only when the data is modified. How is the conflict discovered here? The conventional method is to add a field such as a version number or timestamp to the data row. (This article uses version as a good way to version, and uses timestamp for the same reason)

The implementation principle of optimistic locking:

When a transaction reads data , read the corresponding version number field, assuming that the version number at this time is 1.
Another transaction also performs the same read operation. When the transaction is committed, 1 is executed for the version number. At this time, the version number of the data row is 2.
• When the second transaction performs a modification operation, conditions are made based on the business data, and a version number is added by default as the where condition. At this time, the version number field in the modification statement does not meet the where condition, and the transaction fails to execute. In this way, the lock function is achieved.

Client one:

mysql root@127.0.0.1:demo> select * from user where id = 1;
+----+------------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+------------+-----+---------------------+-------------+---------+
| 1 | 张三111111 | 0 | 18228937997@163.com | 18228937997 | 1 |
+----+------------+-----+---------------------+-------------+---------+
1 row in set
Time: 0.012s
mysql root@127.0.0.1:demo> update `user` set name = '事务一', version = version + 1 where id = 1 and version = 1;
Query OK, 1 row affected
Time: 0.008s
mysql root@127.0.0.1:demo> select * from user where id = 1;
+----+--------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+--------+-----+---------------------+-------------+---------+
| 1 | 事务一 | 1 | 18228937997@163.com | 18228937997 | 2 |
+----+--------+-----+---------------------+-------------+---------+
1 row in set
Time: 0.009s

The order of executing update statements should be on the client 2. After executing the select, execute.

Client 2:

mysql root@127.0.0.1:demo> select * from user where id = 1;
+----+------------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+------------+-----+---------------------+-------------+---------+
| 1 | 张三111111 | 1 | 18228937997@163.com | 18228937997 | 1 |
+----+------------+-----+---------------------+-------------+---------+
1 row in set
Time: 0.015s
mysql root@127.0.0.1:demo> update `user` set name = '事务二', version = version + 1 where id = 1 and version = 1;
Query OK, 0 rows affected
Time: 0.003s
mysql root@127.0.0.1:demo> select * from user where id = 1;
+----+--------+-----+---------------------+-------------+---------+
| id | name | sex | email | mobile | version |
+----+--------+-----+---------------------+-------------+---------+
| 1 | 事务一 | 1 | 18228937997@163.com | 18228937997 | 2 |
+----+--------+-----+---------------------+-------------+---------+
1 row in set
Time: 0.012s

At this time, according to the structure returned by update, it can be seen that the number of affected rows is 0, and after the select query, The cashback data is also the data of transaction one.

Applicable scenarios

Pessimistic lock: It is more suitable for scenarios where write operations are more frequent. If there are a large number of read operations, it will be added every time it is read. Locks, this will increase a lot of lock overhead and reduce the throughput of the system.

Optimistic locking: is more suitable for scenarios where read operations are more frequent. If a large number of write operations occur, the possibility of data conflicts will increase. In order to ensure the consistency of the data , the application layer needs to constantly re-obtain data, which will increase a large number of query operations and reduce the throughput of the system.

Summary

Both types have their own advantages and disadvantages. Optimistic locks are used for frequent reads, and pessimistic locks are used for frequent writes.

Optimistic locking is suitable for situations where there are relatively few writes, that is, when conflicts rarely occur. This can save the cost of locking and increase the overall throughput of the system. But if conflicts often occur, the upper-layer application will continue to retry, which actually reduces performance. Therefore, in this case, it is more appropriate to use pessimistic locking. The reason why pessimistic locking is used is because the probability of two users updating the same piece of data is high. , that is, when the conflict is serious, pessimistic locking is used.

Pessimistic locking is more suitable for strong consistency scenarios, but the efficiency is relatively low, especially the read concurrency is low. Optimistic locking is suitable for scenarios with more reads and less writes and fewer concurrency conflicts.

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL optimistic lock and pessimistic lock specific implementation. For more information, please follow other related articles on the PHP Chinese website!