【MySQL】4. 事务

【MySQL】4. 事务

事务(Transaction)是数据库区别于文件系统的重要特性之一。事务会把数据库从一种一致性转换为另一种一致性状态。在数据库提交工作时,可以确保要么所有的修改都已经保存了,要么所有的修改都不保存。

事务需要符合ACID的特性:

原子性(Atomicity):保证事务中的操作要不不执行,要不全执行 (单事务需要满足)
一致性(Consistency):保证事务从一个状态装换到另一个状态 
隔离性(Isolation):保证事务之间是隔离的,脏读 和 不可重复读(幻读)(并发需要满足)
持久性(Durability):保证事务的数据一旦提交,可以被写入磁盘存储,即提交即完成

1 . 认识事务

1.1 概述

事务可由一条简单的SQL语句组成,也可以由一组负责的SQL组成。事务是访问并更新数据库中数据的一个程序执行单元。核心特性:原子性。

事务有着极其严格的定义,必须同时满足四个特性,即事务的ACID。

A(Atomicity),原子性。原子性指整个数据库事务是不可分割的工作单位,只有使事务中所有的数据库操作执行成功,才算事务成功。事务中有任何SQL执行失败,还能够回滚到事务执行之前的状态。

C(Consistency),一致性。一致性指事务将数据库从一种状态转变为下一种一致性状态。在事务开始之前和事务结束之后,数据库的完整性约束没有被破坏。数据库的状态只有两种,要不执行成功,数据库数据跟SQL预期的结果一致,要不执行失败,回滚到事务开始之前的状态。

I(Isolation),隔离性。隔离性还有其他的称呼,如并发控制(concurrency control)、可串行化(serializability)、锁(locking)等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离,即该事务提交前对其他事务都不可见,通常这使用锁来实现。当前数据库系统中都提供了一种粒度锁(granular lock)的策略,允许事务仅锁住一个实体对象的子集,以此来提高事务之间的并发度。

D(Durability),持久性。事务一旦提交,其结果是永久性的,即使发生宕机等故障,数据库也能将数据恢复。需要注意的是,只能从事务本身的角度来保证结果的永久性。持久性保证事务系统的高可靠性,而不是高可用性。对于高可用性的实现,事务本身不能保证,需要一些系统共同完成。
(理解:事务提交并没有写磁盘,只是写了缓存或者说只是写了 redo 日志文件,在数据库宕机重启之后,可以通过 redo 恢复之前到操作记录,再写入磁盘,所以事务一旦提交,就是持久性的)

虽然事务定义了严格的事务要求,但是数据库厂商处于各种目的,并没有严格的满足事务的ACID标准。

对于MySQL 的 NDB Cluster引擎来说,虽然支持事务,但是不满足D(持久化)的要求(不用)
对于Oracle数据库,默认隔离级别是 Read Commited,不满足I(隔离性)的要求,不可重复读
(虽然在大多数情况下,不会导致严重的结果,甚至带来性能的提升,但还是可能有潜在问题)
对于InnoDB,默认隔离级别是 Read Repeatable,完全遵循和满足事务的ACID特性。

1.2 分类

从事务理论角度可以把事务分为几种类型:

扁平事务(Flat Transactions):默认事务,失败回滚到事务开始的保存点
带有保存点的扁平事务(Flat Transactions with Savepoints):多个事务保存点
链事务(Chained Transactions):提交操作和开始下一个事务操作,合并为原子操作
嵌套事务(Nested Transaction):顶层事务控制各个层次的事务
分布式事务(Distributed Transaction):通常是一个分布式环境下运行的扁平事务

扁平事务:事务类型中最简单的一种,实际生产环境中,可能是使用最为频繁的事务。在扁平事务中,所有操作都处于同一层次,其有 Begin work开始,由commit work 或者 rollback结束,过程中的操作是原子的。要不都执行,要不都回滚。扁平事务是应用程序成为原子操作的基本组成模块。

扁平事务的主要限制是不能提交或者回滚事务的某一部分,或分几个步骤提交,只有一个保存点

