MySQL InnoDB 未使用索引导致行锁转表锁

MySQL InnoDB是MySQL中使用最广泛的一种存储引擎,支持事务,支持表级锁,另外还实现了行级锁,支持更好的并发写操作。InnoDB行级锁是通过给索引上的索引项加锁来实现的,这就导致只有通过索引条件检索数据,InnoDB才会使用行级锁,否则InnoDB将使用表级锁。下面通过一个例子来验证在未使用索引的情况下,行锁转成了表锁,案例使用的版本为MySQL 5.7.19。

1、表结构:
表tb中只有两个字段,id和name,两个字段上都没有索引,表中也只有5条数据,如下:
# 表结构
CREATE TABLE `tb` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 表中数据
mysql> select * from tb;
+——+——+
| id   | name |
+——+——+
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
|    5 | 5    |
+——+——+
5 rows in set (0.00 sec)
2、未使用索引导致行锁转成表锁
启动两个连接,分别为session1和session2,session1更新id为1的记录,session2更新id为2的记录,两个session更新的数据没有交集。执行情况如下:
session1
session2
备注
begin;
begin;
select * from tb where id=1 for update;
select * from tb where id=2 for update;
session2阻塞
commit;
commit;
发现session2被session1阻塞,只有当session1提交或者回滚之后,session2才能返回,session1与session2更新的数据并没有交集,显然行锁升级成了表锁。
3、加上索引,再次执行
给表tb加上索引,再执行一次,看看session2还会不会阻塞。
alter table tb add index idx_id(id);
session1
session2
备注
begin;
begin;
select * from tb where id=1 for update;
select * from tb where id=2 for update;
session2执行正常,不再阻塞。
commit;
commit;
session2执行不再被阻塞,显然在id字段加了索引之后,由于session1和session2更新的数据没有交集,行级锁能够并行更新数据,session2不再阻塞。
MySQL InnoDB 的这一特性在实际开发中应当特别注意,避免由于未使用索引,行级锁升级为表级锁,导致并发度降低,SQL执行阻塞,影响正常业务。

发表评论