MySQL锁模块详解（手把手实战）

比眉伴天荒 2023-02-10 07:51 66阅读 0赞

## 锁模块 ##

### 1. MyISAM与InnoDB关于锁方面的区别是什么 ###

*  MyISAM支持表级锁，不支持行级锁
 *  InnoDB默认行级锁，也支持表级锁

#### MyISAM表 ####

由于MyISAM无事务，所以我们想测试锁，需要执行大数据量语句。  
**打开多个查询窗口（每个窗口代表一个SESSION，后用S1、S2代表两个窗口）**  
读锁（共享锁）

1.  用S1，查询一个MyISAM表前200W条数据，同时S2更新第200001条，S2进入阻塞，等待S1查询执行完，S2才能执行
2.  用S1，查询一个MyISAM表前200W条数据，同时S2查询第200001条，S2直接执行

> 这种情况是S1上共享锁，MyISAM上读锁（共享锁），S2能上共享锁，不能上排他锁

写锁（排他锁）

1.  用S1，更新一个MyISAM表前200W条数据，同时S2更新第200001条，S2进入阻塞，等待S1查询执行完，S2才能执行
2.  同时S2查询第200001条，第二个窗口会进入阻塞，等待S1查询执行完，S2才能执行

> 这种情况是S1上排他锁，MyISAM上写锁（排他锁），S2无法上共享锁、排他锁

#### InnoDB ####

读锁（共享锁）

1.  用S1，开启事务，查询一个InnoDB表第3条数据，同时S2更新第3条，S2进入阻塞，等待S1进行commit提交事务，S2才能执行成功![在这里插入图片描述][20200520204033755.gif_pic_center]
2.  用S1，查询一个InnoDB表第3条数据，同时S2更新第4条，S2直接执行成功  
    ![在这里插入图片描述][20200520203309436.gif_pic_center]  
    这里说明InnoDB行级锁，共享锁是针对行进行上锁的

> xxx查询语句 lock in share mode;通过这种方式给sql语句加共享锁

以上我们测试都是通过ID来选中数据库记录，进行测试InnoDB表的行级锁，我们的ID是这张表的主键（聚簇索引），**如果我们对非索引字段查询，上共享锁，那么会上表锁，而非行级锁**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70]

#### 适合场景 ####

MyISAM

*  频繁执行全表count语句
 *  对数据增删改频率不高，查询非常频繁
 *  没有事务

InnoDB

*  数据增删改相当频繁
 *  支持事务

#### 数据库锁的分类 ####

*  按照锁粒度，表级锁、行级锁、页级锁
 *  按锁级别划分，可分为共享锁、排他锁
 *  按锁方式划分，自动锁、显示锁
 *  按操作划分，DML锁、DDL锁
 *  按使用方式，悲观锁、乐观锁

乐观锁、悲观锁  
实现一个乐观锁

### 2. 数据库事务的四大特性 ###

**ACID**

*  原子性（Atomic）要么全成功，要么全失败
 *  一致性（Consistency）满足完整性约束，A+B共1w元，A给B转多钱，都总价还是1W
 *  隔离性（Isolation）各个事务之间不相互影响
 *  持久性（Durability）当系统与介质发生故障，DBMS必须保证数据的写入必须被保存下来

### 3. 事务隔离级别以及各级别下的并发访问问题 ###

事务隔离级别  
read uncommitted、read committed  
事务并发访问引起的问题

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 1]这种情况，就会发现，出现一个更新丢失的问题。

#### 脏读 ####

我们设计一个场景，理解一下脏读

1.开启两个数据库sessions窗口，分别设置数据库事务隔离级别为read uncommitted  
2. S1窗口，开启事务，更新id为1的余额1000-100，不提交事务  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 2]  
3. S2窗口，开启事务，查询id为1的余额（900），已经产生脏读  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 3]  
4. S1窗口，回滚事务，余额在S1看来是1000元了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 4]  
5. 此时S2窗口，更新900+200，提交事务  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 5]  
6. 完蛋，用户莫名其妙丢100！

> 设置事务隔离级别为read committed（MySQL、Oracle默认级别）以上，防止读取未提交事务数据，设置后第二步S2会读取1000（S1未提交事务不被读取）

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

开启repeatable-read以上隔离级别解决，我们开启repeatable-read级别再试验

1.  S1开启事务，执行1000+500不提交
2.  同时S2开启事务，查到1000（S2此时如果repeateble-read以下级别，S2多次读取会读取到1000突然变成1500）
3.  S1提交，此时S2查询仍然为1000（不重复读），拿到多少就是多少，不管别人改没改，我就是1000，这种模式下，我们要使用update set balance = balance+100这种就能避免使用旧数据进行更新

