首先思考一个问题：锁是用来干嘛的？
锁一般是用来协调多个进程/线程同时访问一资源时，产生的数据一致性问题。
这里我们要先从MySQL的事务说起
事务
概念
什么是事务？
维基百科：数据库事务是数据库管理系统执行过程中的一个逻辑单位，由有限的数据库操作序列构成。
- 逻辑单位是不可拆分的
- 包含了一个或者一系列的 DML 语句
四大特性
事务的四大特性：ACID
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
在 InnoDB 里面是通过 undo log 来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以用 undo log 来实现回滚操作
隔离性（Isolation）
多个事务，对表或者行的并发操作，应该是透明、互相不干扰的。通过这种方式，我们最终也是保证业务数据的一致性。
持久性（Durable）
我们对数据库的任意增删改，只要事务提交成功，那么结果就是永久性的，即使数据库重启、宕机也不应该对其有影响。
在 InnoDB 中，持久性是通过 redo log 和 double write 双写缓冲来实现的
一致性（Consistent）
指的是数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态
这个一致性怎么理解呢？
来几个经典例子：
A、B两个账户各有100元，A转给B 150元
- 表字段有限制（A金额 > 0），转账失败-回滚
- 表字段没有限制，一个事务代码中做了判断（A金额 > 0），转账失败-回滚
- 都没有做限制，转账成功：A = -50，B = 250
上面三种例子，都实现了一致性，但第三种：A怎么能为负数呢？
因为事务的一致性是由用户自己定义的规则，属于应用层的约束，而AID是属于数据库层面的约束，AID都是为了实现数据的一致性。
事务并发带来的问题
脏读
事务A可以读取到事务B未提交的数据
不可重复读
事务A可以读取到事务B已提交的数据更新，导致事务内前后两次读取数据不一致的情况
幻读
一个事务内多次读取到的结果集个数不一致，即读取到了其他事务新增、删除后的数据
事务的隔离级别
读未提交（READ UNCOMMITTED）
事务A可以读取到事务B未提交的数据，什么问题都没解决
读已提交（READ COMMITTED）
事务A可以读取到事务B已提交的数据，解决了脏读
可重复读（REPEATABLE READ）
事务A可以读取到事务B已提交新增、删除的数据，解决了不可重复读，没有解决幻读
而这是MySQL默认的隔离级别，通过实现快照读+当前读来解决幻读问题
序列化（SERIALIZABLE）
事务不同隔离级别解决的问题如下：
事务隔离级别对应解决的问题如下：
InnoDB在RR级别下是怎么解决幻读的？
幻读解决方案
InnoDB默认的事务隔离级别是Repeatable Read（后文中用简称RR），它为了解决该隔离级别下可能产生的幻读问题，提出了LBCC和MVCC两种方案。其中：
LBCC解决的是当前读（实时读）情况下的幻读
undolog+MVCC解决的是普通读（快照读、一致性读）的幻读
快照读
快照读（Consistent Read）也叫普通读、一致性读，就是单纯的 SELECT 语句，不包括下面这两类语句：
SELECT … FOR UPDATE
SELECT … LOCK IN SHARE MODE
快照读的执行方式是生成 ReadView（快照），每次读取的都是建立快照那一刻的数据，并不会对记录进行加锁。
什么时候建立快照？
- 在 Serializable 隔离级别下 - 普通 select 也变成当前读，即加共享读锁
- 在 RC 隔离级别下 - 每次 select 都会建立新的快照（就是每次select都是最新的数据）
- 在 RR 隔离级别下
- 事务启动后，首次 select 会建立快照
- 如果事务启动选择了 with consistent snapshot，事务启动时就建立快照
- 当前事务对旧数据进行修改，会重新建立快照
注意：快照一旦建立，针对的是整个DB实例，跨表依旧有效
InnoDB是怎样实现快照读的？
靠MVCC实现的，MVCC 只在 Read Commited 和 Repeatable Read 两种隔离级别下工作
MVCC
https://www.php.cn/mysql-tutorials-460111.html
MVCC在MySQL的实现原理主要是版本链，undo日志 ，Read View来实现的
- 版本链
- db_trx_id6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID。
- db_roll_pointer（版本链关键）7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
- db_row_id6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以db_row_id产生一个聚簇索引。
- 实际还有一个删除flag隐藏字段, 记录被更新或删除并不代表真的删除，而是删除flag变了
- undo日志
如上图
- ReadView
事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照。
记录并维护系统当前活跃事务的ID(没有commit，当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以越新的事务，ID值越大)，是系统中当前不应该被本事务看到的其他事务id列表。
Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
当前读
从字面上理解，当前读就是读取的是记录的最新版本，并且在读取之后，还需保证其他事务不能修改当前记录，对读取的记录加锁，如以下这些 SQL 类型：
select … lock in share mode
select … for update
update 、delete 、insert
当前读本质上是加了锁的增删改查语句，无论加的共享锁还是排他锁均为当前读.
为什么update、delete、insert也用到了当前读？
在执行update语句更新某行数据时，需要先读取最新数据行，然后再更新，读取的时候用到的就是当前读
LBCC
LBCC是Lock-Based Concurrent Control的简称，即基于锁的并发控制。
InnoDB是通过锁来实现LBCC方案的
但如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，而我们的大多数应用都是读多写少的，这样会极大地影响操作数据的效率
InnoDB在RR级别下真的解决了幻读吗？
https://www.jianshu.com/p/b7c53ee0ed0e
| 步骤 | S1 | S2 |
|---|---|---|
| 1 | begin; | begin; |
| 2 | select * from student where id =8;(没值) | |
| 3 | INSERT INTO student (id, name, age, addr, uniq_id) VALUES (8, ‘张三’, 18, ‘合肥’, 8); | |
| 4 | update student set addr=‘合肥’ where id=8; | |
| 5 | select * from student where id =8; (有值) | |
| 6 | commit; |
上面的场景下没有解决幻读
S1下的步骤2和步骤5读取的数据集不一致，产生了幻读
锁的类型
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
兼容性
共享锁
共享锁，Share lock，也叫读锁。它是指当事务获取对象的共享锁时，允许其它事务读取对象、获取对象的共享锁，但是不可以写入
排它锁
排它锁，Exclusive Lock，也叫写锁或者独占锁，主要是防止其它事务和当前加锁事务锁定同一对象。
| 锁类型 | 排他锁 | 共享锁 |
|---|---|---|
| 排他锁 | 互斥 | 互斥 |
| 共享锁 | 互斥 | 兼容 |
机制
在MySQL中，无论是悲观锁还是乐观锁，都是人们对概念的一种抽象，它们本身还是利用 MySQL提供的锁机制来实现的。除了MySQL，在Java中也有乐观锁和悲观锁的概念。
乐观锁
乐观锁是对于数据冲突保持一种乐观态度，操作数据时不会对操作的数据进行加锁（这使得多个任务可以并行的对数据进行操作），只有到数据提交的时候才通过一种机制来验证数据是否存在冲突(一般实现方式是通过加版本号然后进行版本号的对比方式实现)
比如MVCC、CAS
悲观锁
总是假设以最悲观的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁
比如synchronized
粒度
https://zhuanlan.zhihu.com/p/387087073
全局锁
- 概念
对整个DB实例加锁
- 语句
- 加锁：flush tables with read lock（FTWRL）读锁执行后，整个库处于只读状态的时候，其他线程涉及到排他锁的操作都会被阻塞。
若FTWRL之前有读写，FTWRL会等待读写执行完毕后再执行。FTWRL执行时，要刷脏页数据到磁盘， 因为要保持数据一致性，所以执行FTWRL的时机是所有事务都提交完毕后。
- 解锁：unlock tables或客户端断开连接
- 适用场景
全库备份场景
FTWRL会带来什么问题？
- 若你在主库备份，则备份期间，主库都不能再执行更新，业务直接停摆
- 若你在从库备份，则备份期间，从库不能执行主库同步过来的binlog，主从延迟加剧
mysqldump
官方推荐的逻辑备份工具mysqldump。当mysqldump使用参数–single-transaction，导数据前会启动一个事务，确保得到一致性视图。 由于MVCC，该过程中数据依旧能正常更新。
存储引擎需要支持事务，这也是提倡存储引擎尽量使用InnoDB的原因。
mysqldump的时候不要执行DDL语句：
master对表test的DDL传输到slave去应用时，mysqldump还没对test进行备份，该DDL会在slave的test表应用成功，但当导出到test表时，会报“ERROR 1412 (HY000): Table definition has changed, please retry transaction” 错误，导致导出失败！
为什么不用reaonly
set global read_only=1;
- 有些系统的readonly值会被用来做其他逻辑，比如判断一个库是主库or备库。因此，修改global变量的方式影响面太大！
- 执行FTWRL后，由于客户端异常断开，MySQL会自动释放该全局锁，整库回到可正常更新的状态。而将整库设为readonly后，若客户端异常，则数据库就一直保持readonly，导致整库长时间不可写。
表级锁
https://cloud.tencent.com/developer/article/1553461
https://zhuanlan.zhihu.com/p/573336304
表锁
https://blog.youkuaiyun.com/u022812849/article/details/122847603
表锁一般是在数据库引擎不支持行锁的时候才会被用到的
- 语法
-- 加锁
lock tables tablename read/write
-- 解锁
unlock tables
-- 客户端自动断开连接的时候，也会解锁
- 特点
- 写锁：本线程可读、写，其他线程不可读写
- 读锁：所有线程可读
注：一个会话中只要使用lock table加表锁，read还是write其他表或用表别名访问同一个表会报错
MDL
MDL全称为meta data lock，即元数据锁，主要作用是维护表元数据的一致性
元数据锁是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请MDL锁
- DML操作需要MDL读锁
- DDL操作需要MDL写锁
MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥
| S1 | S2 | S3 |
|---|---|---|
| begin | ||
| select * from student | ||
| ALTER TABLE student ADD INDEX idx_name(name);(阻塞) | ||
| select * from student(阻塞) | ||
| commit; | ok | ok |
若只有S2被阻塞还没啥，但之后所有要在表student上新申请MDL读锁的请求也会被S2阻塞。 所有对表的CRUD操作都要先申请MDL读锁，就都被锁住，等于该表此时完全不可读写！
遇到这种问题怎么临时解决呢？
- show full processlist 可以看到正在等待MDL锁
- **SELECT * FROM information_schema.INNODB_TRX ; 根据这个事务的线程ID（trx_mysql_thread_id），这是个长事务，直接kill trx_mysql_thread_id **
MySQL5.6后支持Online DDL，对表操作增加字段等功能时，不会阻塞读写，那为啥还会出现上述案例的结果？
Online DDL过程
- 拿MDL写锁
- 降级成MDL读锁
- 真正做DDL
- 升级成MDL写锁
- 释放MDL锁
1、2、4、5若无锁冲突，执行时间很短。第3步占用了DDL绝大部分时间，这期间该表可正常读写数据，因此称为Online。
但案例中，第1步就阻塞了。所以必须等待之前的事务全部提交后，才能开始执行。
怎样避免阻塞？
- 有长事务
- 原地等待，先暂停DDL
- 或kill掉这长事务
- 热点表，查询请求特别多
alter table语句设定等待时间，若在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。
表级意向锁
一句话总结：由于InnoDB支持多粒度锁，允许行锁和表锁共存，为了快速的判断表中是否存在行锁，InnoDB推出了意向锁。
- 排他意向锁：若有事务在表里执行增删改操作，那在行级会加独占锁，此时同时会在表级加一个意向独占锁
- 共享意向锁：若有事务在表执行查询操作，会在表级加一个意向共享锁
注：意向锁与意向锁、行级锁之间不会产生冲突，意向锁只会与表级锁产生冲突
| 锁类型 | 独占锁 | 共享锁 | 意向独占锁 | 意向共享锁 |
|---|---|---|---|---|
| 独占锁 | 互斥 | 互斥 | 互斥 | 互斥 |
| 共享锁 | 互斥 | 不互斥 | 互斥 | 不互斥 |
| 意向独占锁 | 互斥 | 互斥 | 不互斥 | 不互斥 |
| 意向共享锁 | 互斥 | 不互斥 | 不互斥 | 不互斥 |
表级的意向锁有什么意义呢？
当我们准备给表加上表锁的时候，先要判断有没其他的事务锁定了其中了某些行，如果有的话，肯定不能加上表锁。那么在有意向锁的时候，就不需要扫描表中的所有行来找行锁，如果有，就直接返回失败。如果没有，就可以加锁成功。
AUTO-INC锁
AUTO-INC锁是一种特殊的表级锁，当表中有AUTO_INCREMENT的列时，如果向这张表插入数据时，InnoDB会先获取这张表的AUTO-INC锁，等插入语句执行完成后，AUTO-INC锁会被释放。
AUTO-INC锁可以使用innodb_autoinc_lock_mode变量来配置自增锁的算法。innodb_autoinc_lock_mode变量可以选择三种值如下表：
| innodb_autoinc_lock_mode | 变量含义 |
|---|---|
| 0 | 传统锁模式（insert语句排队获取锁） |
| 1 | 连续锁模式（insert确定行数的并行，不确定行数的排队获取锁） |
| 2 | 交错锁模式(MySQL8默认，都不排队) |
三种模式的具体含义参考https://www.jianshu.com/p/8f6598da99e4
innodb_autoinc_lock_mode=2的性能最好，阿里的数据库默认是第三种模式，但这样带来两个问题
- 并发插入数据时，自增长的值可能不是连续的
- 基于Statement的主从复制会出现问题。因此使用这个模式，主从复制应该使用Row方式。这样才能保证最大的并发性能及主从数据的一致
行锁
加锁方式
记录锁
记录锁就是对表中的记录加锁，简称行锁。比如
SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;
它会在 id=1 的记录上加上记录锁，以阻止其他事务增删改 id=1 这一行
记录锁的条件：
- id 列必须为唯一索引列或主键列
- 条件必须为精准匹配（=），不能为 >、<、like等。
间隙锁
- 间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的加锁机制
RC在特殊情况下也会使用间隙锁封锁区间：
- 外键约束检查(foreign-key constraint checking)
- 重复键检查(duplicate-key checking)
- 使用间隙锁锁住的是一个区间（左开右开），而不仅仅是这个区间中的每一条数据。
- 间隙锁存在于非唯一索引中。
间隙锁的加锁规则：
- 原则
- 加锁的基本单位是 next-key lock，next-key lock 是左开右闭区间
- 查找过程中访问到的对象才会加锁
- 优化
- 等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁；如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁。
- 范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止
关闭间隙锁的2种方式：
(1)将事务隔离级别变为read committed
(2)将参数innodb_locks_unsafe_for_binlog设置为1（默认是0，即开启间隙锁）
有一个特殊的间隙锁：
**插入意向锁 **(Insert Intention Lock）
https://zhuanlan.zhihu.com/p/344542398
插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突，但是第三个事务获取间隙的排他锁进行update或delete则会阻塞。
临键锁
**Next-key锁是记录锁和间隙锁的组合（**左开右闭的区间）
InnoDB默认使用的是临键锁，部分场景下会退化成记录锁、间隙锁。
- 查询条件没有用到索引，直接锁表，比如查询条件不是索引列，比如隐式转换
- 查询条件的列是唯一索引且命中，临键锁退化为记录锁
- 查询条件的列是唯一索引但未命中，临键锁退化为间隙锁（间隙锁与间隙锁之间兼容，间隙锁只是为了防止在锁定期间有数据插入这个间隙）
间隙锁到底锁住了什么？
https://www.jianshu.com/p/42e60848b3a6
系统参数
事务隔离级别
-- 8.0以上事务隔离级别
select @@global.transaction_isolation;
show variables like '%transaction_isolation%';
-- 8.0以下事务隔离级别
select @@global.tx_isolation;-- MySQL8.0以下
show variables like '%tx_isolation%';
-- 修改事务隔离级别为RC
set global transaction_isolation='READ-COMMITTED';
set transaction_isolation='READ-COMMITTED';
事务自动提交
-- ON为自动提交 OFF反之
show variables like 'autocommit';
-- 修改自动提交为OFF
set autocommit=0
死锁
-- 死锁等待时间，默认50秒
show VARIABLES like '%innodb_lock_wait_timeout%'
-- 死锁自动检测，默认开启
show VARIABLES like '%innodb_deadlock_detect%'
用户正在运行的状态
-- 显示用户正在运行的线程
show processlist
-- 查看正在运行的事务
select * from information_schema.innodb_trx;
-- 8.0之前查看当前的锁信息
select * from information_schema.innodb_locks;
-- 8.0之后查看当前的锁信息
select * from performance_schema.data_locks;
-- 8.0之前查看锁等待的信息
select * from information_schema.innodb_lock_waits;
-- 8.0之后查看锁等待的信息
select * from performance_schema.data_lock_waits;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
