面试官：MySQL中InnoDB行锁变表锁的原因可能有哪些？

野性酷女 2022-10-14 00:49 17阅读 0赞

## 你知道的越多，不知道的就越多，业余的像一棵小草！ ##

成功路上并不拥挤，因为坚持的人不多。

## 编辑：业余草 ##

cnblogs.com/jian0110/p/12721924.html

## 推荐：https://www.xttblog.com/?p=5186 ##

`InnoDB 不同于 MyISAM 最大的两个特点就是：一是支持事务，二是支持行锁`；毋庸置疑，因为这两个特性大部分都采用 InnoDB 引擎，其中的支持行锁就是 InnoDB 适合多并发优势所在，但是行锁的一些细节没有深入理解过的话，可能会造成一定的误解，造成“**「看似命中索引，走行锁，结果却是表锁，最终导致锁等待情况」**”。

## 一、**「InnoDB行锁的实现方式」** ##

通过给索引上的索引项加锁来实现的，也就意味着：**「只有通过索引条件检索数据，「「InnoDB」」才使用行级锁，否则，InnoDB 将使用表锁」**。这一点在实际应用中特别需要注意，不然的话**「可能导致大量的锁冲突，从而影响引发并发性能」**。

### 实验一：对没有索引的加锁，导致表锁 ###

1）准备工作：建 tab\_no\_index 表，表中无任何索引，并插入数据

![46d758d2219d7540c4574602e635bf48.png][]

2）Session\_1: 我们给 id=1 的行加上排它锁（for update），由于 id 没有索引，实际上是表级锁；

![2a016ff0e8fed0cdb2e3bdd6e575028e.png][]

3）Session\_2：我们给 id=2 的行加上排它锁（for update），由于 id 没有索引，所以去申请表级锁，但是却出现了锁等待！原因就是在没有索引的情况下，InnoDB 只能使用表锁。

![b35ad8a9d240c1ced0a965c4d3106ab8.png][]

备注：MySQL 中的 for update 仅适用于 InnoDB（因为是只有此引擎才有行级锁），并且必须开启事务，在 begin 与 commit 之间才生效。for update 是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以对这行读取但是不能写入或更新，只能等该事务 Rollback, Commit, Lost connection…

### **「实验二：对有索引的键值加锁，会对所有涉及到的数据行加锁」** ###

1）准备工作：对id建索引如下

![ec1adb835e5061418c79a01830968467.png][]2）Session\_1：此时 id 是有索引的，我们对 id=1 and name=1 的一行加排它锁；

![7503fdb54fddab53e917182057c9fc50.png][]

3）Session\_2：访问不同于 Session\_1 的 id=1, name=5 行，但是索引键值是一样的，照样等待锁，锁冲突了。

![d0fe374c5c222a84767e7a78df5ef221.png][]

### 实验三：多个索引时，不同的事务可以使用不同的索引锁定不同的行，`不论什么索引，InnoDB 都会使用行锁对数据加锁`（对有索引的行数据）。 ###

1）准备工作：对 tab\_no\_index 追加 name 索引：

alter table tab\_no\_index add index name(name);

![4caf9fb593894ba46bb32d44236f53ef.png][]

2）Session\_1：开启事务对 id=1 的行加排它锁，即对 name=1 与 name=5 两个数据加锁。

![f27a74e11c7d9d275bc4482ce26f11a5.png][]

3）Session\_2：开启事务对 name=2 行加锁，因为该数据没有被加锁，索引可以获得锁

![6a7479b6bd0bffd431ebed61e51f094f.png][]

4）Session\_3：再对 name=5 的数据进行加锁，由于该数据记录已被 Session\_1 锁定，所以等待获得锁。

![3e4311f352b11bbaa45142405c6d6c3a.png][]

### **「注意事项：即便使用了索引，但还是要看 MySQL 具体对 SQL 的执行计划，不一定能使用到」** ###

如我们对实验三对 name='2' 进行加锁，误以为 name 是 int 类型，本来 name 是有索引的，但是最后结果导致表锁：

![ea6912b0b214ec22946070d74010a81e.png][]

## **「二、间隙锁（Next-Key 锁）」** ##