带有保存点的扁平事务:除了支持扁平事务支持的操作外,允许在事务执行过程中回滚到该事务的上一个保存点。这是因为某些事务可能在执行过程中出现了错误,但不会导致所有的操作都无效,放弃整个事务是不合适的。保存点:用来通知系统保存事务当前的状态,以便当之后发生错误时,事务能回到保存点当时的状态。每一个保存点都是一个commit操作。

扁平事务:其隐式的设置了一个保存点。然而在整个事务中,只有这一个保存点,因此,回滚只能回滚到事务开始时的状态。

带有保存点的扁平事务:可以设置多个保存点,当出现问题时,保存点能用作内部的重启动点,根据应用逻辑,决定是否回到最近的一个保存点还是其他的保存点。
1. 保存点:用Save work函数来建立,通知系统记录当前事务的状态。
2. 保存点在事务内部是递增的,Rollbakc不会影响保存点的计数,并且单调递增的编号能保持事务执行的整个历史过程,包括在执行过程中想法的改变。
3. 只要事务完全被回滚,只是回滚到某一个保存点,就表示当前事务还是活跃的,如果想完全回滚,需要执行 Rollback work.

链事务:保存点模式的一种变种。在提交一个事务时,释放不需要的数据对象,将必要的处理上下文隐式的传给下一个要开始的事务。

注意:提交事务操作和开始下一个事务操作将合并为一个原子操作,下一个事务将看到上一个事务的结果(串行,序列化),好像是在一个事务中进行。

带有保存点...事务:当发生系统崩溃时,所有的保存点都将消失,因为其保存点是非持久的,所以当进行恢复时,事务需要从开始出重新执行,而不能从最近的保存点继续执行。

带有保存点的扁平事务和链事务的区别:

带有保存点..事务能回滚到任意正确的保存点。而链事务的回滚仅限于当前事务,只能恢复到最近的一个保存点。对于锁的处理,两个也不相同。链事务在执行commit后即释放了当前事务锁持有的锁,而带有保存点的扁平事务不影响所持有的锁(每一次save work都是一次commit操作)

嵌套事务:一个层次结构框架。由一个顶层事务(Top-level transaction)控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务,其控制每一个局部的变换。

在Moss的理论中,实际的工作是交由叶子节点来完成的,即只有叶子节点的事务才能访问数据库、发送消息、获取其他理性的资源。而高层的事务仅负责逻辑控制,决定何时调用相关的子事务。即使一个系统不支持嵌套事务,用户也可以通过保存点技术来模拟嵌套事务。

分布式事务:通常是一个在分布式环境下运行的扁平事务,因此需要根据数据所在的位置访问网络中的不同节点。

2. 事务的实现

原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志,用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。

redo 和 undo 的作用都可以视为一种恢复操作,redo 恢复提交事务修改的页操作,而 undo 回滚行记录到某个特定版本,因此两者记录的内容不同。
redo 通常是物理日志,记录的是页的物理修改操作。
undo 是逻辑日志,根据每行记录进行记录。

2.1 redo ??? 还是不太理解

2.1.1 基本概念

重做日志用来实现事务的持久性,即事务ACID中的D。其有两部分组成:一是内存中的重做日志缓冲(redo log buffer),其是易失的。二是重做日志文件(redo log file),其是持久的。

InnoDB是事务的存储引擎,其通过Force Log at Commit 机制实现事务的持久化。

当事务提交(Commit)时,必须先将该事务的所有日志写入到重做日志文件进行持久化,等待事务的commit操作完成才算完成。这里的日志是指重做日志。

在InnoDB中,有两部分组成,即 redo log 和 undo log。
redo log:保证事务的持久性,redo log 都是顺序写的,等待写入到内存或者磁盘
undo log:帮助事务回滚及MVCC的功能,undo log是有需要进行随机读取的

在数据库运行时不需要对redo log的文件进行读取操作,而undo log是有需要进行随机读取的。

为了确保每次日志都写入重做日志文件,在每次将重做日志缓冲写入重做日志文件后,InnoDB存储引擎都需要调用一次 fsync 操作。

