概叙
MySQL锁的由来
客户端发往MySQL的一条条SQL语句，实际上都可以理解成一个个单独的事务（一条sql语句默认就是一个事务）。而事务是基于数据库连接的，每个数据库连接在MySQL中，又会用一条工作线程来维护，也意味着一个事务的执行，本质上就是一条工作线程在执行，当出现多个事务同时执行时，这种情况则被称之为并发事务，所谓的并发事务也就是指多条线程并发执行。
多线程并发执行自然就会出问题，也就是科普文：数据库事务、隔离级别和并发问题（MySQL）_数据库的并发控制、事务管理和隔离级别-CSDN博客中提到的脏写、脏读、不可重复读及幻读问题。
而对于这些问题又可以通过调整事务的隔离级别来避免，那为什么调整事务的隔离级别后能避免这些问题产生呢？
这是因为不同的隔离级别中，工作线程执行SQL语句时，用的锁粒度、类型不同。
锁定义
由以上可知，数据库的锁机制本身是为了解决并发事务带来的问题而诞生的，主要是确保数据库中，多条工作线程并行执行时的数据安全性。
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
锁分类
MySQL的锁机制与索引机制类似，都是由存储引擎负责实现的，这也就意味着不同的存储引擎，支持的锁也并不同，这里是指不同的引擎实现的锁粒度不同。
但总归说来说去其实就共享锁（读锁）、排他锁（写锁）两种，只是加的方式不同、加的地方不同，因此就演化出了这么多锁的称呼。
而表锁：MDL元数据锁Meta Data Lock，是“共享排他锁（读写锁）”，因为在5.6引入OnlineDDL后，MDL可以从排他锁降（写锁）级到共享锁（读锁）。
首先要知道 MySQL 有哪些锁，如上图所示，至少有 12 类锁（其中自增锁是事务向包含了 AUTO_INCREMENT 列的表中新增数据时会持有，predicate locks for spatial index 为空间索引专用，本文不讨论这 2 类锁）。
除开从锁粒度来划分锁之外，其实锁也可以从其他的维度来划分，因此也会造出很多关于锁的名词，下面先简单梳理一下MySQL的锁体系：
- 以锁粒度的维度划分
- 全局锁：锁定数据库中的所有表。加上全局锁之后，整个数据库只能允许读，不允许做任何写操作。对整个数据库实例加锁。 加锁表现：数据库处于只读状态，阻塞对数据的所有 DML/DDL； 加锁方式：Flush tables with read lock 释放锁：unlock tables(发生异常时会自动释放)； 作用场景：全局锁主要用于做数据库实例的逻辑备份，与设置数据库只读命令set global readonly=true相比，全局锁在发生异常时会自动释放。
- 表级锁：每次操作锁住整张表。主要分为三类
- 表锁（分为表共享读锁 read lock、表独占写锁 write lock）
- 元数据锁（meta data lock，MDL）：基于表的元数据加锁，加锁后整张表不允许其他事务操作。这里的元数据可以简单理解为一张表的表结构
- 意向锁（Intention Locks分为意向共享锁 IS、意向排他锁 IX）：这个是
InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
- 行级锁：每次操作锁住对应的行数据。InnoDB 引擘支持行级别锁，行锁粒度小，并发度高，但加锁开销大，也可能会出现死锁。加锁机制：innodb 行锁锁住的是索引页，回表时，主键的聚簇索引也会加上锁。主要分为三类
- 记录锁 / Record 锁：也就是行锁，一条记录和一行数据是同一个意思。防止其他事务对此行进行update和delete，在 RC、RR隔离级别下都支持
- 间隙锁 / Gap 锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
- 临键锁 / Next-Key 锁：间隙锁的升级版，同时具备记录锁+间隙锁的功能，在RR隔离级别下支持
- 以互斥性的角度划分
- 共享锁 / S锁：不同事务之间不会相互排斥、可以同时获取的锁
- 排他锁 / X锁：不同事务之间会相互排斥、同时只能允许一个事务获取的锁
- 共享排他锁 / SX锁：
MySQL5.7版本中新引入的锁，主要是解决SMO带来的问题
- 以操作类型的维度划分
- 读锁：查询数据时使用的锁
- 写锁：执行插入、删除、修改、
DDL语句时使用的锁
- 以加锁方式的维度划分
- 显示锁：编写
SQL语句时，手动指定加锁的粒度 - 隐式锁：执行
SQL语句时，根据隔离级别自动为SQL操作加锁
- 显示锁：编写
- 以思想的维度划分
- 乐观锁：每次执行前认为自己会成功，因此先尝试执行，失败时再获取锁
- 悲观锁：每次执行前都认为自己无法成功，因此会先获取锁，然后再执行
锁信息查看方式
- MySQL 5.6.16 版本之前，需要建立一张特殊表 innodb_lock_monitor，然后使用show engine innodb status查看
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_lock_monitor;
- MySQL 5.6.16 版本之后，修改系统参数 innodb_status_output 后，使用show engine innodb status查看
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
每 15 秒输出一次 INNODB 运行状态信息到错误日志。
- MySQL5.7 版本之后
可以通过 information_schema.innodb_locks 查看事务的锁情况，但只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况
- MySQL8.0
删除 information_schema.innodb_locks，添加 performance_schema.data_locks，即使事务并未被阻塞，依然可以看到事务所持有的锁，同时通过 performance_schema.table_handles、performance_schema.metadata_locks 可以非常方便的看到元数据锁等表锁。
Innodb中的锁
锁按粒度可分为分为全局，表级，行级3 类。
1 全局锁
对整个数据库实例加锁。 加锁表现：数据库处于只读状态，阻塞对数据的所有 DML/DDL； 加锁方式：Flush tables with read lock 释放锁：unlock tables(发生异常时会自动释放)； 作用场景：全局锁主要用于做数据库实例的逻辑备份，与设置数据库只读命令set global readonly=true相比，全局锁在发生异常时会自动释放。
2 表锁
对操作的整张表加锁， 锁定颗粒度大，资源消耗少，不会出现死锁，但会导致写入并发度低。具体又分为 3 类： 1）显式表锁：分为共享锁（S）和排他锁（X） 显示加锁方式：lock tables ... read/write 释放锁：unlock tables(连接中断也会自动释放)
2）Metadata-Lock（元数据锁）：MySQL5.5 版本开始引入，主要功能是并发条件下，防止 session1 的查询事务未结束的情况下，session2 对表结构进行修改，保护元数据的一致性。在 session1 持有 metadata-lock 的情况下，session2 处于等待状态：show processlist 可见Waiting for table metadata lock。
科普文：软件架构数据库系列之【MySQL 执行DQL/DML/DDL必加的自动表级锁：MDL元数据锁Meta Data Lock】-CSDN博客
其具体加锁机制如下：
- DML->先加 MDL 读锁（SHARED_READ，SHARED_WRITE）
- DDL->先加 MDL 写锁（EXCLUSIVE）
- 读锁之间兼容
- 读写锁之间、写锁之间互斥
3）Intention Locks（意向锁）：意向锁为表锁（表示为 IS 或者 IX），由存储引擎自己维护，用户无法干预。下面举一个例子说明其功能：
假设有 2 个事务：T1 和 T2 T1: 锁住表中的一行，只能读不能写（行级读锁）。 T2: 申请整个表的写锁（表级写锁）。 如 T2 申请成功，则能任意修改表中的一行，但这与 T1 持有的行锁是冲突的。故数据库应识别这种冲突，让 T2 的锁申请被阻塞，直到 T1 释放行锁。
有 2 种方法可以实现冲突检测： \1. 判断表是否已被其它事务用表锁锁住。 \2. 判断表中的每一行是否已被行锁锁住。
其中 2 需要遍历整个表，效率太低。因此 innodb 使用意向锁来解决这个问题： T1 需要先申请表的意向共享锁（IS），成功后再申请某一行的记录锁 S。 在意向锁存在的情况下，上面的判断可以改为： T2 发现表上有意向共享锁 IS，因此申请表的写锁被阻塞。
3 行锁
InnoDB 引擘支持行级别锁，行锁粒度小，并发度高，但加锁开销大，也可能会出现死锁。
加锁机制：innodb 行锁锁住的是索引页，回表时，主键的聚簇索引也会加上锁。
行锁具体类别如上图所示，包括：Record lock/Gap Locks/Next-Key Locks，每类又可分为共享锁（S）或者排它锁（X），一共 23=6 类，最后还有 1 类插入意向锁：
Record lock（记录锁）：最简单的行锁，仅仅锁住一行。记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB 也会隐式的创建一个索引，并使用这个索引实施记录锁。
Gap Locks（间隙锁）：加在两个索引值之间的锁，或者加在第一个索引值之前，或最后一个索引值之后的间隙。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。间隙锁只阻止其他事务插入到间隙中，不阻止其它事务在同一个间隙上获得间隙锁，所以 gap x lock 和 gap s lock 有相同的作用。它是一个左开右开区间：如（1，3）
Next-Key Locks：记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。它是一个左开右闭区间：如（1，3】
Insert Intention（插入意向锁）：该锁只会出现在 insert 操作执行前（并不是所有 insert 操作都会出现），目的是为了提高并发插入能力。它在插入一行记录操作之前设置一种特殊的间隙锁，多个事务在相同的索引间隙插入时，如果不是插入间隙中相同的位置就不需要互相等待。
TIPS:
1.不存在 unlock tables … read/write，只有 unlock tables 2.If a session begins a transaction, an implicit UNLOCK TABLES is performed
4 锁的兼容情况
引入意向锁后，表锁之间的兼容性情况如下表：
总结：
- 意向锁之间都兼容
- X,IX 和其它都不兼容（除了 1）
- S,IS 和其它都兼容（除了 1,2）
5 测试和验证Innodb锁
5.1 建立测试表
该表包含一个主键，一个唯一键和一个非唯一键：
CREATE TABLE t (
id int(11) NOT NULL,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
c varchar(10),
PRIMARY KEY (id),
unique KEY a (a),
key b(b))
ENGINE=InnoDB;5.2 写入测试数据
insert into t values(1,10,100,'a'),(3,30,300,'c'),(5,50,500,'e');
5.3 记录存在时的加锁
对于 innodb 引擘来说，加锁的 2 个决定因素：
1）当前的事务隔离级别 2）当前记录是否存在
假设 id 为 3 的记录存在，则在不同的 4 个隔离级别下 3 个语句的加锁情况汇总如下表(select 3 表示 select * from t where id=3)：
| 隔离级别 | select 3 | begin;select 3 | begin;select 3 for update |
|---|---|---|---|
| RU | 无 | SHARED_READ | SHARED_WRITE IX X,REC_NOT_GAP：3 |
| RC | 无 | SHARED_READ | SHARED_WRITE IX X,REC_NOT_GAP：3 |
| RR | 无 | SHARED_READ | SHARED_WRITE IX X,REC_NOT_GAP：3 |
| Serial | 无 | SHARED_READ IS S,REC_NOT_GAP：1 | SHARED_WRITE IX X,REC_NOT_GAP：3 |
分析：
- 使用以下语句在 4 种隔离级别之间切换： set global transaction_isolation='READ-UNCOMMITTED'; set global transaction_isolation='READ-COMMITTED'; set global transaction_isolation='REPEATABLE-READ'; set global transaction_isolation='Serializable';
- 对于 auto commit=true，select 没有显式开启事务（begin）的语句，元数据锁和行锁都不加，是真的“读不加锁”
- 对于 begin; select ... where id=3 这种只读事务，会加元数据锁 SHARED_READ，防止事务执行期间表结构变化，查询performance_schema.metadata_locks表可见此锁：
- 对于 begin; select ... where id=3 这种只读事务，MySQL 在 RC 和 RR 隔离级别下，使用 MVCC 快照读，不加行锁，但在 Serial 隔离级别下，读写互斥，会加意向共享锁（表锁）和共享记录锁（行锁）
- 对于 begin; select ... where id=3 for update，会加元数据锁 SHARED_WRITE
- 对于 begin; select ... where id=3 or update，4 种隔离级别都会加意向排它锁（表锁）和排它记录锁（行锁）,查询performance_schema.data_locks可见此 2 类锁
5.4 记录不存在时的加锁
| 隔离级别 | select 2 | begin;select 2 | begin;select 2 for update |
|---|---|---|---|
| RU | 无 | SHARED_READ | SHARED_WRITE IX |
| RC | 无 | SHARED_READ | SHARED_WRITE IX |
| RR | 无 | SHARED_READ | SHARED_WRITE IX X,GAP：3 |
| Serial | 无 | SHARED_READ IS S,GAP：3 | SHARED_WRITE IX X,GAP：3 |
分析：
- 当记录不存在的时候，RU 和 RC 隔离级别只有意向锁，没有行锁了
- RR，Serial 隔离级别下，记录锁变成了Gap Locks（间隙锁），可以防止幻读，lock_data 为 3 的 GAP lock 锁住区间（1，3），此时 ID=2 的记录插入会被阻塞。
那么对于主键范围查询，唯一键查询，非唯一键查询，在不同隔离级别下又是如何加锁的呢？
敬请期待、后面会给出一条sql命令执行过程，包括加锁解锁过程。
6.认识MVCC
科普文：数据库事务、隔离级别和并发问题（MySQL）_数据库的并发控制、事务管理和隔离级别-CSDN博客
科普文：软件架构数据库系列之【Innodb的锁和MVCC】-CSDN博客
科普文：软件架构数据库系列之【MySQL引擎Innodb的MVCC特性】-CSDN博客
科普文：软件架构数据库系列之【MySQL 执行DQL/DML/DDL必加的自动表级锁：MDL元数据锁Meta Data Lock】-CSDN博客
MVCC（Multi-Version Concurrency Control）翻译过来是多版本并发控制，存在于RC、RR隔离级别下，用于快照读。是这样来实现的，存储引擎全局维护了一个系统版本号，每开启一个新的事务，这个系统版本号就会递增。事务开始时刻的系统版本号，会作为这个事务本身的版本号。在每行记录中，存储引擎又在每行的后面保存两个隐藏的列，分别保存这一行的开始版本号（trx_id）和过期版本号(roll_pointer)。版本号就是事务ID。
看下各种更新语句版本号的情况，
insert，存储引擎为新插入的每一行保存当前的系统版本号作为这一行的开始版本号。
update，存储引擎会新插入一行记录，当前的系统版本号是新记录行的开始版本号；同时会将当前行的过期版本号设为原来行的系统版本号；
delete，存储引擎将删除的记录行的过期版本号设置为当前的系统版本号。
还要了解另外一个概念，readview。
readview用来判断版本链中哪个版本对当前事务是可见的,包含4个重要属性：
m_ids：生成ReadView时，当前系统活跃的事务id列表;
min_trx_id：列表中的最小事务id;
max_trx_id：列表中最大事务id;
creator_trx_id：生成该ReadView自身事务的id。
注意：如果一个事务只读，则creator_trx_id默认为0，只有当事务发生INSERT和UPDATE、DELETE操作时才会分配该事务id。creator_trx_id是为了判断这条undo log是否是自己生成的。
如何判断数据的可见性，
- 从记录的最新版本开始迭代依次取隐藏列trx_id和ReadView的m_ids、min_trx_id、max_trx_id比较。
- 如果trx_id等于creator_trx_id表示该版本是自己更新的，版本可见；
- 如果trx_id在m_ids列表中，则该版本不符合可见性要求；
- 如果版本trx_id小于min_trx_id，表示事务在创建ReadView时已经提交，版本可见；
- 如果版本trx_id大于max_trx_id表示，该事务是在ReadView生成之后创建和提交的，不符合可见性要求；
简单点来说，MVCC就是基于记录的事务id做控制，一条记录会有多个事务id，代表多个事务id的记录会存储在undo log中。
7.Innodb解决幻读和不可重复读
InnoDB解决幻读的方法是通过行级锁，可以有以下几种模式：
-
共享锁（S Lock）：允许事务读行。
-
排他锁（X Lock）：允许事务删除或更新行。
为了防止幻读，InnoDB引入了间隙锁（Gap Lock），它锁定的是一个范围，但不包括记录本身，防止其他事务在这个范围内插入新的行。
另外，InnoDB通过MVCC（多版本并发控制）解决了不可重复读的问题，它保留了旧版本的数据，在事务开始时保证了一致性视图，即事务过程中，即使其他事务修改了数据，也能读取到事务开始时的数据。
解决方法：
-
幻读：通过间隙锁解决。
-
不可重复读：通过MVCC解决，默认事务隔离级别是可重复读（REPEATABLE READ），它使用了锁和MVCC来保证事务的一致性和隔离性。
示例代码（SQL语句）：
-- 设置事务隔离级别为可重复读
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 开始事务
START TRANSACTION;
-- 读取数据（不会锁定，但是会生成一个读锁，阻止写操作）
SELECT * FROM your_table WHERE your_condition;
-- 进行其他操作...
-- 提交事务
COMMIT;在可重复读隔离级别下，即使其他事务更新了表中的行，之前读取的行版本也不会改变，保证了事务的一致性。间隙锁会在事务级别锁定可能影响的间隙，防止其他事务在这个范围内插入新行，从而减少幻读的可能性。
7.1、select
在RC隔离级别下，解决了脏读的情况，是怎么解决的呐，当然是通过MVCC，因为MVCC是基于事务ID，也就是trx_id，所以在select的时候读取的一定是commit之后的数据，不提交没有trx_id哦，注意在RC隔离级别下是每次select都会产生一个独立的readview，所以幻读和不可重复读是无法解决的。
在RR隔离级别下，肯定脏读是不存在的。同时解决了幻读和不可重复读，也是通过MVCC，只不过这里生成readview的时机和RC隔离级别下不一样，在RR隔离级别下readview是在事务的第一个select的时候生成的，以后的select会使用第一个select的readview，这样就可以解决了幻读和不可重复读。
7.2、update/insert/delete
update/insert/delete语句都是当前读，为什么在更新语句中会有读，这是因为在执行更新操作的时候肯定要先读出来，再进行更新，这里的读便是当前读，只不过这里的当前读在RR隔离级别下是通过加next-key解决的，所以在RR隔离级别下可以解决幻读和不可重复读。
扫一扫
886
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
