InnoDB锁机制和事务管理介绍

本文链接：https://blog.csdn.net/LG772EF/article/details/141230529

前言

本文对《Innodb 锁机制和事务管理介绍》这篇文章中的思考题进行解答。查看完解答后如果对原理有疑问的，可以重新看下原文帮助理解。

初始化信息

验证环境：8.0.28 MySQL Community Server，InnoDB引擎，RR隔离级别。
初始化信息：

-- 问题一、二
drop table if exists layout_test;
drop PROCEDURE if exists layout_test_mock;

create table layout_test (
    `col1` int NOT NULL AUTO_INCREMENT,
    `col2` int not null,
    primary key(`col1`),
    key(`col2`)
) engine = innodb AUTO_INCREMENT = 0;

DELIMITER $$  
CREATE PROCEDURE layout_test_mock()  
BEGIN  
    DECLARE i INT DEFAULT 0;  
    WHILE i < 10000 DO  
        INSERT INTO layout_test (col2) VALUES (FLOOR(RAND() * 100) + 1);  
        SET i = i + 1;  
    END WHILE;  
END$$  
  
DELIMITER ;
call layout_test_mock();
OPTIMIZE table layout_test;

-- 问题三
DROP TABLE IF EXISTS test_lock;
CREATE TABLE `test_lock` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `b` INT DEFAULT NULL,
  `c` INT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`b`)
) ENGINE=INNODB AUTO_INCREMENT=0;
INSERT INTO test_lock VALUES (50, 50, 50), (55, 55, 55), (60, 60, 60), (62, 62, 62), (65, 65, 65), (66, 66, 66);

问题一

问题

下面的操作，为什么update更新的是0行，如何来构造这个场景？

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM layout_test WHERE col2 = 37 limit 5;
+------+------+
| col1 | col2 |
+------+------+
|  343 |   37 |
|  411 |   37 |
|  479 |   37 |
|  575 |   37 |
|  641 |   37 |
+------+------+
5 rows in set (0.00 sec)

mysql> update layout_test set col2 = col2+2 where col2 = 37;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM layout_test WHERE col2 = 37 limit 5;
+------+------+
| col1 | col2 |
+------+------+
|  343 |   37 |
|  411 |   37 |
|  479 |   37 |
|  575 |   37 |
|  641 |   37 |
+------+------+
5 rows in set (0.00 sec)

解答

场景构造：
在这里插入图片描述

解答：涉及MVCC部分的当前读、快照读，以及事务的可见性规则这些知识点。 select查询，使用的快照读。会话B的操作，对于会话A而言不可见。而update，使用的是当前读。

问题二

问题

下面的操作，为什么加锁查询比不加锁快很多，如何来构造这个场景？

mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM layout_test WHERE col1 = 1;
+------+--------+
| col1 | col2   |
+------+--------+
|    1 | 1      |
+------+--------+
1 row in set (8.24 sec)

mysql> SELECT * FROM layout_test WHERE col1 = 1 LOCK IN SHARE MODE;
+------+---------+
| col1 | col2    |
+------+---------+
|    1 | 1000001 |
+------+---------+
1 row in set (0.00 sec)

解答

场景构造：
在这里插入图片描述

-- 存储过程
CREATE PROCEDURE `layout_test_mock_update`()
BEGIN  
    DECLARE i INT DEFAULT 0;  
    WHILE i < 1000000 DO  
        SET i = i + 1;
        update layout_test set col2 = col2+1 where col1 = 1;
    END WHILE;  
END

解答：涉及MVCC部分的当前读、快照读；而会话B中执行了100万次更新（事务），生成了100万个回滚日志。会话A中执行select，使用的快照读，需要根据版本链找可见的版本，执行100万次之后才找到；而lock in share mode，使用的当前读，故速度很快。

状态示意图：
在这里插入图片描述

问题三

问题

session B中的操作，为什么有的成功，有的被阻塞？
ps: 先执行A，在执行B

-- session A
BEGIN;
SELECT * FROM test_lock WHERE b=55 FOR UPDATE;

-- session B
BEGIN;

INSERT INTO test_lock VALUES(40, 40, 40); -- ok
INSERT INTO test_lock VALUES(45, 50, 45); -- ok
INSERT INTO test_lock VALUES(51, 50, 51); -- blocked
INSERT INTO test_lock VALUES(52, 52, 52); -- blocked
INSERT INTO test_lock VALUES(57, 57, 57); -- blocked
INSERT INTO test_lock VALUES(58, 60, 58); -- blocked
INSERT INTO test_lock VALUES(61, 60, 61); -- ok