MySQL优化之MySQL锁机制

喜欢ヅ旅行 2022-12-01 15:41 157阅读 0赞

以下是最近学习MySQL的一些笔记，推荐一起阅读：

[MySQL逻辑架构介绍][MySQL]

[MySQL性能分析][MySQL 1]

[MySQL索引优化][MySQL 2]

[MySQL查询截取分析][MySQL 3]

[MySQL锁机制][MySQL 4]

[MySQL主从赋值][MySQL 5]

# MySQL锁机制 #

## 概述 ##

### 数据库锁 ###

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

### 锁分类 ###

*  按照数据操作粒度划分：表锁/行锁
 *  对数据操作的类型划分：读锁/写锁
 *  **读锁**（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响
 *  **写锁**（排他锁）：当前写操作没有完成前，阻断其他写锁和读锁

## 表、行、页锁 ##

### 表级锁 ###

*  特点：偏向MyISAM引擎，开销小，加锁快；无死锁；锁定粒度大，发成锁冲突的概率高，并发度低。
 *  偏向于读取

\#\#\#\#使用表级锁

**表级读锁**（使用MyISAM引擎的表，因为MyISAM是表级锁）：

lock table my_table read;
    unlock tables;

锁定后：

*  使用读锁的一方：仅加锁表可读，不可写
 *  其他会话：可读加锁表，不可写（阻塞），其他表读写不受影响

**表级写锁**

lock table my_table write;
    unlock tables;

锁定后：

*  使用写锁的一方：仅加锁表可读可写，其他表不可读不可写
 *  其他会话：不可读、不可写加锁表（阻塞）

MyISAM在读操作是会自动给表加读锁，在写操作时会自动给表加写锁

#### 表级锁分析 ####

*  查看哪些表被加锁
    
        show open tables;
 *  查看锁争用情况
    
        show status like 'table%';
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 1][]
    
     *  Table\_locks\_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数
     *  Table\_locks\_waited：出现表级锁争用而发生等待的次数，每等待一次数值加1

MyISAM对锁的调度是写优先，因为写锁会同时阻塞表的读写，所以最好不要用于写多的场景，因此MyISAM偏向于读取

### 行级锁 ###

*  特点：偏向InnoDB引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发成锁冲突的概率低，并发度高
 *  InnoDB相比于MyISAM的不同：事务、行级锁
 *  偏向于写入

#### 事务 ####

##### 事务及其ACID属性 #####

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

##### 并发事务处理出现的问题 #####

###### 更新丢失 ######

*  问题：多个事务同时更新同一条记录，最后的更新覆盖了前面的更新
 *  解决方案：在前面的事务完成前，另外的会话不能访问同一记录

###### 脏读 ######

*  问题：事务A读取到了事务B已经修改但是没有提交的数据，如果事务B此时回滚，则A读取的数据无效
 *  解决方案：事务隔离级别

###### 不可重复读 ######

*  问题：事务A读取到了事务B已经提交的修改后的数据
 *  解决方案：事务隔离级别

###### 幻读 ######

*  问题：事务A读取到了事务B新增的数据，前后数据量不同
 *  解决方案：事务隔离级别

**脏读的重点是读另一个事务未提交的数据(假若那个事务RollBack, 则这数据就是无效的):某个事务已更新一份数据, 另一个事务在此时读取了同一份数据, 由于某些原因, 前一个RollBack了操作, 则后一个事务所读取的数据就会是不正确的**

**不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了**

**幻读的重点在于新增或者删除: 同样的条件, 第1次和第2次读出来的记录数不一样**

