6-1 MySQL事务及相关特性
1. 前言
首先非常感谢读到第六章的同学,可能看到这章的目录你会产生一个疑问:前五章内容都是围绕C++程序设计讲述的有关内容,为什么第六章是讲MySQL数据库,而不是操作系统和计算机网络等内容呢?
我认为原因主要有两点:
- 专刊的篇幅有限,且作者的能力和精力有限。
- 为什么选择数据库?首先,我认为数据库原理的掌握是后台开发不可或缺的,应该深入学习MySQL数据库的实现原理、工作机制、产品特性并适动手实践。其次,对于操作系统和计算机网络的一些原理和特性介绍的资料较为全面和丰富,且在专刊的前五章中或多或少的有一些涉及和讲解。但我在这里需要提醒大家,操作系统和计算机网络相关的知识掌握也非常重要,需要同学们参考1-2节的知识图谱,主动去搜寻学习资料并掌握相关内容。
2. 事务
在关系型数据库中,事务通常是多个SQL指令的集合操作。如下图所示:事务的一个完整周期包括:begin-开启事物、commit-提交事务、rollback-回滚事务。
2.1 事务的四个特性
事务必须满足4个条件,也叫事务的4个特性(ACID):
- 1.原子性(Atomicity)
事务被视为不可分割的最小单元,事务的所有操作要么全部提交成功,要么全部失败回滚到事务开始之前。MySQL的数据库管理系统通过回滚日志(undo log)来实现原子性,回滚日志记录着事务所执行的修改操作,若需要回滚则反向执行这些修改操作。 - 2.一致性(Consistency)
数据库在事务执行前后都保持一致性状态,一致性是对数据可见性的约束,保证在一个事务中的多次操作的数据中间状态对其他事务不可见的。这些中间状态,是过渡状态,与事务的开始状态和事务的结束状态是不一致的。 - 3.隔离性(Isolation)
数据库允许多个并发事务同时对数据进行读写和修改,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。 - 4、持久性(Durability)
一旦事务提交,则其所做的修改将会永远保存到数据库中。即使系统发生崩溃,事务执行的结果也不能丢失。
需要强调的是:
(1)原子性和一致性的侧重点不同:原子性关注状态,要么全部成功,要么全部失败,不存在部分成功的状态。而一致性关注数据的可见性,中间状态的数据对外部不可见,只有最初状态和最终状态的数据对外可见。
(2)只有满足一致性,事务的执行结果才是正确的:
- 在无并发的情况下,事务串行执行,隔离性一定能够满足。此时只要能满足原子性,就一定能满足一致性。
- 在并发的情况下,多个事务并行执行,事务不仅要满足原子性,还需要满足隔离性,才能满足一致性。
2.2 事务控制语句
在MySQL数据库的Innodb引擎下,使用以下语句操作和控制事务:
- 1.使用BEGIN,COMMIT,ROLLBACK来开启、提交和回滚事务
BEGIN——开始一个事务
ROLLBACK——事务回滚
COMMIT——事务提交 - 2.SAVEPOINT identifier, 允许在事务中创建一个保存点,在数据库事务处理中实现“子事务”(subtransaction),一个事务中可以有多个SAVEPOINT
- 3.ROLLBACK TO identifier 把事务回滚到保存点;
- 4.RELEASE SAVEPOINT identifier 删除一个事务的保存点
- 5.SELECT @@tx_isolation 查看当前事务的隔离级别
- 6.SET TRANSACTION 用来设置事务的隔离级别,InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE
- 7.SET AUTOCOMMIT=0 禁止自动提交。当AUTOCOMMIT=1时是自动提交的状态,也就是一条SQL语句执行完会默认执行一次COMMIT,也就不受我们自己事务命令的控制。因此,需要SET AUTOCOMMIT=0设置成禁止自动提交。
事务测试:
MySQL [(none)]> CREATE DATABASE TESTDB; // 创建数据库TESTDB Query OK, 1 row affected (0.00 sec) MySQL [(none)]> use TESTDB; // 选择数据库TESTDB Database changed MySQL [TESTDB]> CREATE TABLE transaction_test( pri_id int(5)) engine=innodb; // 创建表transaction_test 存储引擎指定为innodb Query OK, 0 rows affected (0.05 sec) MySQL [TESTDB]> desc transaction_test; // transaction_test表结构为一个pri_id字段,类型为int +--------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------+------+-----+---------+-------+ | pri_id | int(5) | YES | | NULL | | +--------+--------+------+-----+---------+-------+ 1 row in set (0.00 sec) MySQL [TESTDB]> SET AUTOCOMMIT=0; // 禁止事务自动提交 Query OK, 0 rows affected (0.00 sec) MySQL [TESTDB]> select * from transaction_test; // 当前为空表 Empty set (0.00 sec) MySQL [TESTDB]> begin; // 开启事务 Query OK, 0 rows affected (0.00 sec) MySQL [TESTDB]> insert into transaction_test value(0); // 插入元素0 Query OK, 1 row affected (0.00 sec) MySQL [TESTDB]> insert into transaction_test value(1); // 插入元素1 Query OK, 1 row affected (0.00 sec) MySQL [TESTDB]> commit; // 提交事务 Query OK, 0 rows affected (0.00 sec) MySQL [TESTDB]> select * from transaction_test; +--------+ | pri_id | +--------+ | 0 | | 1 | +--------+ 2 rows in set (0.00 sec) MySQL [TESTDB]> begin; // 开启事务 Query OK, 0 rows affected (0.00 sec) MySQL [TESTDB]> insert into transaction_test value(10); // 插入元素10 Query OK, 1 row affected (0.00 sec) MySQL [TESTDB]> rollback; // 回滚事务 Query OK, 0 rows affected (0.00 sec) MySQL [TESTDB]> select * from transaction_test; // 因为回滚所以元素10没有插入 +--------+ | pri_id | +--------+ | 0 | | 1 | +--------+ 2 rows in set (0.00 sec)
3. 隔离级别和并发一致性问题
ACID中的一致性描述的是一个最理想的事务应该怎样的,是一个强一致性状态,如果要做到这点,需要使用排它锁把事务排成一队,即串行化(Serializable)的隔离级别,这样性能就大大降低了。现实是骨感的,所以隔离性设置了不同隔离级别来打破一致性原则,来获取更好的性能。
3.1 读未提交(READ UNCOMMITTED)
读未提交(READ UNCOMMITTED)是低的隔离级别。在读未提交级别下,事务能够读取到其他事务“未提交”的数据,从而引发丢失修改、脏读和不可重复读的问题。
3.1.1 丢失修改
丢失修改指一个事务A的更新操作被另外一个事务B的更新操作替换,从而导致事务A的修改丢失了。
例如:
- 1.商家A将某个商品定价为100元,A开启事务先修改并提交生效;
- 2.随后,商家B也开启事务并对该商品的定价进行修改200元,但未提交事务;
- 3.此时,B尽管未提交事务仍然影响了商品的价格的展示;
3.1.2 脏读
脏读数据指并发执行的事务下,当前事务可以读到另外事务未提交的数据。
例如:牛妹转账给Evila200元作为奖励。
- 首先,牛妹开启事务并修改了Evila的账户余额+200,但牛妹未提交事务;
- 此时Evila开启事务并查看账户发现多了200元,便提交事务结束操作;
- 牛妹经过清算觉得200奖励在上周给过了,于是牛妹回滚了事务;
- 最终,Evila的账户没有增加200元,但Evila以为增加了。
3.2 读已提交(READ COMMITTED)
读已经提交(READ COMMITTED)是高于读未提交的隔离级别。在读未已提交级别下,事务只能够读取到其他事务“提交”的数据,但并不能解决并发事务时引起的不可重复读问题。
3.2.1 不可重复读
不可重复读指在一个事务内多次读取同一个数据得到不同的结果。
例如:
事务A和B并发读写同一份数据,
- 事务A首先读取了数据的值,在A读取后,另一事务B修改了该数据并提交了事务;
- 在事物B提交后,事物A再次读取该数据;
- 由于事务B的修改,事务A的两次读取的数据不一致。
3.3 可重复读(REPEATABLE READ)
可重复读(REPEATABLE READ)是在读已提交级别下隔离性更强的等级。可以避免读已提交级别下产生的事务内读取同一个数据结果不一致问题(不可重复读问题),这是因为在可重复读(REPEATABLE READ)级别下,同一个事务内的查询都是事务开始时刻一致的,即快照查询,因此无论在事务中是否有其他事务对数据进行了修改,都不会影响本事务的查询结果。 可重复读(REPEATABLE READ)也是InnoDB引擎的默认级别。但可重复读级别下仍然具有幻读的并发不一致问题:
3.3.1 幻读
幻读是事务中读取范围数据时,发现前后多次读取到的范围数据数目发生变化,本质上也属于不可重复读。
例如:
- 1.牛妹开启事务,想要查询Evila的消费账单;
- 2.在事务中,牛妹查询发现Evila有一笔10元的消费;
- 3.此后,Evila开启事务,增加了一笔100元的消费,并提交;
- 4.牛妹再次查询账单,发现Evila有两笔消费记录,即同一个事务内本次查询结果和上次查询结果不一致。
3.4 串行化(SERIALIZABLE)
串行化(SERIALIZABLE)是隔离性最强的等级,强制事务串行执行,失去并发性能,造成事务执行效率降低。由于事务是串行执行的,因此对于脏读、不可重复度、幻读的问题都可以避免。
3.5 隔离级别能解决并发一致性问题
表头 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交(READ UNCOMITTED) | × | × | × |
读已提交(READ COMITTED) | √ | × | × |
可重复读(REPEATABLE READ) | √ | √ | × |
4. 多版本并发控制(Multi-Version Concurrency Control,MVCC)
4.1 MVCC的基本思想
多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL的InnoDB存储引擎实现隔离级别的实现机制,用于实现提交读和可重复读这两种隔离级别。多个事务并发读操作没有互斥关系,但是读和写操作存在互斥关系。MVCC利用多版本机制,可以有效的处理读-写冲突,能够做不加锁且非阻塞并发读。
MVCC实现的基本思想:写操作只更新最新的版本快照,而读操作读旧版本快照,从而消除了读和写操作的互斥关系。在提交读和可重复读隔离级别下,事务进行读取操作时,为了解决脏读和不可重复读问题,MVCC规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照,这不算是脏读。
4.2 MVCC的实现机制
参考MySQL官网的介绍:
为了维护数据的多个版本快照,MySQL的Innodb引擎增加了系统版本号的概念,系统版本号是一个递增的数字,每开启一个新的事务,系统版本号就会自动递增。此外,Innodb为每个数据行记录维护了三个隐藏的列+是否删除的标志位:
- 1.DB_TRX_ID:写操作事务ID:事务的修改操作(DELETE、INSERT、UPDATE)会为数据行新增一个版本快照,该字段记录了创建或最后一次修改该记录的事务ID
- 2.DB_ROLL_PTR:回滚指针,指向记录的上一个版本快照;快照存储在Undo日志中,Undo日志通过DB_ROLL_PTR将记录的所有版本快照连接。
- 3.DB_ROW_ID:隐藏的记录自增主键,当由innodb产生聚集索引时,聚集索引会包括这个行ID的值。
- 4.DEL:DELETE被视为一种UPDATE,若记录被DELETE,则DEL标志被置为1。
4.2.1 Undo日志
undo log是采用段(segment)的方式来记录逻辑操作,数据的快照被存储于Undo log的rollback segment(回滚段),每个回滚段中有1024个undo log segment。
Undo log可分为两类:
- 1.insert undo log:事务在insert记录时产生的undo log, 只在当前事务回滚时需要,并且在事务提交后可以被立即丢弃。
- 2.update undo log:可分为delete和update操作,其中delete操作实际上不会直接删除,而是将delete对象打上delete flag,最终的删除操作是purge线程完成的。
update分为两种情况:(1)update的列如果不是主键列,在undo log中直接反向记录是如何update的;(2)update的列如果是主键列,update分两部执行:先删除该行,再插入一行目标行。
举个例子:
- 首先,创建一个测试用的数据表,并设置事务自动提交(set autocommit = 1), 表的结构如下:
MySQL [test]> desc Persons; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | Id_P | int(11) | YES | | NULL | | | LastName | varchar(255) | YES | | NULL | | | FirstName | varchar(255) | YES | | NULL | | | Address | varchar(255) | YES | | NULL | | | City | varchar(255) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
- 2.执行Insert语句插入一条记录(自动提交事务):
INSERT INTO Persons VALUES(1,"Li","Evila","Tencent Building","ShenZhen");
此时DB_ROW_ID是1,DB_TRX_ID和DB_ROLL_PTR假设为NULL,DEL为0。
- 3.执行Update语句修改FirstName(自动提交事务):
UPDATE Persons SET FirstName='牛妹' WHERE Id_P=1;
,在UPDATE执行时,首先将原数据拷贝到undo log中作为旧记录;然后执行修改操作;最后递增DB_TRX_ID,DB_ROLL_PTR赋值为undo log中拷贝的副本地址。
以此类推,当事务中执行了update操作后,undo log会记录数据修改前的旧版本快照,并以链表的方式关联,链首节点是上一个版本的旧记录,链尾就是最早的旧记录。
4.2.2 ReadView——读视图
ReadView是事务在进行快照读时产生的当前时刻活跃的事务视图,由于DB_TRX_ID是单调递增的,因此DB_TRX_ID越大表明事务开启的时间越新。ReadView用来判断当前事务能够看到undo log中哪个版本的数据,即判断快照对与当前事务的可见性。
例如,若有3个事务并发读写数据,且3个事务均为提交属于活跃事务;此时,系统的ReadView为如下情况:
当前活跃的事务对于数据多版本的可见性,遵循如下算法:
- 1.将活跃事务想要查询或修改数据的最新记录(链表首部节点)中的DB_TRX_ID取出,与ReadView中活跃事务的ID去对比;
- 2.若数据快照的DB_TRX_ID < TRX_ID_MIN,表示该数据行快照均是在当前ReadView中所有未提交事务之前进行更改的,则可以使用
- 3.若数据快照的DB_TRX_ID > TRX_ID_MAX,表示该数据行快照均是在ReadView中事务启动之后被更改的,因此不可使用
- 4.若TRX_ID_MIN <= DB_TRX_ID <= TRX_ID_MAX,需要根据隔离级别再进行判断:提交读:如果 DB_TRX_ID 在 TRX_IDs 列表中,表示该数据行快照对应的事务还未提交,则该快照不可使用。否则表示已经提交,可以使用。可重复读:都不可以使用。
若链表节点不符合可见性,那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较,即遍历链表的DB_TRX_ID(从链首到链尾,即从最近的一次修改查起),直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新版本。
5.Next-Key Locks
5.1 快照读与当前读
1.当前读
当前读是一种对操作所扫描的数据记录索引上加锁的操作,是悲观锁(访问数据之前先锁住数据)的实现。select ... lock in share mode
、select ... for update
、update
、insert
、delete
操作都属于当前读的动作,它们读取的是记录的最新版本,并且在运行时对数据记录进行加锁,以保证读取时其他并发事务不能并发的修改当前记录。2.快照读
不加锁的select操作就是快照读,在读已提交和可重复读隔离级别下依靠MVCC机制实现,串行隔离级别下的快照读会退化成当前读。
5.2 再谈幻读
幻读指的是在一个事务A中执行了一个当前读操作,另外一个事务B在事务A的扫描区间内insert了一条记录并提交事务,这时事务A再执行当前读操作时,出现了幻行。比如在事务A中执行快照读select * from test where id<10
结果集为(1,2,3),这时在B中对test表插入了一条记录4,这时在A中重新查询结果集就是(1,2,3,4),结果和事务A在第一次查询出来的结果集不一致。
因此在可重复读(REPEATABLE READ)隔离级别下,MVCC无法解决幻读问题。
为此,Innodb使用MVCC + Next-Key Locks的解决方案:
- 1.行锁 (Record locks)
锁定一个记录上的索引,而不是记录本身。如果表没有设置索引,InnoDB会自动在主键上创建隐藏的聚簇索引。 - 2.间隙锁 (Gap locks)
锁定索引之间的间隙,但是不包含索引本身。 - 3.Next-Key Locks是行锁+间隙锁的结合,不仅锁定一个记录上的索引,也锁定索引之间的间隙。
例如:某个数字整形列的索引包含:10, 11, 13, and 20,那么就需要锁定以下区间:
(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)
因此,Next-Key Locks可以防止间隙内有新数据被插入,防止已存在的数据,更新成间隙内的数据。
举例:一个数据表的具有上文所展示的索引,假设索引所在的列名为id。
- 事务1执行:
select * from table_name where id = 15 for update;
- 事务2执行:
insert into table_name values(14); 阻塞
insert into table_name values(16); 阻塞
insert into table_name values(22); 成功
insert into table_name values(12); 成功
在上例中,事务1执行当前读,next-key lock将id=15的记录加行锁,且根据间隙锁的性质,15向左取最靠近的索引值是13,向右取最靠近的索引值是20,因此会话1的间隙锁范围是(13,15),(15,20)。