由于重做日志文件打开并没有O_Direct选项,因此重做日志缓冲先写入文件系统缓存,为了确保重做日志写入磁盘,必须执行一次fsync操作。由于fsync的效率取决于磁盘的性能,因此磁盘的性能决定了事务提交的性能,也就是数据库的性能。

InnoDB存储引擎允许用户手工设置非持久性的情况发生,以此提高数据库的性能。即当事务提交时,日志不写入重做日志文件,而是等待一个时间周期后再执行fsync操作。由于并非强制在事务提交时进行一次fsync操作,显然可以显著提高数据库的性能。但是当数据库宕时,由于部分日志未刷新到磁盘,因此会丢失最后一段时间的事务。

参数innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略。
该参数的默认值为1,表示事务提交时必须调用一次fsync操作。
该参数的值为0,表示事务提交时不进行写入重做日志操作,这个操作仅在 master therad完成
该参数的值为2,表示事务提交时将重做日志写入重做日志文件,但仅写入文件系统的缓冲中,不进行fsync操作。在这个设置下,数据库发生宕机而操作系统不宕机的情况下,会丢失缓冲的数据


用户可以设置为 0 或者 2,但是这种设置会丧失事务的ACID特性。
在MySQL中还有一种二进制日志(binlog),其用来进行POINT-TIME(PIT)的恢复及主从复制(Replication)环境的建立。从表面上看其和重做日志非常相似,都是记录了对于数据库操作的日志。但其两者有非常大的不同。

产生位置不同:
重做日志是在InnoDB存储引擎层产生,而二进制日志是在MySQL数据库的上层产生的,并且二进制日志不仅仅针对于InnoDB存储引擎,MySQL数据库的任意存储引擎对于数据库的更改都会产生二进制日志。
文件类型:
MySQL数据库上层的二进制日志是一种逻辑日志,其记录的都是对应的SQL语句,而InnoDB存储引擎层面的重做日志是物理格式日志,其记录的是对于每个页的修改。
写入磁盘的时间不同:
二进制只在事务提交完成后进行一次写入,每个事务仅有一个日志文件
InnoDB的重做日志在事务进行中不断的被写入,表现为日志并不是随事务提交的顺序进行写入的,每个事务有多个日志文件,并发写入
continue......

待补充:log block 、log group、重做日志格式、LSN、恢复、

2.2 undo:回滚 MVCC

2.2.1 基本概念

重做日志记录了事务的行为,可以很好的通过其对页进行“重做”操作。但是事务有时需要进行回滚操作,这时就需要undo。因此在读数据库进行修改时,InnoDB存储引擎不但会产生redo,还会产生一定量的undo。当用户执行事务失败了,需要rollback时,就可以利用这些undo信息将数据回滚到修改之前的样子。

redo 存放在重做日志文件中,
undo 存放在数据库内部的一个特殊段(segment)中,undo段位于共享表空间中。
可以通过py_innodb_page_info.py工具查看当前共享表空间中undo的数量。
undo 并不是用于将数据库物理恢复到执行语句或事务之前的样子。undo是逻辑日志,因此只是将数据库逻辑的恢复到原来的样子。所有修改都被逻辑的取消了,但是数据结构和页本身在回滚之后可能大不相同。
因为多用户并发系统中,可能有多个并发事务,数据库的主要任务是协调对数据记录的并发访问。比如:一个事务在修改当前一个页中某条记录,同时还有别的事务在对同一个页中另几条记录进行修改,因此,不能将一个页回滚到事务开始的地方,因为这样会影响到其他的事务。比如:自增字段的改变、表空间的扩容问题等。

除了回滚操作,undo的另一个作用是MVCC,即在InnoDB存储引擎中MVCC的实现是靠undo来实现的。当用户读取一条记录,若该记录被其他的事务占用,用户可以通过undo读取之前的行版本信息,以此实现一致性非锁定读(读取快照)。

undo log 会产生redo log,因为undo log页需要持久性的保护。