[https://www.cnblogs.com/JohnABC/p/3521061.html][https_www.cnblogs.com_JohnABC_p_3521061.html]

##### 事务隔离级别 #####

<table> 
 <thead> 
  <tr> 
   <th align="center">隔离级别</th> 
   <th align="center">数据一致性级别</th> 
   <th align="center">脏读</th> 
   <th align="center">不可重复读</th> 
   <th align="center">幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">读未提交</td> 
   <td align="center">最低级别，只能保证不读取物理损坏的数据</td> 
   <td align="center">YES</td> 
   <td align="center">YES</td> 
   <td align="center">YES</td> 
  </tr> 
  <tr> 
   <td align="center">读已提交</td> 
   <td align="center">语句级</td> 
   <td align="center"><strong>NO</strong></td> 
   <td align="center">YES</td> 
   <td align="center">YES</td> 
  </tr> 
  <tr> 
   <td align="center">可重复读</td> 
   <td align="center">事务级</td> 
   <td align="center"><strong>NO</strong></td> 
   <td align="center"><strong>NO</strong></td> 
   <td align="center">YES</td> 
  </tr> 
  <tr> 
   <td align="center">可序列化</td> 
   <td align="center">最高级别，事务级</td> 
   <td align="center"><strong>NO</strong></td> 
   <td align="center"><strong>NO</strong></td> 
   <td align="center"><strong>NO</strong></td> 
  </tr> 
 </tbody> 
</table>

事务隔离越严格，并发度越小，消耗性能越高

*  MySQL默认事务隔离级别：可重复读（Repeatable read）
 *  查看当前事务隔离级别
    
        show variables like '%tx_isolation%';
        select @@tx_isolation;
    
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 3][]
 *  设置事务隔离级别
    
        set [SESSION|GLOBAL] transaction isolation level read uncommitted;
        
        set [SESSION|GLOBAL] transaction isolation level read committed;
        
        set [SESSION|GLOBAL] transaction isolation level repeatable read;
        
        set [SESSION|GLOBAL] transaction isolation level serializable;

#### 使用行级锁 ####

如果使用了InnoDB引擎的表，就默认使用行级锁

在行级锁中：

*  事务A更新m行，事务B更新n行，两者互不影响
 *  事务A更新m行，事务B也更新m行，则事务B阻塞

#### 行锁升级为表锁 ####

由于Mysql 的行锁是通过索引实现的，如果遇到索引列失效的情况，则行锁会升级为表锁，从而造成修改其他行的事务阻塞

**当 Where 查询条件中的字段没有索引时，更新操作会锁住全表**

首先两个客户端均关闭autocommit

set autocommit=0;

假设有表user如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 5][]

##### 无索引情况 #####

现在user表的username字段没有加索引，当username作为where中判断字段的时候，会触发全表扫描：

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

此时如果进行事务A，并且使用带where条件为username的更新update操作：

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xwqLeeD7-1598536132074)(/Users/dzzhyk/Desktop/apeBook/src/image-20200814235529847.png)\]][img-xwqLeeD7-1598536132074_Users_dzzhyk_Desktop_apeBook_src_image-20200814235529847.png]

此时如果事务B想要修改同表中的另一行，按照行锁理论不会阻塞，实际会发生阻塞：

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hTZQWQ19-1598536132075)(/Users/dzzhyk/Desktop/apeBook/src/image-20200814235543926.png)\]][img-hTZQWQ19-1598536132075_Users_dzzhyk_Desktop_apeBook_src_image-20200814235543926.png]

只有当事务A commit之后才，事务B才会提交（如果有等待过期时间，则事务B的update会取消），此时发生了行锁升级为表锁的现象。

##### 索引失效情况 #####

现在演示索引失效导致行锁升级为表锁的情况：

首先给username添加索引index\_username

alter table user add index index_username(username);

客户端A进行事务操作，修改记录1：

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-edm0bLtc-1598536132075)(/Users/dzzhyk/Desktop/apeBook/src/image-20200814235939532.png)\]][img-edm0bLtc-1598536132075_Users_dzzhyk_Desktop_apeBook_src_image-20200814235939532.png]

此时客户端B想要修改记录4，按照行锁理论，双方应该互不影响，**但是此时客户端B产生阻塞**：

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1dupQoXq-1598536132076)(/Users/dzzhyk/Desktop/apeBook/src/image-20200815000037240.png)\]][img-1dupQoXq-1598536132076_Users_dzzhyk_Desktop_apeBook_src_image-20200815000037240.png]

此时即为索引失效导致了行锁升级为表锁的情况，只有在事务A结束之后事务B才能进行。

#### 间隙锁 ####

![在这里插入图片描述][2020082721513673.png_pic_center]

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KPenLi2a-1598536132078)(/Users/dzzhyk/Desktop/apeBook/src/image-20200814233158985.png)\]][img-KPenLi2a-1598536132078_Users_dzzhyk_Desktop_apeBook_src_image-20200814233158985.png]

例如使用下面的SQL进行范围内更新：

update user set password='123' where id > 1 and id < 5;

