小王:我肯定付款了啊,不然怎么下单。
老板说:我没收到钱啊。你把付款的截图发给我。
小王说:我吃饭还能不付钱吗,你等着。
于是小王给老板截图了,老板拿着截图去找了美团技术,美团技术一查,转账失败。跟老板说不好意思,今天这代码是实习生写的,我们马上开除他,稍后转给你。这时候老板一颗悬着的心才放下,可不能一天就卖一份水饺还没收到钱,这不亏大了呢!
以上纯属虚构,没有诋毁美团实习生的意思。
从上面的问题看,付款成功了,转账失败了,这时候用户吃到了饭,但是老板没收到钱。放在正常的堂食,你不先付款,估计人儿就的赶你出去,一手交钱一手交货买卖不变的道理。
我们引申出一个概念:最小操作单元。即我们人为定义了一个业务场景,这个场景中的操作要么全部成功,要么全部失败。
英语原文中把这种最小操作单元定义为:transaction ,在英语中的解释是:
an occasion when someone buys or sells something, or when money is exchanged or the activity of buying or selling something:
- a business transaction
- Each transaction at the foreign exchange counter seems to take forever
- We need to monitor the transaction of smaller deals.
通俗的说就是我们做某事所发生的这个时机或这个场景,代指这整个的发生过程。在 MySQL 中我们把 transaction 翻译为 事务,个人感觉中文意思总和英文有点不搭。
上面这个例子中我们可以了解到 transaction 存在的主要意图:
- 在最小操作单元中保持稳定的操作,即使在故障时也能恢复到操作之前的状态保持数据一致性。
- 保持各个最小操作单元之前互相隔离,以防止互相交互产生的覆盖性错误。
一般需要事务来控制的场景发生在:
更新–插入–选择–插入–
即一个最小操作单元中保持两个及以上的非查询操作。
事务结束的两种可能方式:
- commit:提交最小操作单元中的所有操作。
- terminate:操作终止,最小操作单元中所有修改无效。
数据库操作的环境:
- 共享-多用户并发访问
- 不稳定-潜在的硬件/软件故障
事务所需环境:
- 不共享 - 一个事务内的操作不受其他事务影响
- 稳定 - 即使面对系统故障,当前事务的操作也能保留现场
一个事务一旦开始,则必须确保:
- 所有操作必须可回溯
- 所有操作对后续操作的影响必须是可见的
一个事务开始的过程中必须确保:
在该事务结束之前其他事务看不到它的结果。
如果事务中止:
必须确保当前事务所有可能影响数据一致性的操作都会被清理。
如果系统出现故障:
必须确保重新启动时所有未提交的事务都会被清理。
针对以上事务操作过程中可能会出现的问题,抽象出事务如果满足以下条件,则可以保证数据完整性:
- Automicity(原子性)
要么事务中的所有任务都必须发生,要么都不发生。
- Consistency(一致性)
每个事务都必须保留数据库的完整性约束(已声明的一致性规则)。它不能使数据处于矛盾状态。在执行期间,一系列数据库操作不会违反任何完整性约束。
- Isolation(隔离性)
两个同时进行的事务不能互相干扰。交易中的中间结果必须对其他交易不可见。其他一系列数据库操作无法看到一系列数据库操作的中间状态。
- Durability(持久性)
已完成的事务以后不能中止或放弃其结果。它们必须在崩溃后通过(例如)重新启动DBMS持续存在。保证已提交的一系列数据库操作将永久保留。
特意查证了一下,关于事务四大特性的提出最早是在 1983 年由 Andreas Reuter 和 Theo Haerder 两位关系型数据库研发的鼻祖在论文:Principles of transaction-oriented database recovery
中提出。论文链接,感兴趣的可以下载来看看。
事务的 ACID 特性概念简单,但不是很好理解,主要是因为这几个特性不是一种平级关系:
- 只有满足一致性,事务的执行结果才是正确的。
- 在无并发的情况下,事务串行执行,隔离性一定能够满足。此时只要能满足原子性,就一定能满足一致性。 在并发的情况下多个事务并行执行,事务不仅要满足原子性,还需要满足隔离性,才能满足一致性。
- 事务满足持久化是为了能应对数据库崩溃的情况。
InnoDB 如何实现事务
鉴于 MyISAM 引擎不支持事务,支持事务的引擎只有 InnoDB,所以下面关于事务的讲解都是基于 InnoDB引擎。
在 InnoDB引擎中实现事务最重要的东西就是日志文件,保证事务的四大特性主要依靠这两大日志:
- redo log :保证事务持久性
- undo log:回滚日志,保证事务原子性
两大日志系统分别保证了持久性和原子性,另外还有两大特性是通过什么来保证的呢?
一致性 和 隔离性 是通过 MVCC 机制 和 锁机制来一起控制。先提前介绍,后面我们详解讨论。
典型的事务操作会遵循如下流程:
start transaction;
… # do your business
commit;
start transaction
标识事务的开始,直到遇到 commit
才会提交事务。在该事务过程中如果出现问题,会自动调用 rollback 逻辑回滚该事物已完成的 sql。
非显式开启事务
MySQL 中默认采用的是自动提交的模式:
mysql > show variables like ‘autocommit’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| autocomment | ON |
±-----------------±------+
自动模式下,你无需显式的输入 start transaction
作为开头和使用 commit
作为结尾来标识一个事务。每个sql 语句都会被作为一个事务提交。
当然你也可以关闭自动提交事务机制:
mysql > set autocommit = 0;
需要注意的是:autocommit
参数的修改指只针对当前连接,在一个连接中修改该属性并不会影响别的连接。
不被 autocommit 影响的操作
MySQL 中提供了一些不会被 autocommit 属性值所影响的特殊指令,这些指定即使在事务中执行,他们也会立刻执行而不是等到 commit 语句之后再提交,这些特殊指令包括:DDL(create table / drop table / alter table)
、lock tables
等等。
我们探讨事务到底在探讨什么?
事务的定义我们已经了解,无非就是把几个有上下文关联的 sql 放在一起操作要么全部成功,要么全部失败。道理很简单,那我们分析这么多到底在分析什么呢?貌似难的点不在于打包执行,在于如果让这些打包命中不互相影响,事务执行过程中失败如何回滚操作且不污染现有数据。这些才是我们讨论事务应该关注的地方。
这些问题的根本其实又回到了事务的四大特性,不得不说 Theo Haerder 在 1983 年就能抽象出来如此高度凝练的总结实在是让当下汗颜。
下面我就从 InnoDB 如何保证四大特性入手,逐一分析事务机制的实现。
保证原子性的关键技术 - undo log
对于事务的原子性来说,该事务内所有操作要么全部成功要么全部失败就是事务的原子性。
全部成功这个毋庸置疑,如果中间突然失败,原子性该如何保证呢?是否该回滚当前已经执行成功的操作。
InnoDB 提供了一种日志:undo log,它有两个作用:提供 回滚 和 多个行版本控制(MVCC)。
比如一条 delete 操作在 undo log 中会对应一条 insert 记录,反之亦然。当 update 操作时,它会记录一条相反的 update 记录。
当执行 rollback 时,就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。
有时候应用到行版本控制的时候,也是通过 undo log 来实现的:当读取的某一行被其他事务锁定时,它可以从 undo log 中分析出该行记录以前的数据是什么,从而提供该行版本信息,让用户实现非锁定一致性读取。
undo log 的存储方式
InnoDB 存储引擎对 undo log 的管理采用段的方式。rollback segment 称为回滚段,每个回滚段中有 1024 个 undo log slot 。
在以前老版本,只支持 1 个 rollback segment,这样就只能记录 1024 个 undo log slot。后来 MySQL5.5 可以支持 128 个 rollback slot,即支持 128 * 1024 个 undo log 操作。
MySQL5.6 之前,undo log 表空间位于共享表空间的回滚段中,共享表空间的默认的名称是 ibdata,位于数据文件目录中。
MySQL5.6 之后,undo log 表空间可以配置成独立的文件,但是提前需要在配置文件中配置,完成数据库初始化后生效且不可改变 undo log 文件的个数。如果初始化数据库之前没有进行相关配置,那么就无法配置成独立的表空间了。
MySQL5.7 之后的独立 undo log 表空间配置参数如下:
innodb_undo_directory = /data/undospace/ #undo独立表空间的存放目录
innodb_undo_logs = 128 #回滚段为128KB
innodb_undo_tablespaces = 4 #指定有4个undo log文件
undo log 的删除时机
undo log 文件的个数是有限制的,所以不用无限堆积日志文件。undo log 记录的是当前事务操作的反向记录,理论上当前事务结束,undo log 日志就可以废弃。上面也提到过的多版本并发控制机制在隔离级别为 repeatable read
的时候事务读取的数据都是该事务最新提交的版本,那么只要该事务不结束,行版本记录就不能删除。
另外不同的 sql 语句对应的 undo log 类型也不一样,比如:
- insert 语句:因为 insert 操作本身只对该事务可见,事务提交之前别的连接是看不到的,所以 insert 操作产生的 undo log 日志在事务提交之后会马上直接删除,后续不会再被别的功能使用。
- update / delete 语句:delete 操作在事务中并不会真的先删除数据,而是将该条数据打上 “delete_bit” 标识,后续的删除操作是在事务提交后的 purge 线程独立操作。这两种操作产生的 undo log 日志都可以用反向的 update 来代替,这种操作上面说过 MVCC 机制可能会用上,所以就不能在事务结束之后直接删除。
在事务提交之后,也不是马上就删除该事务对应的 undo log 日志,而是将该事务对应的文件块放入到删除列表中,未来通过 purge 来删除。并且提交事务时,还会判断 undo log 分配的页是否可以重用,如果可以重用,则会分配给后面来的事务,避免为每个独立的事务分配独立的 undo log 页而浪费存储空间和性能。
持久性 - redo log
redo log 即重做日志,重做日志记录每次操作的物理修改。
说 redo log 之前其实是要先说一下 binlog,不然就不知道为什么要引入 redo log。
bin log = binary log,二进制日志,它记录了除了 select 之外所有的 DDL 和 DML 语句。以事件形式记录,还包含语句所执行的消耗的时间,MySQL 的二进制日志是事务安全型的。
binlog日志有两个最重要的使用场景:
- mysql 主从复制: mysql replication 在 master 端开启 binlog,master 把它的二进制日志传递给 slaves 来达到 master-slave 数据一致的目的。
- 数据恢复: 通过 mysqlbinlog 工具来恢复数据。
binlog 日志包括两类文件:
- 二进制日志索引文件(文件名后缀为 .index)用于记录所有的二进制文件。
- 二进制日志文件(文件名后缀为 .00000*)记录数据库所有的 DDL 和 DML 语句事件。
binlog 文件是通过追加的方式写入的,可通过配置参数max_binlog_size
设置每个 binlog 文件的大小,当文件大小大于给定值后,日志会发生滚动,之后的日志记录到新的文件上。
binlog 有两种记录模式,statement 格式的话是记 sql 语句,row 格式会记录行的内容。
持久性问题一般在发生故障的情况才会重视。在启动 MySQL 之后无论上次是否正常关闭都会进行恢复操作,我们假设现在没有 redo log 只有 binlog,那么数据文件的更新和写入 binlog 只有两种情况:
- 先更新数据文件,再写入 binlog;
- 先写入 binlog,再更新数据文件。
如果先更新数据文件,接着服务器宕机,则导致 binlog 中缺少最后的更新信息;如果先写 binlog 再更新数据则可能导致数据文件未被更新。
所以在只有 binlog 的环境中 MySQL 是不具备 crash-safe 的能力。另外一开始的 MySQL 使用 MyISAM 引擎,它只有 binlog,所以自然不支持事务。后面引入了 InnoDB 之后才开始使用另外一套日志系统- redo log 来实现 crash-safe 功能。
redo log 和 binlog 的区别:
- redo log 是 InnoDB 引擎特有的,binlog 是MySQL server 层实现的功能,与引擎无关。
- redo log 是物理日志,记录 “在某个数据页做了什么修改”;binlog 是逻辑日志,记录 sql 语句的原始逻辑,比如 “给 ID = 1 这一行的 name value set ‘xiaoming’ ”。
- redo log 空间是固定的,用完之后会覆盖之前的数据;binlog 是追加写,当前文件写完之后会开启一个新文件继续写。
redo log 由两部分组成:
- 内存中的重做日志缓冲(redo log buffer)
- 重做日志文件(redo log file)
一个更新事务的整体流程
从一个事务的更新过程出发看看一个事务更新过程中 redo log 处于什么地位。
- 首先检查 Buffer cache 中是否存在这条数据,如果存在直接返回,如果不存在则去索引树中读取这条数据并加载到 Buffer Cache。
- 执行器拿到这条行数据之后对它执行相应的更新操作。
- 将这条待更新的行数据调用执行引擎更新到 Buffer Cache 中,同时将这个记录更新到 redo log 里面,redo log 包含两个部分的更新,更新完毕,此时 redo log 处于 prepare 的状态,然后告诉执行器,你可以提交事务。
- 执行器生成这个操作的 binlog 日志,并把 binlog 写入磁盘。
- 执行器调用引擎的提交事务接口,引擎把刚写入的 redo log 改为 commit 状态,整个事务提交完成。
这里我们注意到在 redo log 的提交过程中引入了两阶段提交。
两阶段提交
为什么必须有 “两阶段提交” 呢?这是为了让两份日志之间的逻辑一致。
前面我们说过了,binlog 会记录所有的逻辑操作,并且是采用 “追加写” 的形式。如果你的 DBA 承诺说半个月内可以恢复,那么备份系统中一定会保存最近半个月的所有binlog,同时系统会定期做整库备份。
由于 redo log 和 binlog 是两个独立的逻辑,如果不用两阶段提交,要么就是先写完 redo log 再写 binlog,或者采用反过来的顺序,我们看看这两种方式会有什么问题,用上面的 update 示例做假设:
- 先写 redo log 后写 binlog。假设在 redo log 写完,binlog 还没有写完的时候,MySQL 进程异常重启。因为 redo log 已经写完,系统即使崩溃仍然能够把数据恢复回来。但是 binlog 里面就没有记录这个语句,因此备份日志的时候 binlog 里面就没有这条语句。
但是如果需要用这个 binlog 来恢复临时库的话,由于这个语句的 binlog 丢失,恢复出来的值就与原库值不同。
- 先写 binlog 后写 redo log。如果在 binlog 写完之后宕机,由于 redo log 还没写,崩溃恢复以后这个事务无效,所以这一行的值还是未更新以前的值。但是 binlog 里面已经记录了崩溃前的更新记录, binlog 来恢复的时候就多了一个事务出来与原库的值不同。
可以看到,两阶段提交就是为了防止 binlog 和 redo log 不一致发生。同时我们也注意到为了这个崩溃恢复的一致性问题引入了很多新的东西,也让系统复杂了很多,所以有得有失。
InnoDB通过 Force Log at Commit
机制保证持久性:当事务提交(COMMIT)时,必须先将该事务的所有日志缓冲写入到重做日志文件进行持久化,才能 COMMIT 成功。
为了确保每次日志都写入 redo log 文件,在每次将 redo log buffer cache 写入重做日志文件后,InnoDB 引擎都需要调用一次 fsync 操作。因此磁盘的性能决定了事务提交的性能,也就是数据库的性能。
innodb_flush_log_at_trx_commit
参数控制重做日志刷新到磁盘的策略:
- 0:事务提交时不进行写入重做日志操作,仅在 master thread 每秒进行一次。
- 1:事务提交时必须调用一次
fsync
操作。 - 2:仅写入文件系统缓存,不进行
fsync
操作。
log buffer 根据如下规则写入到磁盘重做日志文件中:
- 事务提交时。
- 当 log buffer 中有一半的内存空间已经被使用。
- log checkpoint 时,checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置。
一致性 和 隔离性实现 - 锁机制 和 MVCC
实现一致性和隔离性是保证数据准确性的关键一环,前面两个特性保证数据恢复不出问题,这两个特性要保证数据插入和读取不出问题。实现一致性和隔离性主要使用了两个机制:
- 锁机制
- 多版本并发控制
下面我们就事务会产生哪些问题,MySQL 提出什么方式来解决问题,这些方式的实现方案又是什么来讲解。
并发下事务会产生哪些问题
事务 A 和 事务 B 同时操作一个资源,根据不同的情况可能会出现不同问题,总结下来有以下几种:
- 脏读
事务 A 读到了事务 B 还未提交的数据。
- 幻读
在当前事务中发现了不属于当前事务操作的数据。幻读是针对数据 insert 操作来说的。假设事务A对某些行的内容作了更改,但是还未提交,此时事务 B 插入了与事务 A 更改前的记录相同的记录行,并且在事务 A 提交之前先提交了,而这时,在事务A中查询,会发现好像刚刚的更改对于某些数据未起作用,但其实是事务 B 刚插入进来的,让用户感觉出现了幻觉,这就叫幻读。
- 可重复读
可重复读指的是在一个事务内,最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据 update 操作。
- 不可重复读
在同一个事务中两次读取一个数据的结果不一样。对比可重复读,不可重复读指的是在同一事务内,不同的时刻读到的同一批数据可能是不一样的,可能会受到其他事务的影响,比如其他事务改了这批数据并提交了。
为什么会提出隔离级别的概念
为了解决事务并发过程中可能会产生的这些问题,SQL 标准定义的四种隔离级别被 ANSI(美国国家标准学会)和 ISO/IEC(国际标准)采用,每种级别对事务的处理能力会有不同程度的影响。
SQL 标准定义了四种隔离级别,MySQL 全都支持。这四种隔离级别分别是:
- 读未提交(READ UNCOMMITTED)
- 读提交 (READ COMMITTED)
- 可重复读 (REPEATABLE READ)
- 串行化 (SERIALIZABLE)
从上往下,隔离强度逐渐增强,性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定,其中,可重复读是 MySQL 的默认级别。
事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题,下面展示了 4 种隔离级别对这三个问题的解决程度。
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | 会发生 | 会发生 | 会发生 |
读提交 | 不会发生 | 会发生 | 会发生 |
可重复读 | 不会发生 | 不会发生 | 会发生 |
串行化 | 不会发生 | 不会发生 | 不会发生 |
只有串行化的隔离级别解决了全部这 3 个问题,其他的 3 个隔离级别都有缺陷。
如何设置事务隔离级别
我们可以通过以下语句查看当前数据库的隔离级别,通过下面语句可以看出我使用的 MySQL 的隔离级别是 REPEATABLE-READ
,也就是可重复读,这也是 MySQL 的默认级别。
mysql> show variables like 'trans
《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》
【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享
action_isolation’;
±----------------------±----------------+
| Variable_name | Value |
±----------------------±----------------+
| transaction_isolation | REPEATABLE-READ |
±----------------------±----------------+
1 row in set (0.02 sec)
或者:
mysql> SELECT @@transaction_isolation;
±------------------------+
| @@transaction_isolation |
±------------------------+
| REPEATABLE-READ |
±------------------------+
1 row in set (0.00 sec)
当然我们也能手动修改事务的隔离级别:
set [作用域] transaction isolation level [事务隔离级别];
作用域包含:
SESSION:SESSION 只针对当前回话窗口
GLOBAL:全局生效
隔离级别包含:
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
我们来测试一下各个隔离级别对事务的影响。
新建表:
CREATE TABLE test_db
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(255) NOT NULL DEFAULT ‘’ COMMENT ‘name’,
PRIMARY KEY (id
),
KEY name_idx
(name
(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT=‘测试表’;
插入一些测试数据。
读未提交(READ UNCOMMITTED)
首先设置事务隔离级别:
set global transaction isolation level READ UNCOMMITTED;
注意:设置完全局隔离级别只对新打开的 session 有效,历史打开的是不会受到影响的。
首先关闭事务自动提交:
set autocommit = 0;
开启事务 A:
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> insert test_db (name) values (‘xiaocee’);
Query OK, 1 row affected (0.01 sec)
在事务A 中插入一条数据,并未提交事务。
接着开启事务B:
mysql> select * from test_db;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaocee |
±—±----------+
9 rows in set (0.00 sec)
事务 B 中能够查到这条数据。即不同的事务能读到对方未提交的数据。连脏读都无法解决,可重复读和幻读更没法解决。
读已提交
读已提交的数据肯定能解决脏读问题,但是对于幻读和不可重复读无法将解决。
首先设置事务隔离级别:
set global transaction isolation level READ COMMITTED;
现在数据库数据如下:
mysql> select * from test_db;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaoming2 |
| 2 | xiaohong |
| 3 | xiaowei |
| 4 | xiaowei1 |
| 5 | xiaoli |
| 6 | xiaoche |
| 8 | xiaoche |
| 10 | xiaoche |
| 12 | xiaocee |
±—±----------+
9 rows in set (0.00 sec)
开启事务 A 将 id=1
的数据改为 “xiaoming3”:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_db set name = ‘xiaoming3’ where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
这里事务 A 未提交,接着开启事务B 做第一次查询:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_db where id = 1;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaoming2 |
±—±----------+
9 rows in set (0.00 sec)
事务B查询还是原始值。
下面提交事务 A:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
接着在事务 B 中再查询一次:
mysql> select * from test_db where id = 1;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaoming3 |
±—±----------+
1 row in set (0.00 sec)
当然这次查到的肯定是人家已提交的数据。这里发生的问题就是不可重复读:即同一个事务内每次读取同一条数据的结果不一样。
可重复读
可重复读隔离级别的含义就是重读每次都一样不会有问题。这就意味着一个事务不会读取到别的事务未提交的修改。但是这里就会有另一个问题:在别的事务提交之前它读到的数据不会发生变化,那么另一个事务如果将结果 a 改为 b,接着又改为了 a,对于当前事务来说直到另一个事务提交之后它再读才会获取到最新结果,但是它并不知道这期间别的事务对数据做了更新,这就是幻读的问题。
首先设置事务隔离级别:
set global transaction isolation level REPEATABLE READ;
现在数据库数据如下:
mysql> select * from test_db;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaoming3 |
| 2 | xiaohong |
| 3 | xiaowei |
| 4 | xiaowei1 |
| 5 | xiaoli |
| 6 | xiaoche |
| 8 | xiaoche |
| 10 | xiaoche |
| 12 | xiaocee |
±—±----------+
9 rows in set (0.00 sec)
开启事务 A 将 id=1
的数据改为 “xiaoming4”:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_db set name = ‘xiaoming3’ where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
这里事务 A 未提交,接着开启事务B 做第一次查询:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_db where id = 1;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaoming3 |
±—±----------+
9 rows in set (0.00 sec)
事务B查询还是原始值。
下面提交事务 A:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
接着在事务 B 中再查询一次:
mysql> select * from test_db where id = 1;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaoming3 |
±—±----------+
1 row in set (0.00 sec)
查询到还是一样的结果,下面提交事务B ,然后再查询:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_db where id = 1;
±—±----------+
| id | name |
±—±----------+
| 1 | xiaoming4 |
±—±----------+
1 row in set (0.00 sec)
提交完之后再查就是 “xiaoming4”。
这也意味着在事务B未提交期间,事务A做任何操作对B来说都是盲视的。
串行化读
串行化读意味着将所有事务变为顺序执行,所以就不存在上述的四种问题,当然这也意味着效率是最低的。
有了隔离级别的概念,那隔离级别又是怎么实现的呢?我们接下来要讲的锁机制就是实现隔离级别的重要手段。
锁的类型
从锁定资源的角度看, MySQL 中的锁分类:
- 表级锁
- 行级锁
- 页面锁
表级锁 的特点是每次都整张表加锁,加锁速度快,但是锁的粒度太大,并发性就低,发生锁冲突的概率大。
表锁的种类主要包含两种:
- 读锁 (共享锁):同一份数据多个读操作同时进行不会互相影响,但是读操作会阻塞写操作。
- 写锁(排他锁):当前写操作没有完成之前会阻塞其他读和写操作。
行级锁 的特点是对一行数据加锁,加锁的开销会大但是锁粒度小发生锁冲突的概率就低并发度提高了。
行锁的种类包含:
- 读锁(S 共享锁):允许一个事务读取某一行,其他事务在读取期间无法修改该行数据但可以读。
- 写锁(X 排他锁):允许当前获得排它锁的事务操作数据,其他事务在操作期间无法更改或者读取。
- 意向排它锁(IX):一个事务给该数据行加排它锁之前,必须先获得 IX 锁。
- 意向共享锁(IS):一个事务给该数据行加共享锁之前必须先获得 IS 锁。
页面锁 因为MySQL 数据文件存储是按照页去划分的,所以这个锁是 MySQL 特有的。开销和加锁时间界于表锁和行锁之间,锁定粒度界于表锁和行锁之间,并发度一般。
在 InnoDB 引擎中默认使用行级锁,我们重点就行级锁的加锁、解锁来做一些说明。
行级锁上锁分为 隐式上锁 和 显式上锁。
隐式上锁是默认的上锁方式,select
不会自动上锁,insert
、update
、delete
都会自动加排它锁。在语句执行完毕会释放。
显式上锁即通过手动的方式给 sql 语句加锁,比如:
共享锁:
select * from tableName lock in share mode;
排他锁:
select * from tableName for update;
行级锁的实现方式
在 InnoDB 中行级锁的具体实现分为三种类型:
- 锁定单个行记录:Record Lock。
- 锁定一个范围,不包含记录本身:Gap Lock。
- 同时锁住一行数据 + 该数据上下浮动的间隙 :next-Key Lock。
接下来我们通过一个示例来测试 InnoDB 中这三种锁的实现。
先创建一个测试表:
CREATE TABLE test_db
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(255) NOT NULL DEFAULT ‘’ COMMENT ‘name’,
PRIMARY KEY (id
),
KEY name_idx
(name
(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT=‘测试表’;
插入两条数据:
还记得我们上面说过 MySQL 是自动提交事务,为了测试锁我们需要关闭自动提交:
set autocommit = 0;
这个设置只在当前连接中生效,记得每开一个连接都要设置一下。
Record Lock 测试
开启一个事务:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_db set name = ‘xiaoming1’ where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看事务状态:
mysql> show engine innodb status;
TRANSACTIONS
Trx id counter 25355
ULT ‘’ COMMENT ‘name’,
PRIMARY KEY (id
),
KEY name_idx
(name
(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT=‘测试表’;
插入两条数据:
[外链图片转存中…(img-nOqO2TWb-1639131500213)]
还记得我们上面说过 MySQL 是自动提交事务,为了测试锁我们需要关闭自动提交:
set autocommit = 0;
这个设置只在当前连接中生效,记得每开一个连接都要设置一下。
Record Lock 测试
开启一个事务:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_db set name = ‘xiaoming1’ where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看事务状态:
mysql> show engine innodb status;
TRANSACTIONS
Trx id counter 25355