2.2.2 undo存储管理

InnoDB存储引擎对undo的管理同样采用段的方式。但是这个段和之前介绍的段有所不同。首先,InnoDB存储引擎有rollback segment,每个回滚段记录了1024个undo log segment,而在每个undo log segment段中进行undo页的申请。共享表空间偏移量为5的页(0,5)记录了所有的rollback segment header所有的页,这个页的类型为 FIL_PAGE_TYPE_SYS。

1、rollback segment
2、事务提交
3、重用undo页-->purge耗时
在InnoDB1.1版本之前,只有一个rollback segment,因此支持同时在线的事务限制为1024。从1.1版本开始InnoDB支持最大128个rollback segment,故支持同时在线的事务限制提高到了128*1024.
虽然InnoDB1.1版本支持128个rollback segment,但这些rollback segment 都存储在共享表空间中。从InnoDB1.2版本开始,可通过参数对rollbackk segment做进一步设置:
innodb_undo_directory : 设置文件的路径,可以独立,不在共享表中
innodb_undo_logs: 设置 rollback segment的个数,默认值为128
innodb_undo_tablespaces:设置 构成rollback segment 的文件数量

事务在undo log segment 分配页并写入undo log的这个过程同样需要写入重做日志。当事务提交时,InoDB存储引擎会做以下两件事情:
1、将undo log放入列表中,以供之后的purge操作
2、判断undo log所在的页是否可以重用,若可以则分配给下一个事务使用
事务提交之后,并不能马上删除undo log 及所在的页。这是因为可能还有其他事务需要通过undo log来使用MVCC功能,获取行记录之前的版本。故事务提交时将undo log 放入一个链表中,最终删除undo log 及undo log所在页由purge线程来判断。

在事务提交之后,并不能马上释放页空间,为每一个事务分配一个单独的undo页会非常浪费空间。所以,存储引擎设计中对undo进行了重用。如果一个undo页使用空间小于3/4,表示该页可以被使用。undo log的列表是以记录进行组织的,而undo页可能存放着不同事物的undo log,这样purge操作涉及到磁盘的离散读取,是一个比较缓慢的过程。

2.2.3 undo log格式

undo log分为两种:insert undo log 、 update undo log

insert undo log 是指insert操作产生的undo log记录。因为insert只对事物本身有效,对其他事务不可见(隔离性的要求),所以undo log可以在事务提交之后直接删除。
update undo log 是指update操作或者delete操作产生的undo log记录。该记录需要提供MVCC机制,在事务提交的时候,该记录可能在被其他事务引用中,因此不能在事务提交之后直接删除,需要放入undo log链表,等待purge线程处理

2.2.4 查看undo信息

Oracle 和 Microsoft SQL Server数据库都由内部的数据字典来判断当前undo的信息,InnoDB存储引擎在这方面做得还不够,DBA只能通过原理和经验来进行判断。InoDB对information_scheme进行了扩展,添加了两张数据字典表,这样用户可以非常方便和快捷的查看undo的信息。

1、INNODB_TRX_ROLBACK_SEGMENT:用来查看rollback segment

2、INNODB_TRX_UNDO:记录事务对应的undo log,方便DBA和开发人员详细了解每个事务产生的undo量。
 >事务提交之后,由于undo页重用,insert_undo_list--,insert_undo_cached++。
 >delete操作并不会删除记录,只是将记录标记为已删除,也就是将记录的delete flag设置为1,而记录最终的删除是在purge操作中完成。
 >update主键的操作分两步完成。首先将原主键记录标记为已删除,因此需要产生一个类似为TRX_UNDO_DEL_MARK_REC的undo,之后插入一条新的记录,因此需要产生一个undo log,类型为TRX_UNDO_INSERT_REC。undo_rec_no显示了产生日志的步骤。

2.3 purge:用来清理之前的delete和update标记的undo log

purge用于最终完成delete和update操作,这样设计是因为InnoDB存储引擎支持MVCC,所以记录不能在事务提交时立即进行处理。这时可能有其他的事务正在引用该记录。当该行记录不被引用时,purge就可以清理之前行记录的版本。

