彻底把 MySQL的锁搞懂了!
本文首发于公众号“猿java”
最近，某同事在生产上遇到一个 MySQL 死锁的问题，于是在帮忙解决问题后，特意花了一周的时间，把 MySQL 所有的锁都整理了一遍，今天就来一起聊聊 MySQL锁。
申明：本文基于 MySQL 8.0.30 版本，InnoDB 引擎
MySQL 数据库锁设计的初衷是处理并发问题，保证数据安全。MySQL 数据库锁可以从下面 3个维度进行划分：
- 按照锁的使用方式，MySQL 锁可以分成共享锁、排它锁两种；
- 根据加锁的范围，MySQL 锁大致可以分成全局锁、表级锁和行锁三类；
- 从思想层面上看，MySQL 锁可以分为悲观锁、乐观锁两种；
我们会先讲解共享锁和排它锁，然后讲解全局锁、表级锁和行锁，因为这三种类别的锁中，有些是共享锁，有些是排他锁，最后，我们再讲解 悲观锁和乐观锁。
为什么要掌握 MySQL 锁
作为经常和 MySQL 数据库打交道的程序员来说，编写业务 SQL 语句是在所难免，编写高性能的 SQL 语句更是一种技术能力的体现。锁作为 MySQL 数据库一个非常重要的知识点，不但是面试中的高频问题，更是通往技术高P岗位的必备技能。
共享锁&排他锁
1.共享锁
共享锁，Share lock，也叫读锁。它是指当对象被锁定时，允许其它事务读取该对象，也允许其它事务从该对象上再次获取共享锁，但不能对该对象进行写入。 加锁方式是：
# 方式1
select ... lock in share mode;
1. 方式2
select ... for share;如果事务T1 在某对象持有共享(S)锁，则事务T2 需要再次获取该对象的锁时，会出现下面两种情况： - 如果T2 获取该对象的共享(S)锁，则可以立即获取锁； - 如果T2 获取该对象的排他(X)锁，则无法获取锁；
为了更好的理解上述两种情况，可以参照下面的执行顺序流和实例图：
给user表加共享锁
| 加锁线程 sessionA | 线程B sessionB |
|---|---|
| #开启事务 begin; | |
| #对user整张表加共享锁 select * from user lock in share mode; | |
| #获取user表上的共享锁ok，select操作成功执行 select * from user; | |
| #获取user表上的排他锁失败，操作被堵塞 delete from user where id = 1; | |
| #提交事务 #user表上的共享锁被释放 commit; | |
| #获取user表上的排他锁成功，delete操作执行ok delete from user where id = 1; |
img.png
给user表id=3的行加共享锁
| 加锁线程 sessionA | 线程B sessionB | 线程C sessionC |
|---|---|---|
| #开启事务 begin; | ||
| #给user表id=3的行加共享锁 select * from user where id = 3 lock in share mode; | ||
| #获取user表id=3行上的共享锁ok #select操作执行成功 select * from user where id=3; | #获取user表id=3行上的共享锁ok #select操作执行成功 select * from user where id=3; | |
| #获取user表id=3行上的排它锁失败 #delete操作被堵塞 delete from user where id = 3; | #获取user表id=4行上的排它锁成功 #delete操作执行成功 delete from user where id = 4; | |
| #提交事务 #user表id=3的行上共享锁被释放 commit; | ||
| #获取user表id=3行上的排它锁成功 #被堵塞的delete操作执行ok delete from user where id = 3; |
img.png
通过上述两个实例可以看出： - 当共享锁加在user表上，则其它事务可以再次获取user表的共享锁，其它事务再次获取user表的排他锁失败，操作被堵塞； - 当共享锁加在user表id=3的行上，则其它事务可以再次获取user表id=3行上的共享锁，其它事务再次获取user表id=3行上的排他锁失败，操作被堵塞，但是事务可以再次获取user表id!=3行上的排他锁；
2. 排它锁
排它锁，Exclusive Lock，也叫写锁或者独占锁，主要是防止其它事务和当前加锁事务锁定同一对象。同一对象主要有两层含义： - 当排他锁加在表上，则其它事务无法对该表进行insert,update,delete,alter,drop等更新操作； - 当排他锁加在表的行上，则其它事务无法对该行进行insert,update,delete,alter,drop等更新操作；
排它锁加锁方式为：
select ... for update;为了更好的说明排他锁，可以参照下面的执行顺序流和实例图：
给user表对象加排他锁
| 加锁线程 sessionA | 线程B sessionB |
|---|---|
| #开启事务 begin; | |
| #对user整张表加排他锁 select * from user for update; | |
| #获取user表上的共享锁ok，select执行成功 select * from user; | |
| #获取user表上的排他锁失败，操作被堵塞 delete from user where id=3; | |
| #提交事务 #user表上的排他被释放 commit; | |
| #获取user表上的排他锁成功，操作执行ok delete from user where id = 1; |
img.png
给user表id=3的行对象加排他锁
| 加锁线程 sessionA | 线程B sessionB | 线程C sessionC |
|---|---|---|
| #开启事务 begin; | ||
| #给user表id=3的行加排他锁 select * from user where id = 3 for update; | ||
| #获取user表id=3行上的共享锁ok select * from user where id=3; | #获取user表id=3行上的共享锁ok select * from user where id=3; | |
| #获取user表id=3行上的排它锁失败 delete from user where id = 3; | #获取user表id=4行上的排它锁成功 delete from user where id = 4; | |
| #提交事务 #user表id=3的行上排他锁被释放 commit; | ||
| #获取user表id=3行上的排它锁成功 #被堵塞的delete操作执行ok delete from user where id = 3; |
img.png
全局锁&表级锁&行锁
1. 全局锁
1.1 定义
全局锁，顾名思义，就是对整个数据库实例加锁。它是粒度最大的锁。
1.2 加锁
在MySQL中，通过执行 flush tables with read lock 指令加全局锁：
flush tables with read lock指令执行完，整个数据库就处于只读状态了，其他线程执行以下操作，都会被阻塞： - 数据更新语句被阻塞，包括 insert, update, delete语句； - 数据定义语句被阻塞，包括建表 create table,alter table、drop table 语句； - 更新操作事务commit语句被阻塞；
1.3 释放锁
MySQl释放锁有2种方式：
为了更好的说明全局锁，可以参照下面的执行顺序流和实例图：
| 加锁线程 sessionA | 线程B sessionB |
|---|---|
| flush tables with read lock; 加全局锁 | |
| select user表ok | select user表ok |
| insert user表堵塞 | insert user表堵塞 |
| delete user表堵塞 | delete user表堵塞 |
| drop user 表堵塞 | drop user 表堵塞 |
| alter user表 堵塞 | alter user表 堵塞 |
| unlock tables； 解锁 | |
| 被堵塞的修改操作执行ok | 被堵塞的修改操作执行ok |
img.png
通过上述的实例可以看出，当加全局锁时，库下面所有的表都处于只能状态，不管是当前事务还是其他事务，对于库下面所有的表只能读，不能执行insert，update，delete，alter，drop等更新操作。
1.4 使用场景
全局锁的典型使用场景是做全库逻辑备份，在备份过程中整个库完全处于只读状态。如下图：
img.png
- 假如在主库上备份，备份期间，业务服务器不能对数据库执行更新操作，因此涉及到更新操作的业务就瘫痪了；
- 假如在从库上备份，备份期间，从库不能执行主库同步过来的 binlog，会导致主从延迟越来越大，如果做了读写分离，那么从库上获取数据就会出现延时，影响业务；
- 此时，发起逻辑备份
- 假如数据备份时不加锁，此时，客户端A 发起一个还钱转账的操作：账户A 往账户B 转200
- 当账户A 转出200完成，账户B 转入200 还未完成时，整个数据备份完成
- 如果用该备份数据做恢复，会发现账户A 转出了200，账户B 却没有对应的转入记录，这样就会产生纠纷：A 说我账户少了 200， B 说我没有收到，最后，A，B谁都不干。
- MDL写锁，它和读锁、写锁都是互斥的，目的是用来保证变更表结构操作的安全性。也就是说，当对表结构进行变更时，会被默认加 MDL写锁，因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
- MDL读写锁是在事务commit之后才会被释放；
- 乐观锁，是相对悲观锁而言，一般不会利用数据库的锁机制，而是采用类似版本号比较之类的操作，因此乐观锁不会产生死锁的问题；
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其它事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启死锁检测。
