MySQL 与InnoDB 下的锁做朋友（二）共享锁与排他锁

小灰灰 2022-11-13 00:53 247阅读 0赞

# **前言** #

没有前言，我们直接看内容。

# **正文**     #

**共享锁(Shared Lock)**

> 共享锁、S锁、读锁   ，都是他的叫法。
> 
> 而我，喜欢叫他 **共享读锁** 。

> *A shared (S) lock permits the transaction that holds the lock to read 。   *
> 
> *共享锁允许持有该锁的事务读取。*

这里的共享是， **读读共享** 。

也就是说， 无论是 行级或是表级 ， 如果 对某数据 上了 共享读锁 ，**其他事务可以继续 读（也就是允许持有共享读锁）， 但是 不能写，也就是 读写互斥**。

顺便介绍一下如何  加 共享锁（共享读锁） ：

上表级共享锁，也就是表级共享读锁：

>   
> select  \*  from table（表） **lock in share mode** ；

上行级共享锁，也就是行级共享读锁：

> select  \*  from table（表）where id = 10  **lock in share mode** ；

在这啰唆一点， 注意了，在InnoDB 下， 不是你想用行锁就用行锁的，行锁的触发条件我们再次回顾下（开篇有提到）：

![20210327082447278.png][]

**排他锁(Exclusive Lock)**

> 排他锁 、写锁、X锁  ，都是他的叫法。
> 
> 而我，喜欢叫他 **独占写锁**。

> An exclusive (X) lock permits the transaction that holds the lock to update or delete。
> 
> 独占（X）锁允许持有锁的事务更新或删除。

独占 ，这个词。 打过篮球没，以前初中打篮球不懂事，拿着球就不传。同学就说我，你好独啊。  
  
是的，我很独。 就跟这个独占写锁 （排他锁） 一样，很独。

当事务对某数据加上了 独占写锁 （排他锁） ，只有当前事务能够对这数据执行修改或删除操作。

**其他事务，不能读，不能写** 。 因为 这个锁 很独， 必须等这个很独 的锁 使用完了（释放），其他事务才有机可乘。

所以，独占写锁 （排他锁） 是，**读写互斥、写写互斥的**。

顺便介绍一下如何  加 排他锁（独占写锁） ：

上表级排他锁，也就是表级独占写锁：

> select \* from table **for update** ；

上行级排他锁，也就是行级独占写锁：

> select \* from table where id =10  **for update** ；

在这我再再啰唆一点， 注意了，在InnoDB 下， 不是你想用行锁就用行锁的，行锁的触发条件我们再次回顾下（开篇有提到）：

![20210327082447278.png][]

上面的sql能上行级排他锁，是因为命中了索引， id 是 索引。

--------------------

也许看到这里，你对 共享锁  & 排他锁还只是云里雾里 ，大致知道个什么读读共享、读写互斥、写写互斥啥的。

所以，我们需要再次 从上帝视角再看一次 这两个锁 ，

**红色**   **事务操作一**

蓝色    事务操作二

<table> 
 <tbody> 
  <tr> 
   <td style="width:160px;"></td> 
   <td style="width:378px;"><strong><span style="color:#3399ea;">共享锁（共享读锁）</span></strong></td> 
   <td style="width:382px;"><strong><span style="color:#3399ea;">排他锁（独占写锁）</span></strong></td> 
  </tr> 
  <tr> 
   <td style="width:160px;"><span style="color:#f33b45;">共享锁（共享读锁）</span></td> 
   <td style="width:378px;">可以，兼容，一起读</td> 
   <td style="width:382px;">不可以，不兼容，想写得等共享锁没了</td> 
  </tr> 
  <tr> 
   <td style="width:160px;"><span style="color:#f33b45;">排他锁（独占写锁）</span></td> 
   <td style="width:378px;">不可以，不兼容，上了排他锁，别人啥都不能动</td> 
   <td style="width:382px;">不可以，不兼容，上了排他锁，别人啥都不能动</td> 
  </tr> 
 </tbody> 
</table>

--------------------

那么如果你看到这里，还是对 共享锁  & 排他锁还只是云里雾里。

**那我只有动手了!**

实战介绍，演示 所谓的读读共享、读写互斥、写写互斥 。

在演示读读共享、读写互斥、写写互斥前， 我必须点明一点！

在这篇文章里面，我介绍了一些上 共享锁（共享读锁）、排他锁（独占写锁）的方式 。 但是 可以看到写的查询sql 都是后面加了东西的 ，  **lock in share mode ，for update  .... 等。**

**所以我想点明的一点是，**

### **  如果是使用 普通的查询 ，是 什么锁都没上的！** ###

就好像平时我们经常写的  
  
select \* from table ；  
select \* from table where age=18；

### **select语句默认不会加任何锁类型** ###

###  ###

