MySQL/MariaDB中的事务和事务隔离级别
achiverhai 2019-12-07
官方手册：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html
1.事务特性
事务具有ACID特性：原子性(A,atomicity)、一致性(C,consistency)、隔离性(I,isolation)、持久性(D,durabulity)。
- 原子性：事务内的所有操作要么都执行，要么都不执行。
- 一致性：事务开始和结束前后，数据都满足数据一致性约束，而不是经过事务控制之后数据变得不满足条件或业务规则。
- 隔离性：事务之间不能互影响，它们必须完全的各行其道，互不可见。
- 持久性：事务完成后，该事务内涉及的数据必须持久性的写入磁盘保证其持久性。当然，这是从事务的角度来考虑的的持久性，从操作系统故障或硬件故障来说，这是不一定的。
2.事务分类
- 扁平事务
- 带保存点的扁平事务
- 链事务
- 嵌套事务
- 分布式事务
2.1 扁平事务
即最常见的事务。由begin开始，commit或rollback结束，中间的所有操作要么都回滚要么都提交。扁平事务在生产环境中占绝大多数使用情况。因此每一种数据库产品都支持扁平事务。
扁平事务的缺点在于无法回滚或提交一部分，只能全部回滚或全部提交，所以就有了"带有保存点"的扁平事务。
2.2 带有保存点的扁平事务
通过在事务内部的某个位置使用savepoint，将来可以在事务中回滚到此位置。
MariaDB/MySQL中设置保存点的命令为:
savepoint [savepoint_name]
回滚到指定保存点的命令为：
rollback to savepoint_name
删除一个保存点的命令为：
release savepoint savepoint_name
实际上，扁平事务也是有保存点的，只不过它只有一个隐式的保存点，且自动建立在事务开始的位置，因此扁平事务只能回滚到事务开始处。
2.3 链式事务
链式事务是保存点扁平事务的变种。它在一个事务提交的时候自动隐式的将上下文传给下一个事务，也就是说一个事务的提交和下一个事务的开始是原子性的，下一个事务可以看到上一个事务的处理结果。通俗地说，就是事务的提交和事务的开始是链接式下去的。
这样的事务类型，在提交事务的时候，会释放要提交事务内所有的锁和要提交事务内所有的保存点。因此链式事务只能回滚到当前所在事务的保存点，而不能回滚到已提交的事务中的保存点。
2.4 嵌套事务
嵌套事务由一个顶层事务控制所有的子事务。子事务的提交完成后不会真的提交，而是等到顶层事务提交才真正的提交。
关于嵌套事务的机制，主要有以下3个结论：
- 回滚内部事务的同时会回滚到外部事务的起始点。
- 事务提交时从内向外依次提交。
- 回滚外部事务的同时会回滚所有事务，包括已提交的内部事务。因为只提交内部事务时没有真的提交。
不管怎么样，最好少用嵌套事务。且MariaDB/MySQL不原生态支持嵌套事务(SQL Server支持)。
2.5 分布式事务
将多个服务器上的事务(节点)组合形成一个遵循事务特性(acid)的分布式事务。
例如在工行atm机转账到建行用户。工行atm机所在数据库是一个事务节点A，建行数据库是一个事务节点B，仅靠工行atm机是无法完成转账工作的，因为它控制不了建行的事务。所以它们组成一个分布式事务：
- 1.atm机发出转账口令。
- 2.atm机从工行用户减少N元。
- 3.在建行用户增加N元。
- 4.在atm机上返回转账成功或失败。
上面涉及了两个事务节点，这些事务节点之间的事务必须同时具有acid属性，要么所有的事务都成功，要么所有的事务都失败，不能只成功atm机的事务，而建行的事务失败。
MariaDB/MySQL的分布式事务使用两段式提交协议(2-phase commit,2PC)。最重要的是，MySQL 5.7.7之前，MySQL对分布式事务的支持一直都不完善(第一阶段提交后不会写binlog，导致宕机丢失日志)，这个问题持续时间长达数十年，直到MySQL 5.7.7，才完美支持分布式事务。相关内容可参考网上一篇文章：https://www.linuxidc.com/Linux/2016-02/128053.htm。遗憾的是，MariaDB至今(MariaDB 10.3.6)都没有解决这个问题。
3.事务控制语句
begin 和 start transaction表示显式开启一个事务。它们之间并没有什么区别，但是在存储过程中，begin会被识别成begin...end的语句块，所以存储过程只能使用start transaction来显式开启一个事务。commit 和 commit work用于提交一个事务。rollback 和 rollback work用于回滚一个事务。savepoint identifier表示在事务中创建一个保存点。一个事务中允许存在多个保存点。release savepoint identifier表示删除一个保存点。当要删除的保存点不存在的时候会抛出异常。rollback to savepoint表示回滚到指定的保存点，回滚到保存点后，该保存点之后的所有操纵都被回滚。注意，rollback to不会结束事务，只是回到某一个保存点的状态。set transaction用来设置事务的隔离级别。可设置的隔离级别有read uncommitted/read committed/repeatable read/serializable。
commit与commit work以及rollback与rollback work作用是一样的。但是他们的作用却和变量completion_type的值有关。
例如将completion_type设置为1，进行测试。
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> set completion_type=1; mysql> begin; mysql> insert into ttt values(1000); mysql> commit work; mysql> insert into ttt values(2000); mysql> rollback; mysql> select * from ttt where id>=1000; +------+ | id | +------+ | 1000 | +------+ 1 row in set (0.00 sec)
begin开始事务后，插入了值为1000的记录，commit work了一次，然后再插入了值为2000的记录后rollback，查询结果结果中只显示了1000，而没有2000，因为commit work提交后自动又开启了一个事务，使用rollback会回滚该事务。
将completion_type设置为2，进行测试。
1 2 3 4 mysql> set completion_type=2; mysql> begin; mysql> insert into ttt select 1000; mysql> commit;
提交后，再查询或者进行其他操作，结果提示已经和MariaDB/MySQL服务器断开连接了。
1 2 3 mysql> select * from ttt; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect...
4.显式事务的次数统计
通过全局状态变量com_commit和com_rollback可以查看当前已经显式提交和显式回滚事务的次数。还可以看到回滚到保存点的次数。
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> show global status like "%com_commit%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_commit | 14 | +---------------+-------+ mysql> show global status like "%com_rollback%"; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | Com_rollback | 24 | | Com_rollback_to_savepoint | 0 | +---------------------------+-------+
5.一致性非锁定读(快照查询)
在innodb存储引擎中，存在一种数据查询方式：快照查询。因为查询的是快照数据，所以查询时不申请共享锁。
当进行一致性非锁定读查询的时候，查询操作不会去等待记录上的独占锁释放，而是直接去读取快照数据。快照数据是通过undo段来实现的，因此它基本不会产生开销。显然，通过这种方式，可以极大的提高读并发性。
快照数据其实是行版本数据，一个行记录可能会存在多个行版本，并发时这种读取行版本的方式称为多版本并发控制(MVCC)。在隔离级别为read committed和repeatable read时，采取的查询方式就是一致性非锁定读方式。但是，不同的隔离级别下，读取行版本的方式是不一样的。在后面介绍对应的隔离级别时会作出说明。
下面是在innodb默认的隔离级别是repeatable read下的实验，该隔离级别下，事务总是在开启的时候获取最新的行版本，并一直持有该版本直到事务结束。更多的"一致性非锁定读"见后文说明read committed和repeatable read部分。
当前示例表ttt的记录如下：
1 2 3 4 5 6 7 mysql> select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
在会话1执行：
1 2 mysql> begin; mysql> update ttt set id=100 where id=1
在会话2中执行：
1 2 3 4 5 6 7 8 mysql> begin; mysql> select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
查询的结果和预期的一样，来自开启事务前最新提交的行版本数据。
回到会话1提交事务：
mysql> commit;
再回到会话2中查询：
1 2 3 4 5 6 7 mysql> select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
再次去会话1更新该记录：
1 2 3 mysql> begin; mysql> update ttt set id=1000 where id=100; mysql> commit;
再回到会话2执行查询：
1 2 3 4 5 6 7 mysql> select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
这就是repeatable read隔离级别下的一致性非锁定读的特性。
当然，MySQL也支持一致性锁定读的方式。
6.一致性锁定读
在隔离级别为read committed和repeatable read时，采取的查询方式就是一致性非锁定读方式。但是在某些情况下，需要人为的对读操作进行加锁。MySQL中对这种方式的支持是通过在select语句后加上lock in share mode或者for update。
select ... from ... where ... lock in share mode;select ...from ... where ... for update;
使用lock in share mode会对select语句要查询的记录加上一个共享锁(S)，使用for update语句会对select语句要查询的记录加上独占锁(X)。
另外，对于一致性非锁定读操作，即使要查询的记录已经被for update加上了独占锁，也一样可以读取，就和纯粹的update加的锁一样，只不过此时读取的是快照数据而已。
7.事务隔离级别
SQL标准定义了4中隔离级别：read uncommitted、read committed、repeatable read、serializable。
MariaDB/MySQL也支持这4种隔离级别。但是要注意的是，MySQL中实现的隔离级别和SQL Server实现的隔离级别在同级别上有些差别。在后面有必要说明地方会给出它们的差异之处。
MariaDB/MySQL中默认的隔离级别是repeatable read，SQL Server和oracle的默认隔离级别都是read committed。
事务特性(ACID)中的隔离性(I,isolation)就是隔离级别，它通过锁来实现。也就是说，设置不同的隔离级别，其本质只是控制不同的锁行为。例如操作是否申请锁，什么时候申请锁，申请的锁是立刻释放还是持久持有直到事务结束才释放等。
7.1 设置和查看事务隔离级别
隔离级别是基于会话设置的，当然也可以基于全局进行设置，设置为全局时，不会影响当前会话的级别。设置的方法是：
1
2
3
set [global | session] transaction isolation level {type}
type:
read uncommitted | read committed | repeatable read | serializable或者直接修改变量值也可以：
1 2 set @@global.tx_isolation = ‘read-uncommitted‘ | ‘read-committed‘ | ‘repeatable-read‘ | ‘serializable‘ set @@session.tx_isolation = ‘read-uncommitted‘ | ‘read-committed‘ | ‘repeatable-read‘ | ‘serializable‘
查看当前会话的隔离级别方法如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> select @@tx_isolation; mysql> select @@global.tx_isolation; mysql> select @@tx_isolation;select @@global.tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+
注意，事务隔离级别的设置只需在需要的一端设置，不用在两边会话都设置。例如想要让会话2的查询加锁，则只需在会话2上设置serializable，在会话1设置的serializable对会话2是没有影响的，这和SQL Server中一样。但是，MariaDB/MySQL除了serializable隔离级别，其他的隔离级别都默认会读取旧的行版本，所以查询永远不会造成阻塞。而SQL Server中只有基于快照的两种隔离级别才会读取行版本，所以在4种标准的隔离级别下，如果查询加的S锁被阻塞，查询会进入锁等待。
在MariaDB/MySQL中不会出现更新丢失的问题，因为独占锁一直持有直到事务结束。当1个会话开启事务A修改某记录，另一个会话也开启事务B修改该记录，该修改被阻塞，当事务A提交后，事务B中的更新立刻执行成功，但是执行成功后查询却发现数据并没有随着事务B的想法而改变，因为这时候事务B更新的那条记录已经不是原来的记录了。但是事务A回滚的话，事务B是可以正常更新的，但这没有丢失更新。
7.2 read uncommitted
该级别称为未提交读，即允许读取未提交的数据。
在该隔离级别下，读数据的时候不会申请读锁，所以也不会出现查询被阻塞的情况。
在会话1执行：
1 2 3 4 5 create table ttt(id int); insert into ttt select 1; insert into ttt select 2; begin; update ttt set id=10 where id=1;
如果会话1的隔离级别不是默认的，那么在执行update的过程中，可能会遇到以下错误：
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
这是read committed和read uncommitted两个隔离级别只允许row格式的二进制日志记录格式。而当前的二进制日志格式记录方式为statement时就会报错。要解决这个问题，只要将格式设置为row或者mixed即可。
set @@session.binlog_format=row;
在会话2执行：
1 2 3 4 5 6 7 8 set transaction isolation level read uncommitted; select * from ttt; +------+ | id | +------+ | 10 | | 2 | +------+
发现查询的结果是update后的数据，但是这个数据是会话1未提交的数据。这是脏读的问题，即读取了未提交的脏数据。
如果此时会话1进行了回滚操作，那么会话2上查询的结果又变成了id=1。
在会话1上执行：
rollback;
在会话2上查询：
1 2 3 4 5 6 7 mysql> select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
这是读不一致问题。即同一个会话中对同一条记录的读取结果不一致。
read uncommitted一般不会在生产环境中使用，因为问题太多，会导致脏读、丢失的更新、幻影读、读不一致的问题。但由于不申请读锁，从理论上来说，它的并发性是最佳的。所以在某些特殊情况下还是会考虑使用该级别。
要解决脏读、读不一致问题，只需在查询记录的时候加上共享锁即可。这样在其他事务更新数据的时候就无法查询到更新前的记录。这就是read commmitted隔离级别。
7.3 read committed
对于熟悉SQL Server的人来说，在说明这个隔离级别之前，必须先给个提醒：MariaDB/MySQL中的提交读和SQL Server中的提交读完全不一样，MariaDB/MySQL中该级别基本类似于SQL Server中基于快照的提交读。
在SQL Server中，提交读的查询会申请共享锁，并且在查询结束的一刻立即释放共享锁，如果要查询的记录正好被独占锁锁住，则会进入锁等待，而没有被独占锁锁住的记录则可以正常查询。SQL Server中基于快照的提交读实现的是语句级的事务一致性，每执行一次操作事务序列号加1，并且每次查询的结果都是最新提交的行版本快照。
也就是说，MariaDB/MySQL中read committed级别总是会读取最新提交的行版本。这在MySQL的innodb中算是一个术语:"一致性非锁定读"，即只读取快照数据，不加共享锁。这在前文已经说明过。
MariaDB/MySQL中的read committed隔离级别下，除非是要检查外键约束或者唯一性约束需要用到gap lock算法，其他时候都不会用到。也就是说在此隔离级别下，一般来说只会对行进行锁定，不会锁定范围，所以会导致幻影读问题。
这里要演示的就是在该级别下，会不断的读取最新提交的行版本数据。
当前示例表ttt的记录如下：
1 2 3 4 5 6 7 mysql> select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
在会话1中执行：
begin;update ttt set id=100 where id=1;
在会话2中执行：
1 2 3 set @@session.tx_isolation=‘read-committed‘; begin; select * from ttt;
会话2中查询得到的结果为id=1，因为查询的是最新提交的快照数据，而最新提交的快照数据就是id=1。
1 2 3 4 5 6 +------+ | id | +------+ | 1 | | 2 | +------+
现在将会话1中的事务提交。
在会话1中执行：
commit;
在会话2中查询记录：
1 2 3 4 5 6 7 select * from ttt; +------+ | id | +------+ | 100 | | 2 | +------+
结果为id=100，因为这个值是最新提交的。
再次在会话1中修改该值并提交事务。
在会话1中执行：
begin;update ttt set id=1000 where id=100;commit;
在会话2中执行：
1 2 3 4 5 6 7 select * from ttt; +------+ | id | +------+ | 1000 | | 2 | +------+
发现结果变成了1000，因为1000是最新提交的数据。
read committed隔离级别的行版本读取特性，在和repeatable read隔离级别比较后就很容易理解。
7.4 repeatable read
同样是和上面一样的废话，对于熟悉SQL Server的人来说，在说明这个隔离级别之前，必须先给个提醒：MariaDB/MySQL中的重复读和SQL Server中的重复读完全不一样，MariaDB/MySQL中该级别基本类似于SQL Server中快照隔离级别。
在SQL Server中，重复读的查询会申请共享锁，并且在查询结束的一刻不释放共享锁，而是持有到事务结束。所以会造成比较严重的读写并发问题。SQL Server中快照隔离级别实现的是事务级的事务一致性，每次事务开启的时候获取最新的已提交行版本，只要事务不结束，读取的记录将一直是该行版本中的数据，不管其他事务是否已经提交过对应的数据了。但是SQL Server中的快照隔离会有更新冲突：当检测到两边都想要更新同一记录时，会检测出更新冲突，这样会提前结束事务(进行的是回滚操作)而不用再显式地commit或者rollback。
也就是说，MariaDB/MySQL中repeatable read级别总是会在事务开启的时候读取最新提交的行版本，并将该行版本一直持有到事务结束。但是MySQL中的repeatable read级别下不会像SQL Server一样出现更新冲突的问题。
前文说过read committed隔离级别下，读取数据时总是会去获取最新已提交的行版本。这是这两个隔离级别在"一致性非锁定读"上的区别。
另外，MariaDB/MySQL中的repeatable read的加锁方式是next-key lock算法，它会进行范围锁定。这就避免了幻影读的问题(官方手册上说无法避免)。在标准SQL中定义的隔离级别中，需要达到serializable级别才能避免幻影读问题，也就是说MariaDB/MySQL中的repeatable read隔离级别已经达到了其他数据库产品(如SQL Server)的serializable级别，而且SQL Server中的serializable加范围锁时，在有索引的时候式锁范围比较不可控(你不知道范围锁锁住哪些具体的范围)，而在MySQL中是可以判断锁定范围的(见innodb锁算法)。
这里要演示的就是在该级别下，读取的行版本数据是不随提交而改变的。
当前示例表ttt的记录如下：
1 2 3 4 5 6 7 mysql> select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
在会话1执行：
begin;update ttt set id=100 where id=1
在会话2中执行：
1 2 3 4 5 6 7 8 set @@session.tx_isolation=‘repeatable-read‘; begin;select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
查询的结果和预期的一样，来自开启事务前最新提交的行版本数据。
回到会话1提交事务：
commit;
再回到会话2中查询：
1 2 3 4 5 6 7 select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
再次去会话1更新该记录：
begin;update ttt set id=1000 where id=100;commit;
再回到会话2执行查询：
1 2 3 4 5 6 7 select * from ttt; +------+ | id | +------+ | 1 | | 2 | +------+
发现结果根本就不会改变，因为会话2开启事务时获取的行版本的id=1，所以之后读取的一直都是id=1所在的行版本。
7.5 serializable
在SQL Server中，serializable隔离级别会将查询申请的共享锁持有到事务结束，且申请的锁是范围锁，范围锁的情况根据表有无索引而不同：无索引时锁定整个表，有索引时锁定某些范围，至于锁定哪些具体的范围我发现是不可控的(至少我无法推测和计算)。这样就避免了幻影读的问题。
这种问题在MariaDB/MySQL中的repeatable read级别就已经实现了，MariaDB/MySQL中的next-key锁算法在加范围锁时也分有无索引：无索引时加锁整个表(实际上不是表而是无穷大区间的行记录)，有索引时加锁部分可控的范围。
MariaDB/MySQL中的serializable其实类似于repeatable read，只不过所有的select语句会自动在后面加上lock in share mode。也就是说会对所有的读进行加锁，而不是读取行版本的快照数据，也就不再支持"一致性非锁定读"。这样就实现了串行化的事务隔离：每一个事务必须等待前一个事务(哪怕是只有查询的事务)结束后才能进行哪怕只是查询的操作。
这个隔离级别对并发性来说，显然是有点太严格了。