则预期更新id=2,3,4号一共3条记录，但是此时表内容如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 7][]

可以看到，3号id的记录缺失，出现了”间隙“

假设事务A执行上面的update更新操作：

![在这里插入图片描述][20200827215214194.png_pic_center]

事务B此时**恰巧**正在执行插入id=3的记录的操作，则会造成事务B也被阻塞，即使事务B是无辜的：

![在这里插入图片描述][20200827215223240.png_pic_center]

只有在事务A结束或者事务B超时之后才会解除阻塞，这就是间隙锁的情况

#### 如何锁定一行 ####

假设想要人为进行一些改动更新，并且此时有其他事务想要对这一行进行操作，如何把这一行锁定，从而保证人工操作进行顺利呢？

答案是使用for update的方式：

begin;
    
    select * from table_name where id=? for update;
    
    ...
    
    commit;

这样就锁定了id=?这一行，在提交commit之前，其他人想要对这一行进行修改都会阻塞

#### 行级锁分析 ####

show status like 'innodb_row_lock%';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 8][]

各参数含义：

*  Innodb\_row\_lock\_current\_waits：当前正在等待锁定的数量
 *  **Innodb\_row\_lock\_time**：从系统启动到现在锁定总时间长度
 *  **Innodb\_row\_lock\_time\_avg**：每次等待所花平均时间
 *  Innodb\_row\_lock\_time\_max：最长一次等待时间
 *  **Innodb\_row\_lock\_waits**：总等待次数

加粗参数比较重要，越小越好；如果出现了异常，则可以使用show profile来进行检查

#### 行锁优化建议 ####

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 9][]

### 页级锁 ###

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

[MySQL]: https://blog.csdn.net/weixin_43826242/article/details/108269094
[MySQL 1]: https://blog.csdn.net/weixin_43826242/article/details/108269176
[MySQL 2]: https://blog.csdn.net/weixin_43826242/article/details/108269212
[MySQL 3]: https://blog.csdn.net/weixin_43826242/article/details/108269252
[MySQL 4]: https://blog.csdn.net/weixin_43826242/article/details/108269379
[MySQL 5]: https://blog.csdn.net/weixin_43826242/article/details/108269489
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/c3d85b0b77b743f5a923ed0b0053a171.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/ed71677e02254f9d8ffa14a11c66b262.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/a26a977bdcc54601b7e903ac692a1580.png
[https_www.cnblogs.com_JohnABC_p_3521061.html]: https://www.cnblogs.com/JohnABC/p/3521061.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/a667d0c3184d41cea599d9f95bf14e52.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/5b334ea26e454f13bda387fdceed98b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/21d0a08e8cda4f8fb7a49af364ec59b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221123/09001b57805e4d9e88a36b0d55ff4e1b.png
[img-xwqLeeD7-1598536132074_Users_dzzhyk_Desktop_apeBook_src_image-20200814235529847.png]: /images/20221123/b0ac27eec2fd4ccd98bf8db37261f9f7.png
[img-hTZQWQ19-1598536132075_Users_dzzhyk_Desktop_apeBook_src_image-20200814235543926.png]: /images/20221123/f403c3ed478746af967d568228873e3b.png
[img-edm0bLtc-1598536132075_Users_dzzhyk_Desktop_apeBook_src_image-20200814235939532.png]: /images/20221123/d7c611cfa5864090bf8ba37b58103cd0.png
[img-1dupQoXq-1598536132076_Users_dzzhyk_Desktop_apeBook_src_image-20200815000037240.png]: /images/20221123/0705ae419cb444109fdf1d9370c5f0b1.png
[2020082721513673.png_pic_center]: /images/20221123/238dbd50c2b644e7959e00e64ccfff80.png
[img-KPenLi2a-1598536132078_Users_dzzhyk_Desktop_apeBook_src_image-20200814233158985.png]: /images/20221123/1a2099a215f7477f91b11ecb059b7d33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221123/9f974f609f73481f885b125763a05b19.png
[20200827215214194.png_pic_center]: /images/20221123/5fe949b2f8c64a1a97571780318dd82b.png
[20200827215223240.png_pic_center]: /images/20221123/ed758cb2d8b54b5faf87fc66f43df392.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221123/089fc0f209fc4e5db9c2152258405ff0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzgyNjI0Mg_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221123/a7516fc9aa064cf09348cd5c4e62a8f8.png