当用范围条件而不是相等条件检索数据，并请求共享或者排它锁的时候，`InnoDB`会给符合条件的已有数据记录的索引项加锁；对于不在范围内的但并不存在的记录，叫做“间隙(GAP)”，`InnoDB`也会对这个间隙加锁，这就是所谓的间隙锁。

如：`select \* from where id>100 for update` 对 id 大于 100 的数据对加锁，但是此时数据中 id 只有 1,2….100,101，不仅对存在的 101 的记录加锁，还会对大于 101 不存在的数据的间隙加锁。

此外，对使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁，如下：

Session\_1：对不存在的 id=6 的记录加锁

![2a4d6870e0b6becf8ccdff649e4490f5.png][]

Session\_2：插入 id=6 的记录，也会出现锁等待

![60224a0412fbe0be3455064ea3236303.png][]

## 三、**「什么时候使用表锁？」** ##

对于 InnoDB 表，在绝大部分情况下都应该使用行锁，因为事务和行锁往往是我们之所以选择 InnoDB 表的理由，但在个别情况下也使用表级锁；

1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突；

2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。

使用表锁需要注意几点：

1）使用 LOCK TABLES 虽然可以给 InnoDB 加表级锁，表级锁不是 InnoDB 存储引擎层管理的，而是由其上一层 MySQL Server 负责的

2）在用 LOCK TABLES 对 InnoDB 表加锁时需要注意，要将 AUTOCOMMIT 设置为 0，否则 MySQL 不会给表加锁；事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK\_TABLES 隐含提交事务；COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加表级锁。

SET AUTOCOMMIT=0;
    
    LOCK TABLES table1 WRITE, table2 READ,...；
    
    [do something....]
    
    COMMIT;
    
    UNLOCK TABLES;

## 总结： ##

*  **「从设计之初，就应该建立良好的索引机制，避免对关键字段搜索时造成表锁」**
 *  **「避免长时间事务未提交等情况，导致锁冲突，死锁等情况」**
 *  **「不要老是抱怨数据库有问题，应该从自身写的 SQL 分析出发，学会分析（数据库不行大部分是因为 SQL 写的有问题，没错，是自身问题）」**
 *  **「不要总是觉得这是 DBA 该做的事，开发者应该学会基本的 SQL 常识（如 MySQL 的最左索引，回表，索引覆盖等知识），学会基本的优化步骤。」**

本文参考资料：极客时间《MySQL 实战45讲》，http://gk.link/a/10pUz

[46d758d2219d7540c4574602e635bf48.png]: /images/20221014/e05c482d98c6466790a294c5d68154df.png
[2a016ff0e8fed0cdb2e3bdd6e575028e.png]: /images/20221014/e04de2e5b2454509acbc706b0557d016.png
[b35ad8a9d240c1ced0a965c4d3106ab8.png]: /images/20221014/5cf57133ea9746cbabdbaa3714777bcb.png
[ec1adb835e5061418c79a01830968467.png]: /images/20221014/721d4e14273c44bd88c331427002419b.png
[7503fdb54fddab53e917182057c9fc50.png]: /images/20221014/8f4e9d7dcd0740f08c18cd71aecb8626.png
[d0fe374c5c222a84767e7a78df5ef221.png]: /images/20221014/1c1c13ff5ee942918aceaebcdb73f770.png
[4caf9fb593894ba46bb32d44236f53ef.png]: /images/20221014/4b9da86d05424d0f95ea8786301486a1.png
[f27a74e11c7d9d275bc4482ce26f11a5.png]: /images/20221014/11e9bd9f6659418d8de44f49ebe909aa.png
[6a7479b6bd0bffd431ebed61e51f094f.png]: /images/20221014/82ac6dc567d1424f91656dc6a5b96d09.png
[3e4311f352b11bbaa45142405c6d6c3a.png]: /images/20221014/bba77656a8c14a18ba8a32835649b6fd.png
[ea6912b0b214ec22946070d74010a81e.png]: /images/20221014/5e2b892d00444dfe9c3b37217e9973b8.png
[2a4d6870e0b6becf8ccdff649e4490f5.png]: /images/20221014/9647b0b5b90648e088fe94a9ca6ae444.png
[60224a0412fbe0be3455064ea3236303.png]: /images/20221014/3854753a9a64446ea034982d347908b1.png