MySQL/MariaDB中的事务和事务隔离级别
官方手册: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
。也就是说会对所有的读进行加锁,而不是读取行版本的快照数据,也就不再支持"一致性非锁定读"。这样就实现了串行化的事务隔离:每一个事务必须等待前一个事务(哪怕是只有查询的事务)结束后才能进行哪怕只是查询的操作。
这个隔离级别对并发性来说,显然是有点太严格了。