InnoDB存储引擎的undo log设计允许多个事务的undo log存在。InnoDB还有一个history list列表,根据事务提交的顺序,将undo log进行链接。
history list表示按那种啊事务提交的顺序将undo log 进行组织,先提交的事务总是是尾端,undo page存放了undo log,由于可以重用,因此一个undo page中可能存放了多个不同事务的undo log.
在执行purge时,首先从history list中找到第一个需要清理的记录,清理完成之后,从当前的undo page中寻找是否存在可以被清理的记录,继续清理,如果没有可以被清理的记录,返回history list继续寻找下一条。
这种从history list中找undo log,然后从undo page中找undo log的设计模式是为了避免大量的随机读取操作,从而提高purge的效率。

2.4 group commit

在非只读事务中,每次事务提交时都需要进行一次fsync操作,但是fsync操作磁盘同步操作时非常耗时的。数据库提供了 group commit,调用一个fsync操作即可将多个事务的日志写入到文件。

对于InnoDB存储引擎来说,事务提交时会进行两个阶段的操作:
1) 修改内存中的事务对应的信息,并且将日志写入重做日志缓冲
2)调用fsync将确保日志都从重做日志缓冲写入磁盘
在步骤2通过group commit机制刷新日志打磁盘,极大的提高了性能。
但是在1.2版本之前,开启了二进制日志后,InnoDB的group commit会失效,导致性能下降。(而且在线环境多使用replication,因此二进制日志的选项基本都是开启状态)
导致该问题的主要原因是:为了保证存储引擎层中的事务和二进制日志的一致性,两者之间使用了两阶段事务:
1)当事务提交时,InnoDB进行prepare操作
2)MySQL数据库上层写入二进制日志
3)InnoDB存储引擎层将日志写入重做日志文件
  a) 修改内存中事务对应的信息,并且将日志写入重做日志缓冲
  b) 调用fsync将确保日志都从重做日志缓冲写入磁盘
该过程需要因为备份和replication的需要,使用了prepare_commit_mutex锁保证 步骤3)中 步骤a) 不能在其他事务执行 步骤b) 的时候进行。导致 group commit失效了。

从5.6版本之后,采用了BLGC(Binary Log Group Commit)来保证group commit.实现分为几个步骤:
MySQL上层在提交之前,将事务按顺序放入一个队列中,队列中的第一个事务称为leader,其他事务称为follwer,leader控制着follower的行为。分为三步:
1)Flush阶段:将每个事务的二进制日志写入内存中
2)Sync阶段:将内存中的二进制日志刷新到磁盘,若队列有多个事务,则一次sync刷新多个事务。BLGC的核心:队列
3)Commit阶段:leader根据顺序调用存储引擎层事务的提交,InnoDB存储引擎本就支持group commit,没有了prepare_commit_mutex之后,就可以支持group commit了。
当一组事务进行Commit阶段时,其他新事务可以进行Flush阶段,从而使group commit不断生效,所以每次fsync都能刷新多个事务的日志到磁盘。

3. 事务控制语句

在MySQL命令行的默认情况下,事务都是自动提交的。如果要使用事务机制,需要显示开启一个事务,禁用当前会话的自动提交。

SET TRANSACTION                  # 设置事务的隔离级别,4种隔离级别 
Start transaction | Begin        # 显式开启一个事务
Commit    | Commit work          # 提交事务,对所有的修改是永久性的
Rollback  | Rollback work        # 回滚会结束用户的事务,并撤销所有未提交的修改
 
SAVEPOINT  identifier            # 在事务中创建一个保存点,可以回滚到该状态
RELEASE SAVEPOINT identifier     # 删除一个事务的保存点,当保存点不存在,抛出异常
ROLLBACK TO[SAVEPOINT] identifier # 回滚到某个保存点


存储过程中,Begin 有特殊含义,所以在存储过程中,需要使用 Start Transaction开启事务
commit work用来控制事务结束后的行为是chain还是release,如果是chain,事务即链事务

