金山区做网站公司,html网页设计规则代码,sem模型,广告制作与设计专业文章目录 前言1. 事务处理挑战1.1 事务机制处理的问题1.2 并发事务带来的问题 2. InnodDB 和 ACID 模型2.1 Innodb Buffer Pool2.2 Redo log2.3 Undo log2.4 应用案例 3. 隔离级别和锁机制3.1 事务隔离级别3.1.1 READ UNCOMMITTED3.1.2 READ COMMITTED3.1.3 REPEATABLE READ3.1… 文章目录 前言1. 事务处理挑战1.1 事务机制处理的问题1.2 并发事务带来的问题 2. InnodDB 和 ACID 模型2.1 Innodb Buffer Pool2.2 Redo log2.3 Undo log2.4 应用案例 3. 隔离级别和锁机制3.1 事务隔离级别3.1.1 READ UNCOMMITTED3.1.2 READ COMMITTED3.1.3 REPEATABLE READ3.1.4 SERIALIZABLE 3.2 多版本并发控制3.2.1 两种读取方式3.2.2 Read View 3.3 Innodb 锁机制3.3.1 两阶段锁协议3.3.2 表锁3.3.3 共享锁与排它锁3.3.4 意向锁3.3.5 记录锁3.3.6 Gap 锁3.3.7 Next-Key 锁3.3.8 半一致性读取3.3.9 可重复读的锁定案例案例一唯一索引等值查询不存在的记录案例二唯一索引范围扫描案例三非唯一索引等值覆盖查询案例四非唯一索引范围扫描案例五非唯一索引等值查询案例六limit 语句加锁案例七Next-key lock 实现方式 3.3.10 死锁案例案例一多线程批量更新场景 3.4 metadata lock 后记 前言
事务的起源可以追溯到 6000 年以前当时苏美尔人Sumerians就发明了事务处理和记录方法。已知最早的记录是写在土块上的上面记录的是皇家的税收、土地、谷物、牲畜、奴隶和黄金明确地记下了每笔交易。这种早期的系统已经具备了事务处理的关键特征。记录使用的土块和记号方式就是数据库每次土块的加入都会造成 数据状态的变化在今天可以称为事务。 PS事务是数据库关系系统中的一系列操作的一个逻辑单位。 这种基于土块的事务处理系统的技术演变了数千年经历了纸莎草纸、羊皮纸、最后到我们现在用的纸。在 19 世纪后期出现了打孔卡片计算机系统应用到 1890 年美国人口普查的数据存储和记录。20 世纪前半叶对事务处理的需求促进了打孔卡片计算机的发展提升了查询和更新速度1 秒可以处理一张卡片这种系统被应用于库存控制和记账。
直到 20 世纪后半叶事务处理出现了重大的进展基于磁性存储介质的成批事务处理以及随后基于电子存储和计算机网络的联机事务处理。事务处理的速度成几何倍增长这两种技术极大的促进了计算机工业的发展。
今天事务处理被应用于各个核心领域典型使用场景包括银行交易、电子订单、火车预定、股票交易、自动收款、作业和存货规划、记账等。随着事务技术的发展和被广泛应用对事务处理的 性能 和 安全 的要求也越来越高。数据库系统中事务处理机制要在满足性能的前提下保证用户的数据操作动作对数据是 安全 的。
本篇文章将介绍 MySQL InnoDB 引擎中的事务处理机制探究它是如何保障用户数据操作的安全。
1. 事务处理挑战 1.1 事务机制处理的问题
上面提到事务处理机制是为了保证用户对数据库的操作对数据是安全的。如何保障事务安全呢在 1970 年 IBM 研究员发表了《关系模型的数据库管理系统》论文首次提出 ACID 事务模型。数据只有在带有 ACID 四个特性的事务处理机制的保障下才可以认为是安全的。
原子性Atomicity事务被视为一个原子操作要么全部执行成功要么全部执行失败如果事务中的任何一部分操作失败那么整个事务将回滚到初始状态不会留下部分执行的结果。一致性Consistency事务要保证数据库整体数据的完整性和业务数据的一致性事务成功提交整体数据修改事务错误则回滚到数据回到原来的状态。隔离性Isolation隔离性是说两个事务的执行都是独立隔离开来的事务之前不会相互影响多个事务操作一个对象时会以串行等待的方式保证事务相互之间是隔离的。持久性Durability一旦事务提交成功其结果将永久保存在数据库中并且对于系统故障或崩溃是持久的。即使在系统故障后进行恢复数据库也能保持事务的持久性。
事务模型是商业世界运作的基石主要体现在交易处理电子化。作为一个软件系统数据库系统会遭遇一些故障如我们常说的硬件故障、系统故障、介质故障等。对于数据库系统因为数据实在太为重要数据库系统应该能够保证在出现故障时数据的流动依然满足 ACID 特性。
1.2 并发事务带来的问题
在 1990 年以前数据库是非常简单的隔离级别以及并发控制是没有标准的用户需要处理无数的数据异常情况。为了解决这个问题ASNI 美国国家标准学会推出 SQL-92 standard 基于并发带来的异象首次尝试对隔离级别进行统一定义。 写在前读在后脏读读在前写在后不可重复读读在前写在后再读幻读。
2. InnodDB 和 ACID 模型
事务 ACID 模型是一种数据库设计原则InnoDB 引擎是 MySQL 默认且支持事务的存储引擎它严格遵循 ACID 模型结果也不会因软件崩溃和硬件故障等特殊情况而失真。依靠符合 ACID 标准的功能就不需要重新设计一致性检查和崩溃恢复机制。当然用户如果有额外的软件保障措施和超可靠的硬件或者应用可容忍少量数据丢失或失去 ACID 模型保护可根据 MySQL 提供的参数进行配置从而换取更高的性能和更高的吞吐。
在 MySQL 中涉及 ACID 模型组件非常多贯穿内存结构设计、磁盘结构设计以及事务和锁定模型等等。接下来我们将介绍一些 InnoDB 引擎实现 ACID 模型的一些关键技术。 2.1 Innodb Buffer Pool
InnoDB 存储引擎将数据都存储在表空间中表空间是存储引擎对文件系统上的一个文件或者几个文件的抽象。InnoDB 引擎在处理用户请求时会把需要访问页面加载到内存中页大小一般为 16k 即使只访问一个页面中的一行记录依然需要加载整个页面。在访问结束后 InnoDB 引擎并不会立即释放掉页面而是将其缓存起来内存页面淘汰策略由 LRU 算法控制。下次需要访问时就不需要进行磁盘读从而提高数据库的读写性能。这部分内存的大小由 innodb_buffer_pool_size 控制。
show variables like innodb_buffer_pool_size;2.2 Redo log
为了取得更好的读写性能InnoDB 会将数据缓存在内存中InnoDB Buffer Pool对磁盘数据的修改也会落后于内存这时如果进程或机器崩溃会导致内存数据丢失为了保证数据库本身的一致性和持久性InnoDB 维护了 Redo log。修改 Page 之前需要先将修改的内容记录到 Redo 中并保证 Redo log 早于对应的 Page 落盘也就是常说的 WALWrite Ahead Log日志优先写Redo log 的写入是顺序 IO可以获得更高的 IOPS 从而提升数据库的性能。当故障发生导致内存数据丢失后InnoDB 会在重启时通过重放 Redo将 Page 恢复到崩溃前的状态。 2.3 Undo log
undo 日志是 innodb 引擎非常重要的部分它的设计贯穿事务管理、并发控制和故障恢复等。当开启一个事务涉及到数据变动的时候就会为事务分配 undo 段以 “逻辑日志” 的形式保存事务的信息用于回滚。事务的回滚分为三种情况
用户事务管理 rollback 回滚。锁等待及死锁回滚。Crash Recovery 故障恢复回滚。
数据库在运行过程中随时可能会因为某种情况异常中断软件 bug、硬件故障运维操作等等这个时候可能已经有未提交的事务有部分已经刷新到磁盘中如果不加以处理会违反事务的原子性要么全部提交要么全部回滚。针对这种问题直观的想法是事务在提交后再进行刷盘。也就是 no-steal 策略。显而易见这种方式会造成很大的内存空间压力另一方面事务提交时的随机 IO 会影响性能。
Innodb 事务处理采用的是 steal 策略允许一个 uncommitted 的事务将修改刷新到磁盘。为了保障事务原子性事务执行过程中会持续的写入 undo log 来记录事务变更的历史值。当 Crash 真正发生时可以在 Recovery 过程中通过回放 Undo 将未提交事务的修改抹掉。
需要注意的是因为 Innodb 事务执行过程中产生的 Undo log 也需要进行持久化操作所以 Undo log 也会产生 Redo log。由于Undo log 的完整性和可靠性需要 Redo log 来保证因此数据库崩溃时需要先做 Redo log 数据恢复然后做 Undo log 回滚。
2.4 应用案例
MySQL 社区开源物理热备工具 Xtrabackup 就是基于 MySQL 故障恢复实现的。在 copy 数据文件阶段会一直监听并备份 redo log。在备份恢复阶段 Xtrabackup 需要执行一个 prepare 命令该阶段就是启动一个临时实例依赖 innodb 恢复机制应用刚才备份的 redo 文件相当于应用一遍备份期间的增量数据。
3. 隔离级别和锁机制
MySQL 5.7 及以上的版本默认的隔离级别是 RR 可通过下方 SQL 查看或修改隔离级别。
-- 查看隔离级别
show variables like %transaction_isolation%;-- 修改全局隔离级别 global 替换为 session 表示修改当前会话隔离级别
set global transaction_isolation READ-UNCOMMITTED | READ-COMMITTED |REPEATABLE-READ |SERIALIZABLE;ISO SQL标准基于事务是否可能出现的几种异象来定义隔离级别4 种隔离级别的定义见下表
级别脏读不可重复读幻读读未提交READ-UNCOMMITTED✅✅✅不可重复读READ-COMMITTED❌✅✅可重复读REPEATABLE-READ❌❌✅串行化SERIALIZABLE❌❌❌
隔离级别越严格事务的并发度就越小因此很多时候业务都需要在二者之间选择一个平衡点。
3.1 事务隔离级别
以下是测试隔离级别需要用的演示数据。
-- 创建测试表
create table account(id bigint auto_increment primary key ,name varchar(20) not null ,balance int not null
);
-- 插入测试数据
insert into account(name, balance) VALUES (张三, 300),(李四, 400),(王五, 500);3.1.1 READ UNCOMMITTED
RU 隔离级别也叫读未提交会读取到其他会话中未提交的事务修改的数据。
调整参数修改数据库隔离级别为 RU。
set global transaction_isolation READ-UNCOMMITTED;Session 1在 RU 隔离级别下开启一个事务
-- 开启事务
begin;
-- 查询数据
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 300 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 2在 RU 隔离级别下开启一个事务
-- 开启事务
begin;
-- 给张三账户余额加 1000000
update account set balance balance 100 where id 1;Session 1查询张三余额
-- 查询余额
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 400 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 2因为某种原因需要回滚事务
rollback;Session 1张三余额减少 100
-- 张三余额减少 100 并提交事务
update account set balance balance - 100 where id 1;commit;-- 查询余额
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 200 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 1 中查询张三余额为 400 时然后对张三的余额减 100事务提交后余额又变成了 200因为程序中并不知道其他会话回滚过事务这就是脏读的案例一般生产环境没有理由使用 RU 作为隔离级别。
3.1.2 READ COMMITTED
RC 隔离级别也叫读已提交事务中会读取到其他事务已提交到数据这种隔离级别下会有不可重复读和幻读的问题。
调整参数修改数据库隔离级别为 RC
set global transaction_isolation READ-COMMITTED;Session 1在 RC 隔离级别下开启一个事务
-- 开启事务
begin
-- 查询数据
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 300 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 2开启一个事务给张三余额加 100。
-- 开启事务
begin
-- 修改张三余额
update account set balance balance 100 where id 1;Session 1再次查询张三余额发现余额依然为 300在 RC 隔离级别下不会读取到未提交到事务。
-- 查询数据
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 300 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 2提交事务
commitSession 1再次查询张三余额
-- 查询数据
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 400 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------可以看到 Session 1 在事务中不同时刻读取了张三的余额得到的结果不一样这是因期间 Session 2 对张三的余额进行了改动并提交事务产生不可重复读的问题。
3.1.3 REPEATABLE READ
RR 隔离级别也叫可重复读指在一个事务内无论何时查询到的数据都与开始查询到的数据一致是 InnoDB 引擎默认的隔离级别通过更严格的锁机制一定程度上可以避免幻读。
调整参数修改数据库隔离级别为 RR
set global transaction_isolation REPEATABLE-READ;Session 1在 RR 隔离级别下开启一个事务
-- 开启事务
begin;
-- 查询数据
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 300 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 2开启事务给张三余额添加 100 并提交事务
-- 开启事务
begin;
update account set balance balance 100 where id 1;
commit;
-- 张三余额被修改为 400
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 400 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 1查询表中数据
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 300 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 2 的事务已经提交Session 1 再次读取前后的数据没有发生变化不存在不可重复读的问题。
Session 1为张三余额加 100 提交事务。
-- 为张三加 100
update account set balance balance 100 where id 1;-- 查询
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 500 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------发现最终张三的余额为 500而不是 400 元数据的一致性没有被破坏。RR 隔离级别下使用了 MVCC 多版本并发控制的机制 Select 查询不会更新更新版本号是快照读而 DML 语句操作表中的数据会更新版本号是当前读。
Session 2不显式开启事务插入一条记录。
insert into account(name, balance) VALUES (赵六, 600);Session 1再次查询。
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 500 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
---------------------Session 2 已经插入一条记录并提交Session 1 没有读取到 Session 2 插入的记录没有发生幻读。
Session 1更新赵六的记录触发一次当前读
-- 为赵六余额增加 100
update account set balance balance 100 where id 4;
-- 再次查询
select * from account;
---------------------
| id | name | balance |
---------------------
| 1 | 张三 | 500 |
| 2 | 李四 | 400 |
| 3 | 王五 | 500 |
| 4 | 赵六 | 700 |
---------------------Session 1 未提交的状态下对 Session 2 插入的新纪录触发一次当前读行版本发生了更新出现了幻读的情况。
3.1.4 SERIALIZABLE
SERIALIZABLE 可串行化隔离级别下所有的读写操作都需要加锁并发度很小避免了幻读。
-- 设置隔离级别为 SERIALIZABLE
set global transaction_isolation SERIALIZABLE;Session 1开启事务读取一行数据。
select * from account where id 4;Session 2尝试修改刚才读取的记录。
update account set balance balance 100 where id 5;
-- 锁超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction3.2 多版本并发控制
数据库的核心方向就是高并发Innodb 引擎通过并发控制技术来维护高并发的环境下数据的一致性和数据安全。Innodb 并发控制有两种技术方案锁机制(Locking) 和 多版本并发控制 (MVCC) 。
并发场景分为四类 读-读、读-写、写-读、写-写 通过锁机制可以帮助我们解决 写-写 场景下的并发问题而 MVCC 侧重优化 读-写 和 写-读 业务的高并发场景可以解决写操作时堵塞读操作的并发问题。
Innodb 引擎使用 MVCC 机制来保障事务的隔离性实现数据库的隔离级别。
3.2.1 两种读取方式
快照读(Snapshot Read) 也叫一致性非锁定读取简称 “一致性读”。一种读操作指 innodb 引擎通过多版本并发控制的方式来读取使用 Read View 基于某个时间点呈现查询结果。读取正在进行 update 或 delete 操作的行不会等待锁释放而是会读取该行的快照数据。快照就是指该行之前的版本数据主要靠 undo 日志来实现而 undo 是用于数据库回滚因此快照读本身是没有开销的。后台 Purge 线程也会自动清理一些不需要的 undo 数据。当前读(Current Read) 如果 select 语句后加上 for share、for update或者执行的是 update、delete、insert into t2 select from t1 这类语句这几种类型的 SQL 需要读取最新版本的数据被称为当前读MySQL会根据 where 条件和 SQL 的执行计划加上相应的锁阻止其他会话修改满足过滤条件的记录。
3.2.2 Read View
Read View 是实现 MySQL 一致性读的一种机制对于一条查询来讲不同隔离级别可以读取到的版本是不同的。RC 和 RR 隔离级别在实现上最根本的区别就是创建 READ VIEW 的时间不一样。对于 RC 隔离级别的事务每次执行一致性查询时都会创建一个新的 Read View这样 SELECT 就能获取系统中当前已经提交的最新数据。对于 RR 隔离级别的事务在第一次执行一致性读取时创建 READ VIEW。事务中后续再次执行 SELECT 时会重用已经创建好的 Read View这样同一个 SQL 后续重复执行时就能获取到相同的数据。
3.3 Innodb 锁机制
锁用来控制多个并发的进程或线程对共享资源的访问。在MySQL数据库中共享资源包括
内存中的链表结构如会话列表、活跃事务列表、InnoDB Buffer Pool 中 LRU 链表、脏页 Flush 链表等等。元数据包括表、SCHEMA、存储过程等。表和表中的记录。
3.3.1 两阶段锁协议
Exploring Two-Phase Locking (2PL) 指事务加锁和解锁分为两个阶段事务持续期间持有的所有锁在事务提交的时候释放。
-- 开启事务
begin;
-- 更新一行数据
update test_semi set c 1 where a 10;
-- 查看锁持有情况
--------------------------------------------------------------
| object_name | index_name | lock_type | lock_mode | lock_data |
--------------------------------------------------------------
| test_semi | NULL | TABLE | IX | NULL |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 10 |
--------------------------------------------------------------
-- 再更新一行
update test_semi set c 1 where a 11;
-- 查看锁持有情况
--------------------------------------------------------------
| object_name | index_name | lock_type | lock_mode | lock_data |
--------------------------------------------------------------
| test_semi | NULL | TABLE | IX | NULL |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 10 |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 11 |
--------------------------------------------------------------
-- 提交或回滚后释放所有的锁
commit;3.3.2 表锁
InnoDB 引擎为用户提供 LOCK TABLE…READ/WRITE 语法可以手动为表加上读锁或写锁。
# 关闭隐式提交
set autocommit 0;
# 给 test_semi 加上表锁
lock table test_semi read;
# 查看 InnoDB 层锁模式表锁的 lock_type TABLE锁的模式可以分为 S 读锁 X 写锁。
----------------------------------------------------------
| engine | object_schema | object_name | lock_type | lock_mode |
----------------------------------------------------------
| INNODB | test | test_semi | TABLE | S |
----------------------------------------------------------
# 查看 MySQL 层表锁 - MDL 元数据锁可分为 SHARED_NO_READ_WRITE 与 SHARED_READ_ONLY
------------------------------------------------------------
| object_type | object_name | lock_type | lock_status |
------------------------------------------------------------
| TABLE | test_semi | SHARED_READ_ONLY | GRANTED |
| TABLE | metadata_locks | SHARED_READ | GRANTED |
------------------------------------------------------------只加 MySQL 引擎层元数据锁的语法
FLUSH TABLES test_semi WITH READ LOCK;释放锁的语法
# 会释放掉该会话持有的所有表锁
unlock tables;解锁操作只能释放自己当前会话持有的锁不能解开其他会话持有的表锁。直接结束会话该会话所持有的锁会释放。
3.3.3 共享锁与排它锁
InnoDB 引擎行锁有两种模式分别为 共享锁(S) 和 排他锁(X)。
3.3.4 意向锁
由于 InnoDB 引擎支持表和记录粒度的锁 为什么需要意向锁
3.3.5 记录锁
InnoDB 记录锁的模式跟事务的隔离级别、执行的 SQL 语句、语句的执行计划都有关系。
在 RC 隔离级别下允许幻读发生所以不需要 Gap 锁一般只锁定行。本文通过几个案例讨论在 RR 级别下 InnoDB 的加锁规则。
3.3.6 Gap 锁
在 RR 或更高的隔离级别下为了避免出现幻读InnoDB使用了 GAP 锁阻止其他事务往被锁定的区间内插入数据。
根据 3.1.3 小节的测试在 RR 隔离级别下普通的查询是快照读是不会看到别的事务插入的数据的。因此幻读在 “当前读” 下才会出现。
-- 当前读锁定一个范围
select * from test_semi where a 11 for update;
----------------
| a | b | c |
----------------
| 10 | 1 | 2 |
------------------ 查看锁持有情况
---------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | lock_mode | lock_data |
---------------------------------------------------------------------
| INNODB | test | test_semi | TABLE | IX | NULL |
| INNODB | test | test_semi | RECORD | X | 10 |
| INNODB | test | test_semi | RECORD | X,GAP | 11 |
---------------------------------------------------------------------不仅会锁住对应的记录也会给记录之间的间隙加上 Gap 锁此时如果执行一个写入操作会被堵塞。
-- 写入一条记录a 6
insert into test_semi value(6, 1, 2);另外间隙锁与间隙锁之间可以共存一个事务不会阻止另一个事务给同一个间隙加间隙锁因为它们的目标是共同保护一个间隙不允许新插入值。
Session 1查询一个不存在的范围。
select * from test_semi where a 6 for update;
-- 此时的间隙锁加在 10-∞ 间隙
---------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | lock_mode | lock_data |
---------------------------------------------------------------------
| INNODB | test | test_semi | TABLE | IX | NULL |
| INNODB | test | test_semi | RECORD | X,GAP | 10 |
---------------------------------------------------------------------
-- 此时再次查询一行不存在的记录
select * from test_semi where a 8 for update;-- 10-∞ 又多了一把间隙锁
---------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | lock_mode | lock_data |
---------------------------------------------------------------------
| INNODB | test | test_semi | TABLE | IX | NULL |
| INNODB | test | test_semi | RECORD | X,GAP | 10 |
| INNODB | test | test_semi | TABLE | IX | NULL |
| INNODB | test | test_semi | RECORD | X,GAP | 10 |
---------------------------------------------------------------------3.3.7 Next-Key 锁
间隙锁和行锁合称 next-key lock每个 next-key lock 是前开后闭区间可以避免幻读。
select * from t for update;
----------------
| id | c | d |
----------------
| 0 | 0 | 0 |
| 5 | 5 | 5 |
| 10 | 10 | 10 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
----------------
-- 锁的模式
----------------------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | lock_mode | lock_data |
----------------------------------------------------------------------------------
| INNODB | test | t | TABLE | IX | NULL |
| INNODB | test | t | RECORD | X | supremum pseudo-record |
| INNODB | test | t | RECORD | X | 0 |
| INNODB | test | t | RECORD | X | 5 |
| INNODB | test | t | RECORD | X | 10 |
| INNODB | test | t | RECORD | X | 15 |
| INNODB | test | t | RECORD | X | 20 |
| INNODB | test | t | RECORD | X | 25 |
----------------------------------------------------------------------------------如上方查询整个表所有记录锁起来就形成了 7 个 next-key lock分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, supremum]。 InnoDB 引擎行锁是加在索引上的Next-key lock 是 gap 锁与行锁的组合。由上图Next-key lock 是索引记录锁加上索引记录之前的间隙锁。
3.3.8 半一致性读取
Update 或 Delete 在执行的时候会根据表中的索引情况扫描定位数据在 RR 隔离级别下扫描到的数据都会加锁直到会话结束后释放所以过滤字段无索引的 update 扫描几乎锁定了整张表。如果有二级索引通过索引定位数据扫描数据的范围变小锁定的范围也变小了。
在 RC 隔离级别下扫描到的记录也会先加锁不过确认是不匹配的行后会主动释放掉锁。
接下来通过实验创建测试表并填充数据
CREATE TABLE test_semi (a int NOT NULL,b int DEFAULT NULL,c int DEFAULT NULL,PRIMARY KEY (a)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ciinsert into test_semi values(10, 1, 0),(11, 2, 0),(12,1,0),(13,2,0),(14,1,0);当前隔离级别为 RR开启一个 Session 会话执行如下 SQL
begin;
update test_semi set c 22 where b 1;由于 b 字段无索引所以通过主键索引进行扫描定位记录查询锁持有情况
-----------------------------------------------------------------------
| object_name | index_name | lock_type | lock_mode | lock_data |
-----------------------------------------------------------------------
| test_semi | NULL | TABLE | IX | NULL |
| test_semi | PRIMARY | RECORD | X | supremum pseudo-record |
| test_semi | PRIMARY | RECORD | X | 10 |
| test_semi | PRIMARY | RECORD | X | 11 |
| test_semi | PRIMARY | RECORD | X | 12 |
| test_semi | PRIMARY | RECORD | X | 13 |
| test_semi | PRIMARY | RECORD | X | 14 |
-----------------------------------------------------------------------在 RR 隔离级别下由于 b 字段无索引Update 会为读取的每一行数据都上锁并且不会释放。如果过滤字段有二级索引那么只会锁定匹配的记录以及防止幻读的 Next-Key 锁。
--------------------------------------------------------------
| object_name | index_name | lock_type | lock_mode | lock_data |
--------------------------------------------------------------
| test_semi | NULL | TABLE | IX | NULL |
| test_semi | idx_b | RECORD | X | 1, 10 |
| test_semi | idx_b | RECORD | X | 1, 12 |
| test_semi | idx_b | RECORD | X | 1, 14 |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 10 |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 12 |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 14 |
| test_semi | idx_b | RECORD | X,GAP | 2, 11 |
--------------------------------------------------------------接下来在 RC 隔离级别下复现一次上面的实验
begin;
update test_semi set c 22 where b 1;查看锁持有情况
--------------------------------------------------------------
| object_name | index_name | lock_type | lock_mode | lock_data |
--------------------------------------------------------------
| test_semi | NULL | TABLE | IX | NULL |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 10 |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 12 |
| test_semi | PRIMARY | RECORD | X,REC_NOT_GAP | 14 |
--------------------------------------------------------------在 RC 隔离级别下由于不符合过滤条件的行会被快速释放锁所以相比 RR 隔离级别锁的持有时间更短锁的冲突也更少。除此之外在 RC 隔离级别下对于 Update 语句如果某行已经被锁定则 InnoDB 将执行 “半一致性读”将最新提交的版本返回给 MySQL用于判断是否匹配 where 条件如果匹配的话表示必须更新改行那么会再次读取改行这次 InnoDB 要锁定它或者等待它持有的锁释放。
继续刚才的实验目前 b 1 的记录已经被锁定且 b 字段无索引需要通过主键索引进行记录匹配主键索引已经有记录被锁定了此时会用到半一致性读取读取锁定行的版本用于判断是否匹配过滤条件无需等待锁定行的锁释放。
-- 另一个 Session 再开启一个事务
begin;
update test_semi set c 22 where b 2;-- 查询
select * from test_semi;
----------------
| a | b | c |
----------------
| 10 | 1 | 0 |
| 11 | 2 | 6 |
| 12 | 1 | 0 |
| 13 | 2 | 6 |
| 14 | 1 | 0 |
----------------3.3.9 可重复读的锁定案例
下方 SQL 是本次实验实验的表结构
CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,d int(11) DEFAULT NULL,PRIMARY KEY (id),KEY c (c)
) ENGINEInnoDB;insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);案例一唯一索引等值查询不存在的记录
Session 1通过主键字段访问一行不存在的记录。
begin;
select * from t where id 7 for update;Session 2尝试向间隙插入一条记录被堵塞。
insert into t value (8, 8, 8);
-- blockedSession 3尝试修改 id 10 的记录发现是可以执行成功。
update t set d d 1 where id 10;
-- Rows matched: 1 Changed: 1 Warnings: 0Session 1 访问了一个不存在的 id 7 记录会锁定 (5, 10] 的间隙由于 id 10 不符合过滤条件Next-key lock 退化为间隙锁 (5, 10) 所以 id 10 的行可以正常被更新。
---------------------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |
---------------------------------------------------------------------------------
| INNODB | test | t | TABLE | NULL | IX | NULL |
| INNODB | test | t | RECORD | PRIMARY | X,GAP | 10 |
---------------------------------------------------------------------------------案例二唯一索引范围扫描
下方两条查询得到的结果都相同但是获取的锁不同。等值查询只加一个行锁。
select * from t where id 10 for update;
-------------------------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |
-------------------------------------------------------------------------------------
| INNODB | test | t | TABLE | NULL | IX | NULL |
| INNODB | test | t | RECORD | PRIMARY | X,REC_NOT_GAP | 10 |
-------------------------------------------------------------------------------------范围查询则会额外添加一个间隙锁锁定的范围是 (10, 15)。
select * from t where id 10 and id 11 for update;
-------------------------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |
-------------------------------------------------------------------------------------
| INNODB | test | t | TABLE | NULL | IX | NULL |
| INNODB | test | t | RECORD | PRIMARY | X,REC_NOT_GAP | 10 |
| INNODB | test | t | RECORD | PRIMARY | X,GAP | 15 |
-------------------------------------------------------------------------------------案例三非唯一索引等值覆盖查询
普通索引查询由于没有唯一性约束在扫描的时候都会向右再多查一个记录所以 (5, 10) 的间隙也被锁住。
----------------
| id | c | d |
----------------
| 0 | 0 | 0 |
| 5 | 5 | 6 |
| 10 | 10 | 11 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
----------------Session 1通过二级索引 c 等值查询。
begin;
select id from t where c 5 for share;Session 2试探一下间隙锁此时锁住的间隙是 (-∞, 5)(5, 10)。
insert into t value(6, 6, 6);
-- blockedSession 3通过主键更新一条记录此时发现是可以执行成功的。
update t set d d 1 where id 5;由于 Session 1 无需回表只查询索引即可。主键上没有任何锁所以 Session 2 才能正常更新。
---------------------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |
---------------------------------------------------------------------------------
| INNODB | test | t | TABLE | NULL | IS | NULL |
| INNODB | test | t | RECORD | c | S | 5, 5 |
| INNODB | test | t | RECORD | c | S,GAP | 10, 10 |
---------------------------------------------------------------------------------如果是当前读InnoDB 也会把主键锁定。因为当前读一般用于 DML 意味着要更新数据所以即使没有访问到主键也会锁定主键。
-------------------------------------------------------------------------------------
| engine | object_schema | object_name | lock_type | INDEX_NAME | lock_mode | lock_data |
-------------------------------------------------------------------------------------
| INNODB | test | t | TABLE | NULL | IX | NULL |
| INNODB | test | t | RECORD | c | X | 5, 5 |
| INNODB | test | t | RECORD | PRIMARY | X,REC_NOT_GAP | 5 |
| INNODB | test | t | RECORD | c | X,GAP | 10, 10 |
-------------------------------------------------------------------------------------案例四非唯一索引范围扫描
Session 1非唯一索引范围查询。
select * from t where c 10 and c 11 for update;
----------------
| id | c | d |
----------------
| 10 | 10 | 11 |
----------------Session 2插入一条记录由于间隙锁被堵塞。
insert into t value(8, 8, 8);
-- blockedSession 3尝试更新一条记录发现 Next-key lock。
update t set d d 1 where c 15;InnoDB 搜索过程中访问到的对象会加锁。首先 SQL 会通过 c 10 使用 Next-key lock 锁定 (5, 10]由于是非唯一索引需要扫描到 C 15 的位置才会结束扫描所以 (10, 15] 区间也被锁住。
案例五非唯一索引等值查询
新插入一条记录此时表中的数据如下。
----------------
| id | c | d |
----------------
| 0 | 0 | 0 |
| 5 | 5 | 5 |
| 10 | 10 | 10 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
| 30 | 10 | 30 |
----------------Session 1通过索引 c 10 当前读两条记录。
begin;
select * from t where c 10 for update;InnoDB 会先定位 c 10 条件然后锁定 c 索引上 (5, 10] 间隙由于是二级索引需要向右再扫描 c 15 这行结束索引中 (10, 15] 被锁定由于是等值查询Next-key lock 退化为 Gap 锁最终锁定的范围是 (5, 10)、(10, 15)。 Session 2尝试向间隙中插入一条记录。
insert into t value(12, 12, 12);
-- blockedSession 3更新一条记录可以正常执行验证了 Next-key 退化为 Gap 锁的推论。
update t set d d 1 where c 15;与案例四形成对比二级索引范围扫描与二级索引等值扫描都需要先前额外扫描一条记录区别是范围扫描会使用 Next-key 锁定该条记录等值查询则不会。
案例六limit 语句加锁
Session 1开启一个事务使用与案例五相同的 SQL区别是添加了 limit 2。
begin;
select * from t where c 10 limit 2 for update;Session 2尝试插入数据执行成功案例的区别就是 limit。
insert into t value(12, 12, 12);
Query OK, 1 row affected (0.01 sec)由于加了 limit扫描到第二行记录就结束了所以相比于案例五没有加 (10, 15) 的间隙锁因为没有继续向右扫描。由此可见给 SQL 添加 limit 可以减小加锁的范围。
案例七Next-key lock 实现方式
Session 1二级索引等值查询锁定的范围是 (5, 10](10, 15)。
begin;
select * from t where c 10 for share;Session 2二级索引更新一条记录需要给 (5, 10] 范围加 x 锁由于 Session 1 持 s 锁出现堵塞情况。
update t set d d 1 where c 10;Session 1事务内再插入一条记录竟然出现死锁。
insert into t value(8, 8, 8);Session 2
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction由此案例可以看出 Next-key lock 是 Gap lock 与行锁的组合。Session 2 虽然被堵塞其实 Gap lock 已锁定 (5, 10)只是在等待 c 10 的行锁。所以 Session 1 插入的时候系统检测出了死锁情况。
3.3.10 死锁案例
死锁是指不同事务无法继续进行的情况因为每个事务都持有另一个事务需要的锁。因为两个事务都在等待资源变得可用所以都不会释放它所持有的锁。
案例一多线程批量更新场景
死锁的等待关系如图死锁日志中只提供了当前堵塞的 SQL 语句无法得到完整的事务 SQL所以出现死锁往往需要找研发了解业务逻辑事务内的其他 SQL 语句。
现在模拟一个业务真实遇到的死锁场景一个批量刷新商品展示图片的状态信息业务使用的是主键多线程更新。使用之前 test_semi 表来模拟当时的业务情况。
事务 1按照主键字段批量更新。
begin;
update test_semi set b 0 where a 11;
update test_semi set b 0 where a 12;事务 2更新了事务 1 已经锁定的记录。
begin
update test_semi set b 0 where a 13;
update test_semi set b 0 where a 12;事务 1此时还是持续更新更新的行是事务 2 已经锁定的记录。
update test_semi set b 0 where a 13;事务 2 被回滚事务 1 执行成功。
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction场景描述该业务场景是一个更新一个商品的展示图片的状态场景一个商品有多张图片更新时都放在一个事务中。任务是从上游 kafka 生产的可能由于意料之外的场景出现了一个商品被重复消费的情况就造成上了上方的死锁问题。
3.4 metadata lock
MySQL 中还有一种特殊的表锁MDL(metadata lock) 元数据锁是在 MySQL Server 加的一种锁其作用是维护 DDL 变更数据的安全性和正确性。
当对一个表进行 DML 时需要加 MDL 读锁当需要对一张表结构进行变更时需要加 MDL 写锁。读锁之间不互斥即可以多个线程对一张表进行并发增删改。读写锁与写锁之间是互斥的用来保证变更表结构操作的安全性。因此如果有两个线程要同时给一个表加字段其中一个要等另一个执行完才能开始执行。
从上面我们可以了解到读操作与写操作都需要加 MDL 读锁而 DDL 需要加 MDL 写锁两者互斥的那么 Online DDL 如何保障并发 DML 呢这里就要介绍 Online DDL 加锁过程
首先在开始进行 DDL 时需要拿到对应表的 MDL 写锁然后进行一系列的准备工作然后MDL 写锁降级为MDL 读锁开始真正的 DDL最后再次将 MDL 读锁升级为 MDL 写锁完成 DDL 操作释放 MDL 锁
其中第二阶段占用了 DDL 整个过程的大量时间在这段时间 DDL 才是真正的 online。
如果有一个大查询持有 MDL 读锁那么我们此时进行一个 DDL 操作后因为查询持有读锁DDL 需要加写锁所以变更必须等待查询结束才能进行此时再进行一个查询就会被卡住请看案例。
Session 1Session 2Session 3Session 4begin;select * from sbtest1 limit 5;select * from sbtest1 limit 5;alter table sbtest1 drop column fantasy ,ALGORITHMINPLACE, LOCKNONE; ❌select * from sbtest1 limit 5; ❌
Session 1开启一个事物执行一条查询此时 Session 1 持有 MDL 读锁Session 2也执行一条查询执行一条查询正常执行Session 3执行一条 DDL 语句因为需要 MDL 写锁 被 Session 1 读锁Session 4执行一条查询因为需要读锁但是因为 Session 3 也处于等待状态后面的增删改查都处于等待状态。也就是说这张表完全不可用了连查询也被锁定了。
后记
本篇文章介绍事务有并发和安全两种挑战在 InnoDB 引擎中是如何解决并发挑战的。通过大量案例详细解读了 InnoDB 锁机制希望大家都能有所收获。文章中有不正确的地方欢迎指正后面还会不断完善相关案例。