而排他锁，除了 select .... for update ,InnoDB引擎 默认的修改 、插入、删除（update,insert,delete）都是会给操作的相关数据 加 排他锁的 .

废话不多说，我们上才艺：

准备一些用于测试的数据。

建表：

> DROP TABLE IF EXISTS \`user\`;  
> CREATE TABLE \`user\`  (  
>   \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
>   \`name\` varchar(32) CHARACTER SET utf8 COLLATE utf8\_general\_ci NULL DEFAULT NULL,  
>   \`age\` int(11) NULL DEFAULT NULL,  
>   \`sex\` tinyint(1) NULL DEFAULT NULL,  
>   PRIMARY KEY (\`id\`) USING BTREE  
> ) ENGINE = InnoDB AUTO\_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8\_general\_ci ROW\_FORMAT = Dynamic;
> 
> SET FOREIGN\_KEY\_CHECKS = 1;

搞点模拟数据：

![20210327171138814.png][]  
（id主键索引）

### **第一个小实践：** ###

我们不废话，我们直接上**共享读锁**， 看看是不是能 符合刚才我们的理论 读读共享，读写互斥！

1. 我们先给id=3这数据上个 共享读锁：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70][]

2.基于当前状况， 我们再执行一下查询语句，也是使用共享读锁的：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 1][]

3.那么也是基于当前情况，我们再执行一下使用排他写锁的查询语句，可以发现 读写互斥了：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 2][]

4.验证下，我们查看当前是否存在事务在等待锁：

可以从结果中看出 事务请求id 34847在等待锁：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 3][]

我们再查询一下，那些事务在使用锁，  
可以从结果看出，34844事务在使用S锁，也就是共享读锁；

而34847事务 在使用 X锁， 也就是排他写锁（但是由于共享读锁先上了，所以读写互斥了），所以造成了34847事务 在等待锁。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 4][]

5.那么如果我们一直不 COMMIT 共享读锁， 34847事务 会永无止息地等待锁吗？   那么肯定是不可能允许这种一直等待的场景的：

所以mysql会有个等待锁资源超时的机制，这种情况就会直接返回查询失败的结果。  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 5][]

### **根据第一个小实践，我们得出一个很明显的结论：** ###

当某数据上了 **共享读锁 S** 时， 只允许其他事务上共享读锁 S， 因为读读共享；

不允许其他事务上 独占写锁 X（除非把这个共享读锁S 释放掉），因为读写互斥。

### **第二个小实践：** ###

1.我们直接给某行数据上个排他写锁 X (注意我们的事务是没有执行COMMIT的) ：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 6][]

2. 我们接下来去 通过共享读锁去获取数据，看看会发生什么？

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 7][]

这就是 独占写锁 X 的 **读写互斥、写写互斥 （写写互斥的场景就不展示了）.**

再验证下，我们看下是不是存在事务在等待锁资源：  
![20210407194104648.png][]

3.那么如果独占写锁一直不释放，其余事务时一直等待吗？

也是一样，会等待超时返回查询失败：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 8][]

补充一个小实践：

1. 还是一样，先给某个数据上 独占写锁，不COMMIT：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 9][]

2. 执行普通的查询，select ：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 10][]

可以看到，普通的select语句能正常获取，为什么？ 因为前面我们提到了：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 11][]

所以我必须再啰唆一下，所谓的 读读共享，读写互斥，写写互斥 ，都是对于锁资源来说的，如果你都没有锁资源竞争，那肯定不存在什么互斥什么互斥了。

[20210327082447278.png]: /images/20221022/f3ef296f61f941e7bd56a72b6f673614.png
[20210327171138814.png]: /images/20221022/2e384545081b4ca2880d468985a5aa2f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70]: /images/20221022/e2916e8f0c814b168af5205ff56f7ca0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 1]: /images/20221022/93d04af7d0224c8abc4715be0f6f6d24.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 2]: /images/20221022/538611daa2e340f2823a1c2ed119806f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 3]: /images/20221022/88e12ee336714253944f6e9374b259f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 4]: /images/20221022/1085d50b4c184539abaa74eb033d352c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 5]: /images/20221022/a0d6995e05144e3582c4980992278e45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 6]: /images/20221022/54f17cde1e5e437f903b4e06e6c9dadb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 7]: /images/20221022/5ab2a3dd451d4281a03ca66ea184345a.png
[20210407194104648.png]: /images/20221022/01107851874f405985153891171aa1e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 8]: /images/20221022/ca8999bf0a15429d8d595cf3b13de8a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 9]: /images/20221022/08438fcc0b604a3ca8e3fc97ad76b700.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 10]: /images/20221022/8503526e91d24871be9e5605a8b062cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1Mzg3OTQw_size_16_color_FFFFFF_t_70 11]: /images/20221022/3e64b45f60524b4995b046c62dccacf2.png