容易犯的两个错误:

1)事务是否回滚,需要用户自己决定:
InnoDB存储引擎中的事务都是原子的,这说明:构成事务的每条语句都会提交(成为永久),或者所有的语句都回滚,这种保护延伸到单个的语句,一个语句要不完全成功,要不就回滚,如果语句抛出了异常,并不会导致之前的语句自动回滚,只是当前语句的操作会回滚,然后由用户决定 是否回滚该事务。
2)Rollback to savepoint : 该操作并不会主动结束一个事务,而是回滚到保存点,该事务最终结束还是需要用户 调用 commit 或者 rollback处理。

4. 隐式提交的SQL语句

执行以下SQL语句默认会自动提交,默认的commit:

DDL语句:
Create、Alter、Drop、Truncate
隐式修改MySQL架构的操作:
Create user、Drop user、Grant、Rename、Revoke、Set password
管理语句:
Analyze table、Cache index、Check table、Load index into cache、Optimize table、Repair table

注意:DDL语句在 Microsoft SQL Server 数据库中是可以回滚的,但是在InnoDB和Oracle数据库中不能回滚,默认 commit。
Truncate table 语句是 DDL,虽然执行结果和 delete table一致,但是是不能回滚的。

5. 事务操作的统计

InnoDB存储引擎是支持事务的,因此InnoDB存储引擎的应用需要在考虑每秒请求数(Question Per Second, QPS)的同时,关注每秒的事务处理能力(TPS, Transaction Per Second)。

计算TPS的方法: (com_commit + com_rollback)/ time,该方法不会计算隐式提交和回滚(默认autocommit=1,需要显式提交)的事务。

show Global status like 'com_commit'\G;  # 查看当前的 com_commit

MySQL还有两个参数handle_commit 和 handler_rollback用于事务的统计操作,在 MySQL 5.1可以很好的统计引擎显式和隐式提交的操作,但是在 InnoDB Plugin中不能正确的统计事务的次数。所以,如果用户的程序都是显式提交和回滚的,可以使用 com_commit 和 com_rollback 统计,隐式提交不能被正确的统计,不考虑统计。

6. 隔离级别

ISO和ANIS SQL标准指定了四种事务隔离级别。但是很少有数据库厂商遵循这些标准。大多数数据库系统都没有提供真正的隔离性。数据库实现者在正确性和性能之间做了妥协。比如 Oracle数据库就不支持 READ UNCOMMITED(未提交读) 和 REPEATABLE READ( 重复读 )。

SQL标准定义的四个隔离级别:
READ UNCOMMITED (未提交读)
READ COMMITED (已提交读)
REPEATABLE READ (可重复读)2.9999
SERIALIZABLE  (序列化) 3   SQL 和 SQL2 的默认事务隔离级别

InnoDB 存储引擎默认支持的隔离级别是 REPEATABLE READ,但是与标准的SQL不同,InnoDB在该级别下, 通过Next-Key Lock锁(原理是:Gap Lock)的算法设计,避免了幻读的产生。所以,InnoDB存储引擎在REPEATABLE READ的事务隔离级别下保证了事务的隔离性要求,达到了SQL的 SERIALIZABLE隔离级别。

隔离级别越低,事务请求的锁越少或保持锁的时间越短,也就是性能越高。

大部分用户质疑 Serializable 隔离级别会带来性能问题,但是根据 Jim Gray在《Transaction Processing》中指出的,两者的开销是一致的,甚至Serializable的性能更好
!!! 
因此用户可以大胆的使用 Serializable。

用户命令设置事务的隔离级别:
set [global | session] transaction isolation level {
 READ UNCOMMITED
 | READ COMMITED
 | REPEATABLE READ
 | SERIALIZABLE
}

在Serializable 隔离级别下,每一次 select 都添加 lock in share mode,因此该事务隔离级别下,不太支持一致性非锁定读(读取操作不需要等待X锁释放可以读取行快照(MVCC机制))。因为InnoDB在REPEATABLE READ就可以达到 3 的隔离要求,所以一般不在本地事务中使用SERIALIZABLE,通常只用在InnoDB存储引擎的分布式事务中。

