数据库事务的四个隔离级别及MySQL实现
数据库事务的四个隔离级别及MySQL实现
1、事务的定义：
数据库事务(transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。
2、事务的特性（ACID特性）：
1）原子性（Atomicity）
一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2）一致性（Consistency）
事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。
3）隔离性（Isolation）
指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。
4）持久性（Durability）
指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。
3、事务的隔离级别及并发事务中存在的问题：
隔离级别：读未提交(Read Uncommitted)：（可能发生脏读）如果一个事务已经开始写操作，那么其他事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现，即事务需要对某些数据进行修改必须对这些数据加 X 锁，读数据不需要加 S 锁。
脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。
例：
表：
| id | name | class_id |
| 1 | 范统 | 1 |
| 事务1 | 事务2 |
| begin; | begin; |
| set session transaction isolation level READ UNCOMMITTED;//修改隔离级别 | set session transaction isolation level READ UNCOMMITTED;//修改隔离级别 |
| select class_id from t_isolation_level where id = 1;//结果为1 | |
| update t_isolation_level set class_id=99 where id=1; | |
| select class_id from t_isolation_level where id = 1;//结果为99 | |
| commit; | |
| rollback; |
在例子中，事务2修改了一行，但是没有提交，事务1读了这个没有提交的数据。现在如果事务2回滚了刚才的修改或者做了另外的修改的话，事务1中查到的数据就是不正确的了,所以这条数据就是脏读。
隔离级别：读已提交(Read Committed)：解决了脏读问题，可能发生不可重复读。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。这可以通过“瞬间共享读锁”和“排他写锁”实现， 即事务需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成后立刻释放 S 锁，不用等到事务结束。
不可重复读：不可重复读是指在对于数据库中的某条数据，一个事务范围内多次查询返回不同的数据值(这里不同是指某一条或多条数据的内容前后不一致，但数据条数相同)，这是由于在查询间隔，该事务需要用到的数据被另一个事务修改并提交了。
例：
表：
| id | name | class_id |
| 1 | 范统 | 1 |
事务：
| 事务1 | 事务2 |
| begin; | begin; |
| set session transaction isolation level READ COMMITTED;//修改隔离级别 | set session transaction isolation level READ COMMITTED;//修改隔离级别 |
| select class_id from t_isolation_level where id = 1;//结果为1 | |
| update t_isolation_level set balance=99 where id=1; | |
| commit; | |
| select class_id from t_isolation_level where id = 1;//结果为99 | |
| commit; |
在例子中，事务2提交成功，因此他对id为1的行的修改就对其他事务可见了。导致了事务1在此前读的class_id=1,第二次读的class_id=99,两次结果不一致,这就是不可重复读。
隔离级别：可重复读取(Repeatable Read)：解决了不可重复读和脏读，可能出现幻读。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。Mysql默认使用该隔离级别。这可以通过“共享读锁”和“排他写锁”实现，即事务需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成并不立刻释放 S 锁，而是等到事务结束后再释放。（可能发生幻读）
幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。
例：
表：
| id | name | class_id |
| 1 | 范统 | 99 |
事务：
| 事务1 | 事务2 | ||||
| begin; | begin; | ||||
| set session transaction isolation level READ COMMITTED;//修改隔离级别 | set session transaction isolation level READ COMMITTED;//修改隔离级别 | ||||
| select * from t_isolation_level where id = 99; | |||||
| id | name | class_id | |||
| 1 | 范统 | 99 | |||
| insert into t_isolation_level VALUES(2,'菜镶',99); | |||||
| commit; | |||||
| select * from t_isolation_level where id = 99; | |||||
| id | name | class_id | |||
| 1 | 范统 | 99 | |||
| 2 | 菜镶 | 99 | |||
| commit; | |||||
在例子中，事务1执行了两遍同样的查询语句,第二遍比第一遍多出了一条数据,这就是幻读。
（定义是这样，在mysql实际操作中却不一样，具体见下文）
隔离级别：串行化(Serializable)：解决了幻读。提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
隔离级别与问题对比表：
| 脏读 | 不可重复读 | 幻读 | |
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable read | × | × | √ |
| Serializable | × | × | × |
√: 可能出现 ×: 不会出现
排他锁，共享锁：
排它锁（Exclusive），又称为X 锁，写锁。
共享锁（Shared），又称为S 锁，读锁。
读写锁之间有以下的关系：
一个事务对数据对象O加了 S 锁，可以对 O进行读取操作，但是不能进行更新操作。加锁期间其它事务能对O 加 S 锁，但是不能加 X 锁。
一个事务对数据对象 O 加了 X 锁，就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。
4、Mysql隔离级别实现
Mysql默认隔离级别为：可重复读（Repeatable read）
事务：
| 事务1 | 事务2 | 事务3 | ||||||
| begin; | begin; | begin; | ||||||
| select @@transaction_isolation;//查看当前隔离级别 | select @@transaction_isolation;//查看当前隔离级别 | select @@transaction_isolation;//查看当前隔离级别 | ||||||
| SELECT * FROM t_isolation_level; | ||||||||
| id | name | class_id | ||||||
| 1 | 范统 | 99 | ||||||
| 2 | 菜镶 | 99 | ||||||
| UPDATE t_isolation_level set class_id=100 where id=2; | ||||||||
| commit; | ||||||||
| insert into t_isolation_level VALUES(3,'米芾',100); | ||||||||
| commit; | ||||||||
| SELECT * FROM t_isolation_level; | ||||||||
| id | name | class_id | ||||||
| 1 | 范统 | 99 | ||||||
| 2 | 菜镶 | 99 | ||||||
| commit; | ||||||||
事务A既没有读到事务B更新的数据,也没有读到事务C添加的数据,所以在这个场景下,它既防住了不可重复读,也防住了幻读，这是怎么做到的呢?
在对象上加锁,是一种悲观锁机制,有人认为可重复读会对读的行加锁,导致他事务修改不了这条数据,直到事务结束,但是这种方案只能锁住数据行,如果有新的数据进来,是阻止不了的,所以会产生幻读.
悲观锁#
指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。
乐观锁#
乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。
MySQL、ORACLE、PostgreSQL等都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免不可重复读和幻读,MVCC的实现没有固定的规范，每个数据库都会有不同的实现方式，这里讨论的是InnoDB的MVCC。
MVCC(多版本并发控制)
在InnoDB中，会在每行数据后添加额外的隐藏的值来实现MVCC，这些值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 在可重读Repeatable reads事务隔离级别下：
SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
INSERT时，保存当前事务版本号为行的创建版本号
DELETE时，保存当前事务版本号为行的删除版本号
UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行
MCVV这种读取历史数据的方式称为快照读(snapshot read),而读取数据库当前版本数据的方式,叫当前读(current read).
快照读
只用使用select就是快照读,这样可以减少加锁所带来的开销.
select * from table …
当前读
对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录，即使是别的事务提交的数据也可以查询到。以下第一个语句需要加共享锁，其它都需要加排它锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert…
update…
delete…
例1：
表：
| id | name | class_id |
| 1 | 范统 | 1 |
事务：
| 事务1 | 事务2 | ||||
| begin; | begin; | ||||
| set session transaction isolation level READ COMMITTED;//修改隔离级别 | set session transaction isolation level READ COMMITTED;//修改隔离级别 | ||||
| select * from t_isolation_level where class_id= 1; | |||||
| id | name | class_id | |||
| 1 | 范统 | 1 | |||
| update t_isolation_level set name='测试' where class_id=1; | |||||
| insert into t_isolation_level VALUES(2,'范统',1); | |||||
| commit; | |||||
| select class_id from t_isolation_level where id = 1;//结果为99 | |||||
| id | name | class_id | |||
| 1 | 测试 | 1 | |||
| 2 | 范统 | 1 | |||
| commit; | |||||
在读提交的隔离级别中,事务1修改了所有class_id=1的数据,当时当事务2 insert后,事务A莫名奇妙地多了一行class_id=1的数据,而且没有被之前的update所修改,产生了读提交下的的幻读.
例2：
表：
| id | name | class_id |
| 1 | 范统 | 1 |
事务：
| 事务1 | 事务2 | ||||
| begin; | begin; | ||||
| set session transaction isolation level REPEATABLE READ;//修改隔离级别 | set session transaction isolation level REPEATABLE READ;//修改隔离级别 | ||||
| select * from t_isolation_level where class_id= 1; | |||||
| id | name | class_id | |||
| 1 | 范统 | 1 | |||
| update t_isolation_level set name='测试' where class_id=1; | |||||
| insert into t_isolation_level VALUES(2,'范统',1); //一直等待直到事务1结束 | |||||
| commit; | |||||
| select class_id from t_isolation_level where id = 1;//结果为99 | |||||
| id | name | class_id | |||
| 1 | 测试 | 1 | |||
| commit; | |||||
在可重复度的隔离级别下,情况就完全不同了.事务1在update后,对该数据加锁,事务B无法插入新的数据,这样事务A在update前后数据保持一致,避免了幻读.可以明确的是,update锁的肯定不只是已查询到的几条数据,因为这样无法阻止insert,也不是锁住了整张表，这里的锁,是通过next-key锁实现的.
Next-Key锁
在Users这张表里面,class_id是个非聚簇索引,数据库会通过B+树维护一个非聚簇索引与主键的关系,简单来说,我们先通过class_id=1找到这个索引所对应所有节点,这些节点存储着对应数据的主键信息,即id=1,我们再通过主键id=1找到我们要的数据,这个过程称为回表.
（不懂数据库索引的底层原理？前往学习: https://www.cnblogs.com/sujing/p/11110292.html）
引用上面文章中作者画的B+树来解释Next-key.
假设我们上面用到的表需要对Name建立非聚簇索引,是怎么实现的呢？我们看下图：
B+树的特点是所有数据都存储在叶子节点上,以非聚簇索引的秦寿生为例,在秦寿生的右叶子节点存储着所有秦寿生对应的Id,即图中的34,在我们对这条数据做了当前读后,就会对这条数据加行锁,对于行锁很好理解,能够防止其他事务对其进行update或delete,但为什么要加GAP锁呢?
还是那句话,B+树的所有数据存储在叶子节点上,当有一个新的叫秦寿生的数据进来,一定是排在在这条id=34的数据前面或者后面的,我们如果对前后这个范围进行加锁了,那当然新的秦寿生就插不进来了.
那如果有一个新的范统要插进行呢? 因为范统的前后并没有被锁住,是能成功插入的,这样就极大地提高了数据库的并发能力.
上文中说了可重复读能防不可重复读,还能防幻读,它能防住所有的幻读吗
例3：
表：
| id | name | class_id |
| 1 | 范统 | 1 |
| 2 | 饭桶 | 1 |
事务：
| 事务1 | 事务2 | ||||
| begin; | begin; | ||||
| set session transaction isolation level REPEATABLE READ;//修改隔离级别 | set session transaction isolation level REPEATABLE READ;//修改隔离级别 | ||||
| select * from t_isolation_level where class_id= 1; | |||||
| id | name | class_id | |||
| 1 | 范统 | 1 | |||
| 2 | 饭桶 | 1 | |||
| insert into t_isolation_level VALUES(3,'范统',1); | |||||
| commit; | |||||
| select class_id from t_isolation_level where id = 1; | |||||
| id | name | class_id | |||
| 1 | 范统 | 1 | |||
| 2 | 饭桶 | 1 | |||
| update t_isolation_level set name='测试' where class_id=1; | |||||
| select * from t_isolation_level where class_id= 1; | |||||
| id | name | class_id | |||
| 1 | 测试 | 1 | |||
| 2 | 测试 | 1 | |||
| 3 | 测试 | 1 | |||
| commit; | |||||
1.a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意操作）
2.a事务再select出来的结果在MVCC下还和第一次select一样，
3.接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），
4.a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了。
Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读, 所以这个场景下,算出现幻读了。
参考：