> repeatable-read以上，会让事务读取到的信息，是开启事务时数据，不论别的事务是否 已经修改数据

#### 幻读 ####

幻读可以在serializable级别避免，查询操作都会加锁，更新更新操作会被blocking。（MySQL中repeatable-read下也会防止幻读）  
幻读场景

1.  MySQL开启read-committed，S1开启事务，查询全表，共三条
2.  S2开启事务，执行插入，提交事务
3.  S1更新全表，发现更了四条，出现幻读

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

### 4. InnoDB可重复读隔离级别下如何避免幻读 ###

主要通过以下两种情况避免幻读

*  表象：快照读（非阻塞读）伪MVCC  
    表象避免幻读，是RR下查找数据，第一次读取创建快照，后面读取都是读取本次快照，不论别的事务是否提交相关更改，我们都不知道，掩耳盗铃
 *  内在：next-key锁（行锁+gap锁）  
    上了锁，你别的操作不会修改我锁定的区间了，我就不会幻读

首先我们理解下面两个概念，当前读和快照读

*  当前读：select xxx lock in share mode，select xxx for update、update、delete、insert  
    当前读就是加了锁（共享、排他都算）的增删改查，加锁后的记录不能被别的事务修改  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 7]
 *  快照读，非serializable模式下select为快照读

#### 对主键索引或者唯一索引会用GAP锁么？ ####

##### GAP区间 #####

打开MySQL官网，我们看看它对gap区间的描述，分段进行区间划分  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 8]

##### 实战验证 #####

创建一个表，id（唯一键unique\_id）、name（主键），插入数据ID为1、2、3、5、6、9，name字段随意  
我手动划分一下GAP区间【1，2】【2，3】【3，5】【5，6】【6，9】【9，+∞】

1.  如果where条件全部命中，不会使用GAP锁，只会加记录锁（行锁）  
    RR模式下，S1、S2开启事务，S1查询id为9，S2插入id为9，会被blocking，插入id为7则不会锁，说明他开的是行锁，因为锁9，GAP锁会锁住【6，9】区间  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 9]
2.  where条件未全部命中、全不命中会开GAP锁  
    RR模式下，S1、S2开启事务，S1查询id为5、7、9（其中没有id为7的数据，部分命中），S2插入id为8，会被blocking，因为此时使用GAP锁，锁住区间【5，6】【6，9】

#### GAP锁会用在非唯一索引或者不走索引的当前读 ####

*  非唯一索引  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 10]  
    如上图所示，S1、S2开启事务，S1查询id=9，区间【6，9】【9，11】被锁，想执行插入id为此区间内的，都被blocking
 *  不走索引  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 11]  
    如图所示，**id不是索引了哈！！**，S1删除id=9，全表都被锁了（所有区间），尽量避免这种情况，会影响性能

### 5. RC、RR级别下的InnoDB的非阻塞读如何实现 ###

数据库有着三个隐藏的字段，通过这三个字段，通过这些字段我们实现了记录追踪，历史回朔

*  数据行里的DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID字段
 *  undo日志（每操作一次数据，顺序增加一个日志）
 *  read view（快照本照了）

#### 数据行DB\_TRX\_ID、DB\_ROLL\_PTR、DB\_ROW\_ID字段 ####

1.  DB\_TRX\_ID（最后一次操作事务ID）
2.  DB\_ROLL\_PTR（回滚指针）
3.  DB\_ROW\_ID（InnoDB表中在没有默认主键的情况下会生成一个6字节空间的自动增长主键）

#### undo日志 ####

第一次修改数据12-》32  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 12]第二次修改数据13-》43  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 13]我们看到日志被记录下来，如果 我们第二次事务回滚了，是找到回滚指针指向的undo日志，再回滚操作

#### read view ####

RR下，事务读取数据的时机非常重要，第一次读取后数据会创建快照，以后会读取快照，事务提交前，再读取都是读取第一次读取的数据

RC下，每一次数据读取，都会创建一个新的快照，所以RC能读取到别人提交的结果

[20200520204033755.gif_pic_center]: https://img-blog.csdnimg.cn/20200520204033755.gif#pic_center
[20200520203309436.gif_pic_center]: https://img-blog.csdnimg.cn/20200520203309436.gif#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200520210238246.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200522155218669.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200522161719554.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200522161827434.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200522161903145.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200522161930126.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200522192121845.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200523095949802.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200523110923291.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200523104852309.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200523110817390.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200523114743907.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200523101016533.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/2020052310130192.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ya2Fpemk=,size_16,color_FFFFFF,t_70