在READ COMMITED隔离级别下,除了唯一性的约束检查和外键约束检查需要gap lock,InnoDB存储引擎不会再使用 gap lock算法。使用该隔离级别需要注意主从问题

在MySQL5.1版本上,使用Statement格式的二进制日志文件会异常:
Read commited事务隔离级别默认只能工作在 replication(复制)二进制日志为ROW的格式下


在 5.0 版本之前,使用Statement格式的二进制日志文件会出现数据不一致性:
5.0版本之前,不支持ROW格式的二进制日志时,通过innodb_locks_unsafe_for_binlog 设置为 1 可以在二进制日志为Statement格式下使用Read commited的事务隔离级别,但可能会出现数据结果不一致的情况:
操作步骤:
   1. 开始事务A 删除 id<=5 的数据,但不提交
   2. 开始事务B 插入一条id = 3 的数据,提交
   3. 提交事务A
此时在 master 中查看,可以看到id=3的数据,在 slave中查看,没有数据。

数据发生了不一致性,导致这个问题的原因有两点:
1. 在 Read commited隔离级别下,事务没有使用 gap lock,所以事务B可以插入新的数据。
2. Statement记录的是master上产生的 SQL语句,在master上,执行的顺序是 先删后插入,但是在Statement中记录的是 先插入后删除,所以在 master 和 slave中的执行顺序不一致

要解决主从不一致问题,只需要解决上述问题中的任意一个即可:
使用 REPEATABLE READ 依赖Next-Key Lock(Gap lock机制)
使用 ROW 格式的二进制日志文件

在MySQL 5.1版本之后,InnoDB支持了 ROW格式的二进制日志记录格式。

日志格式:Statement记录SQL语句,二进制格式ROW记录行的变更信息,可以更好的记录数据的变化情况,避免不同步现象,所以建议使用ROW格式的日志记录

7. 分布式事务

7.1 MySQL数据库分布式事务

InnoDB存储引擎提供了对XA事务的支持,并通过XA事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源参与到一个全局事务中。事务资源通常是关系型数据库系统,但也可以是其他类型的资源。全局事务要求在其中的所有参与事务要么全部提交,要么全部回滚。在使用分布式事务时,InnoDB的事务隔离级别必须设置为Serializable。

XA事务允许不同的数据库之间的分布式事务,只有数据库支持XA事务,不关心数据库是MySQL、Oracle、还是SQL Server。
XA事务由一个或多个资源管理器、一个事务管理器、一个应用程序组成。
资源管理器:提供访问事务资源的方法,通常是指数据库。
事务管理器:协调参与全局事务中的各个事务,需要和参与全局事务的所有资源管理器进行通信。
应用程序:定义事务的边界,指定全局事务中的操作。

事务管理器通常是 连接数据库的客户端。

分布式事务使用两段提交的方式:
在第一阶段,所有参与全局事务的节点都开始准备(Prepare),告诉事务管理器它们准备好提交了。
在第二阶段,事务管理器告诉资源管理器 执行Rollback还是Commit.

如果任何一个节点不能提交,则所有的节点都被告知需要回滚。与本地事务不同的是,分布式需要等待所有的节点Prepare,在收到所有节点的消息之后,再决定进行Rollback还是Commit。

分布式事务XA一般通过编程语言来实现,Java 有 JTA(Java Transaction API)可以很好的支持MySQL的分布式事务。
通过innodb_support_xa可以查看是否开启了XA事务的支持(默认为ON。)

7.2 内部XA事务

在MySQL中,在存储引擎和插件 或者 存储引擎和存储引擎之间,存在一个内部XA事务。

最常见的内部XA事务存在于binlog 与 InnoDB存储引擎之间。由于复制的需要,多数数据库开启了binlog功能。在事务提交时,先写二进制日志,再写InnoDB的重做日志。两个操作都是原子的,二进制日志和重做日志(redo)必须同时写入。

