《浅入浅出MySQL》表锁行锁并发插入

太过爱你忘了你带给我的痛 2022-12-16 15:24 118阅读 0赞

> 知道的越多，知道的越少

### 前言 ###

MySQL是世界上最受欢迎的数据库管理系统之一，其高效、稳定、灵活、可扩展性强并且简单易学，这些特性使其在全球获得越来越多的开发人员的青睐。  
不管是为了程序更高效的运行，还是为了在面试中侃侃而谈，亦或是为了在平时的聊天中装13…  
不管是学生，还是即将步入中年危机的worker…  
如果不甘于 CURD，那就有必要对MySQL进行稍微全面的学习  
本系列旨在记录本人学习 MySQL 的一些体会，采用问答式记录形式，便于知识点记忆  
献给未来的自己，望自勉

注：本篇问答基于 MySQL 8.0

### 正文 ###

> 正值酒足饭饱、昏昏欲睡之际。  
> ‘吱呀’，房门传来一声轻微的提示，只见一只白玉般的纤手推开房门，走进一个少女来。那少女披着一袭轻纱般的白衣，犹似身在烟中雾里，看来约莫二十六七岁年纪，除了一头黑发之外，全身雪白，面容秀美绝俗，只是肌肤间少了一层血色，显得苍白异常…

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTgxNTAwMQ_size_16_color_FFFFFF_t_70_pic_center]

*“hello小帅哥，我是你的面试官 Siri，听之前面试你的同事说你对MySQL有一些了解，那我们接下来就聊聊 MySQL吧*

> 锁是什么，为什么需要锁

*千万别被面试官的外表打乱了气息。若无法集中注意力，可气沉丹田，在心中默念“观自在菩萨，行深般若bai波罗蜜多时，照见五蕴皆空，度一切苦厄。舍利子，色不异空，空不异色，色即是空，空即是色…”*

锁：本质上其实就是一种并发控制的手段（机制）

在多用户（并发）环境中，在同一时间可能会有多个用户操作同一条记录，这会产生冲突（如：更新丢失）。为了解决这些并发带来的问题， 所以引入并发控制机制（锁）。

注：锁的各种 **操作**（包括加锁、检测锁、释放锁、…）都会消耗 **资源**（CPU、内存、数据、…）

> 了解MySQL **行锁**（行级锁定） 和 **表锁**（表级锁定）吗？分别有哪些存储引擎支持

MySQL 对 InnoDB 表使用行锁，以支持多个会话同时进行写访问，使其适合多用户、高并发和OLTP应用程序。（自动行级锁定使这些表适合于存储最重要数据的最繁忙的数据库，同时也简化了应用程序逻辑，因为不需要对表进行锁定和解锁。因此，InnoDB存储引擎是MySQL的默认存储引擎）  
除了InnoDB，MySQL对所有其他存储引擎都使用表锁，每次只允许一个会话更新这些表。这种锁定级别使得这些存储引擎更适合于只读、读多写少或单用户应用程序。

注：InnoDB支持多种粒度锁定，允许行锁和表锁并存（话外音：InnoDB支持表锁）

> MySQL授予表读锁的流程

答：查询表上是否有写锁，以及该表的写锁队列是否有写锁请求。如果都没有，则在其上放置一个读锁。否则，将读锁请求放入读锁队列中。

话外音：读锁和读锁兼容，也就是说，可以同时对同一个表加多个读锁

> MySQL授予表写锁的流程

答：如果表上没有锁，则在其上放置一个写锁。否则，将锁定请求放入写锁队列中。

注：写锁和其他锁都不兼容，也就是说，如果一个表被加了一个写锁，则不能在该表上再加其他锁（包括读锁和写锁）。其他锁请求进入相应的请求队列

> 当一个表上的锁释放时，如果同时有读锁和写锁请求，MySQL会怎么办呢？

MySQL默认表更新比表检索具有更高的优先级。因此，当一个表上的锁被释放时，总是优先处理写锁队列中的请求（优先给该表加写锁），然后对读锁队列中的请求可用。

> 这样（写锁优先）有什么好处和缺点呢？

这确保了即使在表有大量查询操作时，也能对表进行更新操作。但是，如果一个表有很多更新（写锁队列一直有请求），那么SELECT语句将等待，直到所有更新请求处理完毕。

> 读写优先级可以更改吗？

答曰：当然可以，通过设置MySQL服务器系统变量 low\_priority\_updates = 1 ，可以使表检索的优先级高于表更新

注：这个变量只影响仅支持表级锁的存储引擎(如MyISAM、MEMORY和MERGE)。【画外音：这个设置影响不到InnoDB】

> 分别说说表锁和行锁的优缺点及适用场景

行锁的优点：  
1.当不同的会话访问不同的行时，锁冲突减少。（意味着支持更高的并发）  
2.回滚更改较少。（InnoDB会自动检测死锁条件并回滚一个受影响的事务）  
3.可以长时间锁定单个行

表级锁定的优点：  
1.所需的内存相对较少（行锁需要每一行或每组被锁行的内存）  
2.在对表数据进行范围操作时（比如：同时修改多条数据），速度更快，因为仅涉及一个锁。  
3.如果你经常对大部分数据进行分组（group by）操作，或者必须频繁地扫描整个表，则表锁速度更快  
4.不会出现死锁（存储引擎始终在查询开始时一次请求所有需要的锁并始终以相同顺序锁定表）

注：行锁的优点则是表锁的缺点，反之亦然

> 怎么分析表锁争用情况呢？

可以通过检查 Table\_locks\_immediate 和 Table\_locks\_waited  状态变量来分析表锁争用情况。【如果你的系统有性能问题， 恰好Table\_locks\_waited 的值又很高。应该考虑首先优化查询，然后拆分一个或多个表或使用复制】

Table\_locks\_immediate：可以立即授予对表锁定的请求的次数  
 Table\_locks\_waited：无法立即授予对表锁定的请求，需要等待的次数

mysql> SHOW STATUS LIKE 'Table%';
    +-----------------------+---------+
    | Variable_name         | Value   |
    +-----------------------+---------+
    | Table_locks_immediate | 1151552 |
    | Table_locks_waited    | 15324   |
    +-----------------------+---------+

> MyISAM并发插入有了解吗？

如果一个MyISAM表的数据文件没有 空闲块 (在表数据中间删除行数据产生)，则允许查询和插入语句并发执行。 如果文件中间有空闲块，则并发插入会被禁用，但是当所有空闲块都填充有新数据时，它又会自动重新启用。

> 并发插入如何设置？

通过设置MySQL服务器系统变量 concurrent\_insert，开启或关闭 MyISAM 的并发插入（系统默认开启并发插入）。可设置三种模式，如下：

![在这里插入图片描述][20201022130105323.png_pic_center]  
 PS：图片描述中的 ‘孔’ = 文中的 ‘空闲块’

### The End ! ###

*‘小帅哥，来先喝杯水，咱们再继续’*

*‘呵，还想打持久战？只要地球还在转，我就能继续持久下去’：心里如是想到*  
微笑着说：仙女客气

### 写在最后 ###

最近看了很多MySQL锁相关的文章，依然不知道如何简单且全面的表达出来。  
如果文中有描述错误、不易理解的地方，欢迎指正  
如果有其他一些好的建议，请联系我，谢谢…

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTgxNTAwMQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/a82d5cc76a464156a4bcb8f35127fa52.png
[20201022130105323.png_pic_center]: /images/20221123/e211644b155e4b1ea1d5b738fcb36237.png