MySQL事务
1.事务特性介绍
-
特性
- Atomicity（原子性）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
- Consistency（一致性）：数据库总是从一个一致性状态转换到另一个一致状态。
- Isolation（隔离性）：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。注意这里的“通常来说”，后面的事务隔离级级别会说到。
- Durability（持久性）：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。
-
为了更好地理解事务的特性，如下创建账户表：
create table account ( id int primary key auto_increment, user varchar(255) not null, balance bigint(20) not null ); insert account values(NULL,'张三',1000); insert account values(NULL,'李四',1000); +----+--------+---------+ | id | user | balance | +----+--------+---------+ | 1 | 张三 | 1000 | | 2 | 李四 | 1000 | +----+--------+---------+ START TRANSACTION; UPDATE account SET balance = balance - 200.00 WHERE user = '张三'; UPDATE account SET balance = balance + 200.00 WHERE user = '李四'; COMMIT; +----+--------+---------+ | id | user | balance | +----+--------+---------+ | 1 | 张三 | 800 | | 2 | 李四 | 1200 | +----+--------+---------+原子性：要么完全提交（张三的余额减少200，李四的余额增加200），要么完全回滚（两个表的余额都不发生变化）
一致性：这个例子的一致性体现在 200 不会因为数据库系统运行到第4行之前时崩溃而不翼而飞，因为事务还没有提交。
隔离性：允许在一个事务中的操作语句会与其他事务的语句隔离开，比如事务A运行到第3行之后，第4行之前，此时事务B去查询张三余额时，它仍然能够看到在事务A中被减去的200元（账户钱不变），因为事务A和B是彼此隔离的。在事务A提交之前，事务B观察不到数据的改变，如下。
-- 事务A START TRANSACTION; UPDATE account SET balance = balance - 200.00 WHERE user = '张三'; UPDATE account SET balance = balance + 200.00 WHERE user = '李四'; select * from account; +----+--------+---------+ | id | user | balance | +----+--------+---------+ | 1 | 张三 | 800 | | 2 | 李四 | 1200 | +----+--------+---------+ -- 事务B START TRANSACTION; select * from account; +----+--------+---------+ | id | user | balance | +----+--------+---------+ | 1 | 张三 | 1000 | | 2 | 李四 | 1000 | +----+--------+---------+持久性：一旦事务提交，其数据会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失，如下当事务A提交事务，则数据永久保存到数据库中。
-- 事务A COMMIT; select * from account; +--------+---------+ | user | balance | +--------+---------+ | 张三 | 800 | | 李四 | 1200 | +--------+---------+ -- 事务B START TRANSACTION; select * from account; +--------+---------+ | user | balance | +--------+---------+ | 张三 | 800 | | 李四 | 1200 | +--------+---------+
2.事务是如何实现的
-
事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL中支持事务的存储引擎有innoDB和NDB。
-
innoDB是MySQL默认的存储引擎（MySQL5.5版本以上），默认的隔离级别是可重复读（Repeatable Read），并且在可重复读的隔离级别下更进一步，通过多版本并发控制（MVCC，Multiversion Concurrency Control ）解决不可重复读问题，加上间隙锁（也就是并发控制）解决幻读问题。因此innoDB的RR隔离级别其实实现了串行化级别的效果，而且保留了比较好的并发性能。
-
事务的原子性、一致性和持久性是通过事务日志实现。
-
redo log
在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，日志预写。当事务提交之后，在Buffer中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。
在系统启动的时候，就已经为redo log分配了一块连续的存储空间,以顺序追加的方式记录Redo Log,通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。如下一个简单示例：
记录1：<trx1, insert…>
记录2：<trx2, delete…>
记录3：<trx3, update…>
记录4：<trx1, update…>
记录5：<trx3, insert…>
-
undo log
undo log主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。
-
-
以下是undo+redo事务的简化过程
假设有2个数值，分别为A和B,值为1，2
-
start transaction;
-
记录 A=1到undo log;
-
update A = 3；
-
记录 A=3 到redo log；
-
记录 B=2 到undo log；
-
update B = 4；
-
记录B = 4 到redo log；
-
将redo log刷新到磁盘
-
commit；
在1-8的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响。如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化。若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘。
所以，redo log其实保障的是事务的持久性和一致性，而undo log则保障了事务的原子性。
-
3.事务的隔离级别
-
并发事务带来的问题
- 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题 －－最后的更新覆盖了由其他事务所做的更新。
- 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前， 这条记录的数据就处于不一致状态； 这时， 另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
-
不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读” 。
-
幻读 （Phantom Reads）： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读” 。
-
幻读和不可重复读的区别：
- 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
- 幻读的重点在于新增或者删除：在同一事务中，同样的条件，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）
-
并发事务处理带来的问题的解决办法：
- 更新丢失通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
- 脏读 、不可重复读和幻读 ，其实都是数据库读一致性问题，须由数据库提供一定的事务隔离机制来解决：
-
- 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
- 另一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC ），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。
-
隔离级别
第1级别：Read Uncommitted(读取未提交内容)
- 所有事务都可以看到其他未提交事务的执行结果
- 本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少
- 该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据
第2级别：Read Committed(读取提交内容)
-
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）
-
它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变
-
这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。导致这种情况的原因可能有：
-
- 有一个交叉的事务有新的commit，导致了数据的改变;
- 一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit
第3级别：Repeatable Read(可重读)
- 这是MySQLInnoBD引擎的默认事务隔离级别。
- 它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
- 此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
- InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决幻读问题；InnoDB还通过间隙锁解决幻读问题
第4级别：Serializable(可串行化)
- 这是最高的隔离级别
- 它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。MySQL锁总结
- 在这个级别，可能导致大量的超时现象和锁竞争。
-
小结
Isolation Level 脏读（Dirty Read） 不可重复读（Non Repeatable Read） 幻读（Phantom Read） Read Uncommitted Yes Yes Yes Read Committed - Yes Yes Repeatable Read - - Yes Serializable - - - 1.事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）时，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
2.隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大
4.MVCC多版本并发控制
MVCC是如何工作的：
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现。这两个列一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动新增。事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行记录的版本号进行比较。
1.为了理解MVCC的工作原理，如下创建账户表：
drop table account;
create table account (
id int primary key auto_increment,
user varchar(255) not null,
balance bigint(20) not null
);
2.REPEATABLE READ（可重读）隔离级别下MVCC如何工作：
-
INSERT
假设系统的版本号从1开始，对应在数据中的表如下(后面两列是隐藏列,我们通过查询语句并看不到)
第一个事务ID为1；
start transaction; insert into account values(NULL,'张三', 1000) ; insert into account values(NULL,'李四', 1000); insert into account values(NULL,'王五', 1000); commit;这时表中的数据如下:
id name balance 创建时间(事务ID) 删除时间(事务ID) 1 张三 1000 1 undefined 2 李四 1000 1 undefined 3 王五 1000 1 undefined -
SELECT
InnoDB会根据以下条件检查每一行记录：
- InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行要么是在开始事务之前已经存在要么是事务自身插入或者修改过的
- 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除
只有符合上述两个条件的才会被查询出来。
-
DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
-- 第二个事务ID为2 start transaction; select * from account; //(1) select * from account; //(2) commit;假设1
假设在执行这个事务ID为2的过程中,刚执行到第一步(1),这时,有另一个事务ID为3往这个表里插入了一条数据;
start transaction; insert into account values('赵六', 1000); commit;第三个事务ID为3;
这时表中的数据如下:
id name balance 创建时间(事务ID) 删除时间(事务ID) 1 张三 1000 1 undefined 2 李四 1000 1 undefined 3 王五 1000 1 undefined 4 赵六 1000 3 undefined 然后接着执行事务2中的第二步(2),由于id=4的数据的创建时间(事务ID为3),执行当前事务的ID为2,而InnoDB只会查找事务ID小于等于当前事务ID的数据行,所以id=4的数据行并不会在执行事务2中的第二步(2)被检索出来,在事务2中的两条select 语句检索出来的数据都只会下表:
id name balance 创建时间(事务ID) 删除时间(事务ID) 1 张三 1000 1 undefined 2 李四 1000 1 undefined 3 王五 1000 1 undefined 假设2
假设在执行这个事务ID为2的过程中,刚执行到第一步(1)，另一个事务执行完事务3，紧接着又执行了事务4;
第四个事务:start transaction; delete from account where id = 1; commit;此时数据库中的表如下:
id name balance 创建时间(事务ID) 删除时间(事务ID) 1 张三 1000 1 4 2 李四 1000 1 undefined 3 王五 1000 1 undefined 4 赵六 1000 3 undefined 接着执行事务ID为2的事务(2),根据SELECT 检索条件可以知道,它会检索创建时间(创建事务的ID)小于当前事务ID的行和删除时间(删除事务的ID)大于当前事务的行,而id=4的行上面已经说过,而id=1的行由于删除时间(删除事务的ID)大于当前事务的ID,所以事务2的第二步(2)select * from account也会把id=1的数据检索出来.所以,事务2中的两条select 语句检索出来的数据都如下:
id name balance 创建时间(事务ID) 删除时间(事务ID) 1 张三 1000 1 4 2 李四 1000 1 undefined 3 王五 1000 1 undefined -
UPDATE
InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识
假设3
假设在执行完事务2的(1)后又执行,其它用户执行了事务3,4,这时，又有一个用户对这张表执行了UPDATE操作:
第5个事务:
start transaction;update account set balance='2000' where id=2;commit;根据update的更新原则:会生成新的一行,并在原来要修改的列的删除时间列上添加本事务ID,得到表如下:
id name balance 创建时间(事务ID) 删除时间(事务ID) 1 张三 1000 1 4 2 李四 1000 1 5 3 王五 1000 1 undefined 4 赵六 1000 3 undefined 2 李四 2000 5 undefined 继续执行事务2的(2),根据select 语句的检索条件,得到下表:
id name balance 创建时间(事务ID) 删除时间(事务ID) 1 张三 1000 1 4 2 李四 1000 1 5 3 王五 1000 1 undefined
3.小结
- 保存这两个版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且能保证只会读取到复合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。
- MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。
- 可以认为MVCC是行级锁一个变种，但是他很多情况下避免了加锁操作，开销更低。虽然不同数据库的实现机制有所不同，但大都实现了非阻塞的读操作（读不用加锁，且能避免出现不可重复读和幻读），写操作也只锁定必要的行（写必须加锁，否则不同事务并发写会导致数据不一致）。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