如果二进制日志先写了,在写入InnoDB重做日志的时候发生了宕机,那么slave可能接收到master传过去的二进制日志并执行,最终导致主从不一致。
在binlog 与 InnoDB存储引擎之间使用XA事务,当事务提交时,存储引擎会做一个Prepare操作,将事务的xid写入,接着进行二进制日志的写入,如果在InnoDB存储引擎提交之前,MySQL宕机,那么MySQL在重启之后,会检查记录的事务id时候提交,如果没有提交,存储引擎层会在进行一次提交操作。

8. 不好的事务习惯

8.1 在循环中提交

在存储过程中,使用事务插入多条数据时,需要有循环操作,不要在循环内部写事务提交,这样事务会提交很多次,每次提交都会写一次重做日志,非常消耗时间,可以在循环完成之后,做一次事务提交,这样,只写一些重做日志,性能较高。

使用InnoDB存储引擎需要注意:

InnoDB 是默认自动提交的。
InnoDB 不要求事务尽快的释放。
InnoDB有足够的undo存储事务的操作记录(Oracle没有足够的undo可能出现Snapshot Too Old)

所以,InnoDB不应该在一个循环中反复提交事务,不管是显式还是隐式的。

8.2 使用自动提交

MySQL默认设置使用自动提交。可以通过设置的当时改变默认的提交方式:

命令行:
set autocommit=0;  #取消自动提交
也可以使用 Start transaction, Begin 来显式开启一个事务,在显式开启事务后,在默认设置下(参数completion_type=0),MySQL会自动执行 set autocommit=0的命令,并在rollback 或 commit结束事务之后执行set autocommit=1,来开启自动提交。

对于不同的API,自动提交有区别,C API默认是自动提交,Python默认不开启自动提交。

8.3 使用自动回滚

InnoDB存储引擎支持通过定义一个Handler来进行自动事务的回滚操作。如在一个存储过程中发生错误可以自动回滚。因此很多开发人员喜欢在应用程序的存储过程中使用自动回滚操作。但是多数的数据库自动回滚不会抛出异常,这样导致开发人员很难发现问题所在,而Microsoft SQL Server的数据库的自动回滚会抛出异常。

所以在存储过程中使用自动回滚操作不是一个良好的习惯,应该更关注程序层面,存储过程应该只关心逻辑代码,跟事务相关的东西应该放在程序中。

9. 长事务

长事务(Long-Lived Transaction):执行时间较长的事务。由于ACID的特性,这个操作被封装在一个事务中完成,在执行过程中,当出现问题,事务回滚的时间比事务正常执行的时间还有长,因此是非常不合理的。

对于长事务,可以通过转化为小批量(mini batch)的事务来进行处理。当事务出现问题时,只需要回滚一部分数据即可。可以通过程序或者存储过程完成。

通过批量处理小事务来完成大事务的逻辑。每完成一个小事务,将完成的结果存放在batchcontext表中,表示已完成批量事务的最大账号ID。若事务在运行过程中产生问题,需要重做事务,可以从这个已完成的最大事务ID继续进行批量的小事务,这样重启事务的代价较低。
batchcontext表的另一个好处,在长事务的执行过程中,可以大概知道事务进行到哪个阶段。

注意:在从表account中获取 max_account_no时,需要人为添加共享锁,防止在执行过程中,其他事务更新表中的数据。

continue…

小贴士: 事务执行过程中,只有在第一次真正修改记录时(比如使用INSERT、DELETE、UPDATE语句),才会被分配一个单独的事务id,这个事务id是递增的。可以人为的更新数据,以获得事务ID。

小贴士: 执行DELETE语句或者更新主键的UPDATE语句并不会立即把对应的记录完全从页面中删除,而是执行一个所谓的delete mark操作,相当于只是对记录打上了一个删除标志位,这主要就是为MVCC服务的。所谓的MVCC只是在进行普通的SEELCT查询时生效。~

0 0 vote
Article Rating
Subscribe
提醒
guest
0 评论
Inline Feedbacks
View all comments