mysql索引列是锁表还是锁行_mysql 如何判断SQL语句触发的行锁还是表锁？

1.情景展示

我们知道，当在对表执行新增、修改、删除，或者select ...for update时，会触发数据库的锁机制；

但如何才能知道当前操作触发的是哪种锁呢？以mysql为例

2.分析

首先，我们需要了解一下mysql的锁机制：

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在mysql中，锁可以分为：行锁和表锁两种类型；

其次，需要确定默认存储引擎。

MyISAM存储引擎：只支持表锁(table-level locking)；

MEMORY存储引擎：只支持表锁(table-level locking)；

BDB存储引擎：支持页面锁，也支持表级锁，已被InnoDB取代(page-level locking)；

InnoDB存储引擎：支持行锁和表锁，默认使用行锁(row-level locking)。

需要确定mysql当前正在使用的是存储引擎是不是InnoDB，只有InnoDB才支持行锁；

锁特点：

原理：

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！！！(下面一起来验证一下)

3.解决方案

办法：

可以来两个事务，一个修改某一行数据，先别提交，然后另一个事务去修改别的行数据，如果不阻塞那应该就是行锁，否则就是表锁。

前提：

关闭mysql的自动提交事务；

在Navicat中，要想进行锁测试，必须关掉mysql的自动提交事务机制，实现方式见文末推荐。

先来验证mysql是不是由索引触发的行锁？

第一步：验证表锁

此时，表中没有任何一个索引。

此时当前号为

更新第一行记录，但并未提交事务。

此时来看表数据是否发生变化

点击底部刷新按钮，数据并未变化；

现在，更新第二条记录，并立刻提交。

我们会发现，它一直处于执行状态，如果时间过长，当前事务被迫中止，结果就是更新失败。

我们需要重新执行更新并提交才行，这次，不能再等待这么长时间，之所以这样弄，是为了方便让大家看效果。

这次，重新执行第二条更新语句，此时第一条更新语句还没有提交：

提交第一条更新语句；

此时，隔壁也会立刻提交完毕。

此时，两条记录都已+1。

如此说来，也就证明了上面的观点的前半句：

如果where条件没有索引，触发的将是表索引。

第二步：验证行锁

因为查询条件中有字段RUSERID，所以，为了验证该表能否触发行锁，加上索引试一试。

点击左上角保存

此时，索引已经加上，还是先更新第一行记录，不提交；

接着更新第二条记录，并提交。

再来看下表数据

这个时候，我们再换一个查询条件，把RUSERID去掉(就是查询条件没有涉及到索引列)。

此时，我们还是先更新第一行记录，不提交；再更新第二条记录(提不提交无所谓，都会失败)。

这时，就证明了上面的观点的后半句：

如果where条件有索引条件项(字段加的有索引)，触发的将是表索引。

还有一种情况是：联合索引。

为上面的查询条件添加联合索引

再次按照上面的方式和查询条件，执行结果如下：

如此说来，联合索引并不能触发行锁，也只能触发表锁。

4.总结

在mysql中，如果存储引擎是InnoDB，结论如下：

第一，select.. for update, insert,update,delete操作都会触发锁，只要涉及到表的改动，都会触发锁；

第二，表中有索引，并不代表触发的锁就是行锁；

当where条件中含有至少一个字段添加了索引，才会触发行锁，否则就是表锁；

除了上面的条件，还有两种特殊情况：

当表中只有联合索引，而且是根据查询条件所创建的索引(总之，就是他俩涉及的列一模一样)，触发的将是表锁，而不是行锁；

当查询结果过多(具体超过全表记录的百分之多少没记录)，索引将会失效，触发全表扫描，此时，也会触发表锁。

第三，行锁并不一定就是只锁了一行，具体视情况而定。

2020-12-09

第四，经过测试，当where条件中携带表主键时，即使不走显式索引(mysql是不是默认会给主键加索引不清楚)，触发的是行锁，而不是表锁。

2020-12-14

第五，经过测试，当执行插入语句insert into时，同样的，当插入的字段含有索引字段时，触发的是行锁；否则，触发的是表锁。

所以，在高并发需要频繁插入的时候，我们不仅可以使用批量插入语句，还可以创建索引来避免锁表。

写在最后

哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

相关推荐：