mysql高级之锁（2）

╰半橙微兮° 2021-09-16 04:16 266阅读 0赞

## 表锁: ##

可以看我上篇博客：[https://blog.csdn.net/tuesdayma/article/details/81901679][https_blog.csdn.net_tuesdayma_article_details_81901679]

## 行锁 ##

**概述：** 行锁主要正针对于innodb（mysql5.5之后默认存储引擎）的存储引擎而言的，每次对数据库操作都会进行行锁。

**特点：**

1、开销大，加锁慢，会出现死锁；发生锁冲突的概率低，并发度很高。

2、会产生间隙锁，从而导致程序无法插入问题。

3、无索引或者索引失效的记录会变成表锁。

4、个人觉得，行锁没有读写锁之分，至少测出来没有。

**优化建议：**

1、尽可能所有的查询条件都使用索引，避免从行锁变成了表锁。

2、尽量缩小更新范围，避免出现间隙锁。

3、合理设计索引，避免出现表锁。

演示

**结论1：**其他session没法读取其他session中未提交的事务（mysql默认事务级别是可重复读，不会出现脏读）  
![这里写图片描述][70]  
**结论2：**当前session更新之后不提交的话（即行锁不释放的话），其他session是无法进行更新操作的。  
![这里写图片描述][70 1]  
**补充1：** 上图也可以看到，其实第二个session执行update操作已经超时了，那么当前session提交事务之后能不能执行第二个session的update操作呢，显然是不行了。。。  
![这里写图片描述][70 2]  
**补充2：** 如果当前session提交的延迟较小，其他session的update操作还是能进行的。这里有个Lock wait timeout的概念。  
![这里写图片描述][70 3]

**结论3：**索引失效或者无索引的话，行锁将变成表锁。（我们增加一个deptno这个varchar类型的字段，然后减伤索引）

alter table test03 add deptno varchar (3)  default '111';
    create index index_deptno on test03(deptno);

![这里写图片描述][70 4]  
![这里写图片描述][70 5]  
![这里写图片描述][70 6]

**间隙锁：** 当我们where条件是一个范围，而这个范围中并不存在的记录，innodb也会对其进行加锁，这就是间隙锁（Next-Key锁），这些不存在的记录成为间隙（GAP）。  
![这里写图片描述][70 7]  
分析：当session1执行所有deptno修改的时候，age=2的那条记录虽然不在，但是也上了锁，这就是间隙锁，这时候如果其他session对间隙锁中的记录进行编辑的话，是会进入阻塞状态的，直到session1提交事务或者超时。如果未超时还是能正常执行的，但是结果中的deptno是不会变了的，新增是能成功的！

危害：当锁定一个范围之后，即使不存在的记录也不会被锁定，从而导致其他session无法进行插入范围之内不存在的记录。

**如何手动锁定一行：**  
![这里写图片描述][70 8]  
**释放行锁：** 在没有超时的前提下，其他session的update操作执行了。  
![这里写图片描述][70 9]  
**结论4：** 行锁是针对编辑操作的，对查询操作是不起作用的。  
![这里写图片描述][70 10]  
**分析行锁：**`show status like 'innodb_row_lock%';`  
![这里写图片描述][70 11]  
Innodb\_row\_lock\_current\_waits：当前正在等待的锁

Innodb\_row\_lock\_time：从系统启动到现在锁定总时间长度

Innodb\_row\_lock\_time\_avg：每次等待所花平均时间

Innodb\_row\_lock\_time\_max：从系统启动到现在，等待最长一次所花的时间

Innodb\_row\_lock\_waits：系统启动到现在总共等待的次数

**结论5：** update的时候，事务不提交的前提下，如果where条件走索引，则是行锁，那么，其他session中就能update其他行的数据；如果where条件没有走索引，那么mysql会将整个表锁住，这个时候其他session是不允许update任何数据的

**结论6：** update的时候，在事务不提交的前提下，另启一个session操作insert的时候：

*  update的where条件走索引：允许insert；
 *  update的where条件不走索引：不允许insert；

**结论7：** update的时候，在事务不提交的前提下，另启一个session操作delete的时候：

*  update的where条件和delete的where条件都走索引：允许delete；
 *  update的where条件不走索引，delete的where条件走索引：允许delete；
 *  update的where条件走索引，delete的where条件不走索引：不允许delete；
 *  update的where条件和delete的where条件都不走索引：不允许delete；

[https_blog.csdn.net_tuesdayma_article_details_81901679]: https://blog.csdn.net/tuesdayma/article/details/81901679
[70]: /images/20210511/314ac583a4874138b75e73f9e2e0b0aa.png
[70 1]: /images/20210511/3eeae3b8a98e4273b09fc8887fdac901.png
[70 2]: /images/20210511/9a6b43adf883488d8ce78a5cf55171fe.png
[70 3]: /images/20210511/c2afd79d1c664a9b8b1af22257dae85a.png
[70 4]: /images/20210511/ba3cd266064041969c4a882d6f11a26c.png
[70 5]: /images/20210511/b272e52fc51049b48a1d2c6bac29032e.png
[70 6]: /images/20210511/8b9a1f636f374e1b8d11908211224a7b.png
[70 7]: /images/20210511/ffc11aaab4cd4ae5b330a88f72c2c82a.png
[70 8]: /images/20210511/168f1628b7d6430c86f58d981b31f313.png
[70 9]: /images/20210511/4d18650bb5604f2fb0ad81ef74e9ab47.png
[70 10]: /images/20210511/d85b15df823245f5b73da2c0a8dab2c5.png
[70 11]: /images/20210511/a99c97290a0b42359c957c2bc019bbcf.png