Mysql在InnoDB引擎下索引失效行级锁变表锁案例

「爱情、让人受尽委屈。」 2022-10-27 01:50 164阅读 0赞

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。[点击跳转到教程][Link 1]  
先做好准备，创建InnoDB引擎数据表，并添加了相应的索引

DROP TABLE IF EXISTS `innodb_lock`;
    CREATE TABLE `innodb_lock` (
      `a` int(10) NOT NULL,
      `b` varchar(255) NOT NULL DEFAULT '',
      KEY `index_a` (`a`),
      KEY `index_b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    
    -- ----------------------------
    -- Records of innodb_lock
    -- ----------------------------
    INSERT INTO `innodb_lock` VALUES ('1', 'b2');
    INSERT INTO `innodb_lock` VALUES ('3', '3');
    INSERT INTO `innodb_lock` VALUES ('4', '4000');
    INSERT INTO `innodb_lock` VALUES ('5', '5000');
    INSERT INTO `innodb_lock` VALUES ('6', '6000');
    INSERT INTO `innodb_lock` VALUES ('7', '7000');
    INSERT INTO `innodb_lock` VALUES ('8', '8000');
    INSERT INTO `innodb_lock` VALUES ('9', '9000');
    INSERT INTO `innodb_lock` VALUES ('1', 'b1');

然后分别打开两个Mysql终端，设置autocommit自动提交为0，也就是关闭自动提交功能，事务隔离级别处于可重复读状态；查看一下表数据。

MySQL [test_db]> set autocommit = 0;
    MySQL [test_db]> select * from innodb_lock;
    +---+------+
    | a | b    |
    +---+------+
    | 1 | b2   |
    | 3 | 3    |
    | 4 | 4000 |
    | 5 | 5000 |
    | 6 | 6000 |
    | 7 | 7000 |
    | 8 | 8000 |
    | 9 | 9000 |
    | 1 | b1   |
    +---+------+

接下来在第一个终端执行update语句

MySQL [test_db]> update innodb_lock set b ='4001' where a = 4;

然后第二个终端执行update语句

MySQL [test_db]> update innodb_lock set b = '4004' where a = 4;

发现第二个终端处于阻塞状态，因为这里修改的是同一行数据，只有在第一个终端提交之后，锁被释放，第二个终端执行完了SQL，最后第二个终端也commit提交之后数据才会更改，b的值为’4004’。

我们继续

在第一个终端执行update语句

MySQL [test_db]> update innodb_lock set b='4005' where a = 4;

然后第二个终端执行update语句

MySQL [test_db]> update innodb_lock set b = '9001' where a = 9;

发现两条语句都执行成功了。因为修改的不是同一行数据。然后分别commit提交，再查看下数据，发现两行数据都发生了变化。

MySQL [test_db]> select * from innodb_lock;
    +---+------+
    | a | b    |
    +---+------+
    | 1 | b2   |
    | 3 | 3    |
    | 4 | 4005 |
    | 5 | 5000 |
    | 6 | 6000 |
    | 7 | 7000 |
    | 8 | 8000 |
    | 9 | 9001 |
    | 1 | b1   |
    +---+------+

下面是重点，两个终端还是分别操作不同的行。

在第一个终端执行update操作

MySQL [test_db]> update innodb_lock set a = 41 where b =4005;

**注意这里的where条件b=4005。要记得当前表的b字段是字符串类型，并且加了索引，加了索引之后如果查询条件没加引号会导致失效**

然后在第二个终端执行update操作

MySQL [test_db]> update innodb_lock set b='9002' where a = 9;

**发现被阻塞，行锁变表锁**  
一定要注意索引的合理利用！~~

内容转自 [https://www.cnblogs.com/wt645631686/p/8323963.html][https_www.cnblogs.com_wt645631686_p_8323963.html]

[Link 1]: https://www.captainai.net/flyop007/
[https_www.cnblogs.com_wt645631686_p_8323963.html]: https://www.cnblogs.com/wt645631686/p/8323963.html