MySQL 与InnoDB 下的锁做朋友 (二)共享锁与排他锁 小灰灰 2022-11-13 00:53 163阅读 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语句默认不会加任何锁类型** ### ### **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
还没有评论,来说两句吧...