MySQL Innodb 引擎中预防 Update 操作上升为表锁

一、MySQL 如何预防 Update 上升为表锁

在 MySQL 中，进行任何数据的 修改 操作都会进行一定的锁操作，而锁的不同直接导致性能的差异。例如 MyISAM 引擎，更新时采用表锁，并发性较差。而 Innodb 引擎支持事务，更新时采用行锁，锁的粒度更细，所以并发性较高。

由于表锁的粒度过大，即使只有部分行被修改，也会阻塞其他事务对整个表的写操作，限制了系统的吞吐量和响应速度。对于在Innodb 引擎中，虽然采用了粒度更细的行锁，但也不是所有的数据修改操作都是仅锁住相关的行，有时很可能不注意就导致了表锁。

下面通过一个实验进行深入：

例如有如下表结构：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_name_age` (`name`,`age`),
  KEY `name` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

写入一些测试数据：

INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`) VALUES (1, '小明', 18, '男');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`) VALUES (2, '小红', 19, '女');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`) VALUES (3, '小蓝', 16, '女');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`) VALUES (4, '小王', 17, '男');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`) VALUES (5, '张三', 18, '男');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`) VALUES (6, '李四', 19, '男');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`) VALUES (7, '王五', 20, '男');
INSERT INTO `test`.`user` (`id`, `name`, `age`, `sex`) VALUES (8, '赵六', 21, '男');

下面尝试在事务中修改姓名为张三的年龄为 20 岁，注意这里先不要着急提交事务：

BEGIN;
UPDATE user SET age = 20 WHERE name = '张三';

下面查看下前正在发生的数据锁情况：

SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME = "user"

从这里可以看出，锁住了表里所有的数据，已经上升为了表锁，但是上面仅更新了姓名为张三的数据，怎么会锁住那么多数据呢？

这是因为 name 字段没有索引，要找到姓名为张三的数据，就要进行全表扫描，但是 update 的时候要保证数据的一致性，所以此时相关的数据就是全部的表数据，因此也就相当于表锁了。

那怎么降低锁的粒度呢，既然是因为name 字段没有索引，那给 name 增加索引，再次进行上面实验呢。

增加索引：

ALTER TABLE user ADD INDEX index_name(name);

再次尝试修改但不提交事务：

BEGIN;
UPDATE user SET age = 20 WHERE name = '张三';

查看下前正在发生的数据锁情况：

SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME = "user"

可以看出此时并没有锁住全部的数据，但是锁住的 '张三', 5 和 '李四', 6 是什么呢？

这其实是索引信息，如果修改操作涉及到了非主键索引，MySql会首先锁住非主键索引，再锁定具体数据的主键索引。至于会锁住李四就是 MySQL 中 Next-Key 和 GAP 间隙锁的原因了，当准备更新张三时，以防止在这个范围内插入新的记录，所以将临近的李四也进行上锁。

从上面结果看增加了索引后已经解决了表锁的问题，但还是会锁住多余的内容，下面直接尝试根据主键进行修改：

BEGIN;
UPDATE user SET age = 20 WHERE id = 5 ;

再次查看下前正在发生的数据锁情况：

SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME = "user"

此时就仅锁住相关的数据了。

二、总结

从上面的实验过程来看，MySQL 中的修改操作很有可能导致表锁，因此最好在更新语句中使用主键列或其他索引列进行筛选。另外索引最好设置在不经常变更的字段上比较好，不然容易造成冲突死锁的情况。

原文地址：https://blog.csdn.net/qq_43692950/article/details/137118053

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！