数据库入门（MySQL）：事务

• MySQL服务的逻辑架构
• 事务ACID
• 事务日志
• 事务的隔离级别
• 死锁
• MVCC
• MySQL的事务

 一、MySQL服务的逻辑架构

在了解SQL事务之前了解MySQL服务的逻辑机构是必须的前提条件，它会告诉你为什么需要事务机制。并且通过了解事务如何工作，它能解决什么问题，它不能解决什么问题，这些问题背后的根源在哪里？这都需要从服务的逻辑架构触发。

1.1MySQL服务逻辑架构图：

图片来源：https://blog.csdn.net/fuzhongmin05/article/details/70904190，关于MySQL性能优化也可以参考这篇博客。

MySQL逻辑架构总体上分为三层，最上层的服务（通常也被称为客户端）并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等。

第二层架构是MySQL的核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，起负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL（InnoDB除外，它会解析外键定义，因为MySQL服务器本身没有实现该功能），不同的存储引擎之间也不会相互通信，而只是简单地相应上层服务器的请求。

1.2并发控制：

每个客户端发起一个新的请求都由服务器端的连接/线程处理工具负责接收客户端的请求并开辟一个新的内存空间，在服务器端的内存中生成一个新的线程，当每一个用户连接到服务器端的时候就会在进程池地址空间里生成一个新的线程用于响应客户端请求，用户发起的查询请求都在线程空间内存运行，结果也在这里面缓存并返回给服务端。线程的重用和销毁都是由连接/线程处理管理器实现的。

简单的说，用户发起请求，连接/线程处理器开辟内存空间，开始提供查询的机制。

当有多个用户发起请求时，就必然会有出现并发的可能，MySQL在两个层面进行并发控制：服务层与存储引擎层。以Unix系统的email box为例，一个mbox邮箱中的所有邮件都穿行在一起，彼此首尾相连。这种格式对于读取和分析邮件信息非常友好，同时投递邮件也很容易，只要在文件末尾附加新的邮件内容即可。

但如果两个进程在同一时刻对同一个邮箱投递邮件，两份右键就会交叉地附加在邮箱文件的末尾，这就是有并发导致的数据错误。设计良好的邮箱投递系统会通过锁（lock）来防止数据损坏。如果一个客户驶入投递邮件，而邮箱已经被其他客户锁住，那就必须等待，直到锁释放才能进行投递。

这种锁的方案在实际应用环境中虽然工作良好，但并不持支并发处理。因为任意一个时刻，只有一个进程可以修改邮箱的数据，这在大量的邮箱系统中是一个问题。

 1.3锁机制：

 1.3.1读写锁

当多个用户的进程同时读取同一个邮件时不会有什么问题，但是如果有一个用户正在读取邮箱，另一个用户示图删除其中一个编号25的邮件，会产生什么结果就不确定了。读的客户可能会报错退出，也可能读取到不一致的邮箱数据。如果把邮箱看作数据库中的一张表，把邮件当作一行记录，同样的问题在数据库中也存在。解决这类问题的方法就是并发控制，在处理并发读或者写时，通过两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁和排他锁，也叫做读锁和写锁。

读锁：读锁是共享的，或者说相互不阻塞。多个用户在同一时刻读取同一个资源，而互不干扰，但读锁会阻塞写。

写锁：写锁则是排他的，也就是说写锁会阻塞其他写和读。

1.3.2锁粒度：

锁粒度是指读写锁锁定数据的范围，理想情况下是尽量锁定需要修改的部分数据，而不是锁定所有资源。比如锁定行比锁定表要更理想，任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可（如果发生冲突，即死锁。后面会有介绍）。

但是理想终归理想，毕竟加锁也是需要消耗资源的。锁的各种操作包括：获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是存取数据，那系统的性能同样会受到影响。

所以，这就需要在锁的开销和安全性之间寻求平衡。大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁，并以各种复杂的方式来实现，以便在锁比较多的情况下提供更好的性能。但是，MySQL则提供了多种选择，每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

表锁：对整张表进行写操作（插入、删除、修改更新等）前进行加锁，只有没有写锁时，其他读取的用户才能获得读锁，读锁之间不相互阻塞。

行级锁：值对当前操作行进行加锁。

表锁是开销最小的策略，但对并发却又非常大的限制，在特定场景中，表锁也可能有良好的性能。例如READ LOCAL表支持某些类型的并发操作。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会插入到读锁的前面，反之读锁不能插入到写锁的前面。可以这么来说表锁比较适合写比较大的场景。尽管存储引擎自己管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为如ALTER TABLE值类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁可以最大程度地支持并发处理，在InnoDB和XtraDB以及一些其他存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务层没有实现。

 二、事务ACID

事务是一组原子性的SQL查询，或者一个独立的工作单元。

当读完这句事务的概念时，你可能仍然不知道事务是什么？为什么需要使用事务？这是因为事务本身就是一个非常复杂的程序，它涉及的内容不仅仅只是数据的操作，而是操作数据的整个过程所包含的内容。在了解事务之前先看一个经典的案例：

假设一个银行的数据库两张表：支票（checking）表和储蓄（savings）表。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那至少需要三个步骤：

1.检查支票账户的余额高于200美元。

2.从支票账户余额中减去200美元。

3.在储蓄账户余额中增加200美元。

按照程序正常的情况来说，这三条SQL语句依次执行就可以完成这个功能，但是前面在并发中就了解到了，可能出现多个用户开启的进程同时操作这两条记录，并可能出现以下情况：

情况一：在步骤1与步骤2之间被其他并发用户开启的进程修改了余额，出现余额低于200美元的情况，这时候必然会导致步骤2执行失败。

情况二：在步骤2实现以后系统崩溃，步骤3没有实现，这必然导致用户损失200美元。

情况三：在步骤2实现以后，其他用户开启的进程删除了支票账户所有余额，这可能导致银行白给Jane 200美元。

这里暂时不讨论数据库系统如何具体的解决这些问题，在实际实现中这是一个非常复杂的数据存储问题，不是一篇博客就能说得清楚的，从应用开发层面来说暂时也没有必要深入了解这些的必要，因为解决这些问题有一套严格的标准，从应用开发层面来说我们只需要了解标准的解决方案，标准适用什么场景，在什么情况下可能出现什么问题，根据标准和数据库系统的实际标准实现我们可以怎么设计我们的数据库。

一般这个案例的三个步骤都会放到一个事务中来执行，使用事务并不能保证这三个步骤就可以正常的完整的执行，除非系统通过严格的ACID测试。

ACID：数据库事务正确执行的四个基本要素，分别是指（原子性：atomicity、一致性：consistency、隔离性：isolation、持久性：durability）。

原子性：一个事务是一个不可分割的工作单位，其中的操作要么提交成功，要么失败回滚，不可能执行其中一部分操作。比如案例中的情况一，步骤2执行失败就不会再执行步骤3。

一致性：数据库总从一个一致的状态转换到另一个一致性的状态。比如案例中情况二在步骤2和步骤3之间系统奔溃，支票账户也不会损失200美元，因为步骤3没有执行事务不会提交，至于这是如何实现的，后面的内容会解释。

隔离性：一个事务所做的修改在最终提交前，对其他事务是不可见的。比如案例中步骤2执行完成，但还没有执行步骤3时，另一个用户进程来查询这个支票记录，其查询的记录是并没有减去200美元的记录。但是，这只能说是理论上来说，这关乎到事务的隔离级别，后面会具体解释。

持久性：一旦事务提交，其所做的修改就会永久保存到数据库中。即使这时候系统崩溃，修改的数据也不会丢失。持久性任然分为很多个级别，有些持久性策略能够非常强的保障数据安全，而有些未必，所以持久性也还只是一个相对而言，不然数据备份又还有什么意义呢？

 三、事务日志

事务日志是用来存储对数据库进行更改记录的文件，记录插入、更新、删除、提交、回退和数据库模式变化，是备份和恢复数据的重要组件，也是SQL Remote或[复制代理]复制数据所必须的。

可以说事务日志是实现事务的基础，那到底什么是事务日志呢？事务日志又是如何工作的呢？

事务日志包含：redo log(重做日志)、undo log(回滚日志)、log group(日志组)。

redo log：Mysql会将事务中的sql语句涉及到的所有数据操作先记录到redo log中，然后再将操作从redo log中同步到对应的数据文件中。也就是说在修改数据库文件中的记录之前，一定保证对应的所有修改操作已经记录到了redo log中。redo log又分为两部分：redo log buffer和redo file log。

• redo log buffer（重做日志缓冲）：缓冲日志写入内存，这比直接写入磁盘要快很多，但是内存无法满足持久性需求，如果这时候系统奔溃会导致数据丢失。
• redo log file（重做日志文件）：redo log file是将日志写入磁盘，相比redo log buffer要慢许多，但是写入磁盘的日志能基本保证持久性的需求。

为了兼顾性能和持久性，一般是先将事务日志写入内存（redo log buffer），再将内存中的日志同步到磁盘（redo log file），即便磁盘io操作相对内存要慢很多，但是相对数据库的离散写操作来说还是要快很多，因为redo log file是磁盘上一段连续的空间。当操作记录到redo log file中以后再同步到数据库文件中，这就是数据库事务写入数据的整个过程。

虽然将redo log buffer的数据写入到redo log file 中保证数据持久性，但是这样会极大降低性能，可以通过innodb_flush_log_at_trx_commit参数来修改从redo log buffer写入redo log file的策略，但这样会失去持久性，有可能会丢失数据，具体怎么刷写策略，根据具体实际情况权衡。

redo log 是物理日志，因为redo log 中记录的数据库对页面的操作，而不是逻辑上的增删改查，重做日志具有幂等性。

undo log：回滚日志可以理解为数据修改前的备份，如果事务进行到一半时，有部分SQL没有执行成功，数据库可以根据undo log进行撤销，将所有修改过的数据从逻辑上恢复到修改之前的样子，比如之前insert了100条数据，那就delete它们，如果update了50条数据，就在根据undo log去update它们，所以，undo log是逻辑日志，与redo log记录的页操作物理日志不同。

log group：重做日志组中包含多个重做日志，当日志组中一个log file写满，则会将redo log写入日志组中的下一个重做日志文件中，当日志组所有redo log file都写满，则将redo log再写入第一个redo log file中，覆盖原来的redo log，以便新的redo log写入。如果重做日志所在的设备崩溃，那么redo log 将有可能丢失，这样无法保证redo log 在任何时候都是可用的，所以log group还支持日志组镜像，一般将log group放在有冗余能力的设备上，比如radi1。

redo log 存储于重做日志中，undo log则存储在数据库内部特殊段中，这个段被称为undo段（undo segment）,undo段位于共享表空间中。不管redo log还是undo log都是恢复数据库的手段。

MySQL中，innodb存储引擎支持事务，myisam存储引擎不支持事务，不管redo log 或undo log，都是innodb的产物，而MySQL中还有另一种重要的二进制日志，也就是binlog，它时间里MySQL主从复制环境所必须的日志，但是binlog并不是存储引擎层面的日志，MySQL数据库中任何存储引擎对于数据库的更改都会产生二进制日志。

redolog与binlog写入磁盘的时机也不同，innodb的redo log在事务进行时不断写入redo log file，binlog只在事务提交完成后进行依次磁盘写入。

查看Innodb存储引擎日志配置参数：

show global variables like '%innodb%log%'

具体日志参数说明可以参考这篇博客：http://www.zsythink.net/archives/1216，关于详细的信息也可以参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

 四、事务的隔离级别

通过上一节已经了解到了事务日志可以解决事务的原子性、持久性，事物ACID剩一个基本要素（隔离性）没有涉及，那什么是隔离性呢？为什么需要隔离，隔离级别有什么区别？

事务隔离性是指事物内部的操作与其他事务是隔离的，并发执行的各个事物之间不能相互干扰。

• READ UNCOMMITTED（未提交读）
• READ COMMITTED（提交读）
• REPEATABLE READ（可重复读）
• SERIALIZABLE（可串行化）

 4.1：READ UNCOMMITTED（未提交读）

未提交读表示事务中对数据的修改即使事务没有提交，对其他事务也都是可见的。假设一个用户的数据操作进程开启事务A，另一个用户进程同时开启事务B，A事务中执行了一个数据修改语句，但事务还未提交时，B事务紧接着就去读取刚刚A事务执行修改的数据，在READ UNCOMMITTED隔离级别读取的数据是刚刚事务A修改的数据。

从性能上来说READ UNCOMMITTED不会比其他级别好太多，但缺乏其他级别的很多好处，除非真的有必要的一般很少使用。

脏读并不是指读取其他事务已执行SQL数据修改语句，但未提交事务的数据就一定会发生，而是指当读取的数据由于以执行修改数据但未提交的事务发生回滚，这时候才被称为脏读，脏读实际上是指读取的数据与写入数据库的数据不一致的情况。

4.2：READ COMMITTED（提交读）

一个事务开始时，只能“看见”已经提交的事务所作的修改，也就是说事务所做的任何修改，在提交前对其他事务都是不可见的。这个级别有时候也叫做不可重复读，因为在事务内部两次同样的查询，可能会得到不一样的结果。

4.3：REPEATABLE READ（可重复读）

该级别保证了在同一个事务中多次读取同样记录的结构是一致的，但是理论上，可重复读隔离级别还是不能解决另一个幻读的问题。

幻读：当事务在读取某个范围内的记录时，另外一个事务又在该范围插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读问题。

可重复读是MySQL的默认事物隔离级别。

4.4：SERIALIZABLE（可串行化）

可串行化是最高的隔离级别。它通过强制事物串行执行，避免了前面说的幻读的问题。简单的说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

———————————————————————————————————————

ANSI SQL隔离级别：

 五、死锁

死锁是指两个或多个事物在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事物按照不同顺序锁定锁定资源时，就可能导致死锁。多个事务同时锁定同一个资源时，也会产生死锁。比如下面这个示例：

事务1：

start transaction ;
update t_employee set sal=5000 where ename='JONES';
update t_employee set sal=4500 where ename='BLAKE';
commit ;

事务2：

start transaction ;
update t_employee set sal=6000 where ename='BLAKE';
update t_employee set sal=3800 where ename='JONES';
commit ;

可以尝试启动两个控制台进入数据库然后按照以下顺序执行语句：

 执行到第三步时，两个进程都会等待一会儿，然后报超时错误：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

为了解决死锁的问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统比如InnoDB存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。除了这种解决方法以外就是当查询达到锁超时的设定后放弃锁请求，这种方式不太友好。InnoDB目前处理死锁的方法是，将有最少行级排他锁的事务进行回滚。

锁的行为和顺序是和存储引擎相关的，以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致。

上面示例测试的表事务隔离级是REPEATABLE READ，存储引擎是InnoDB。

 

 关于死锁更详细的解析和解决方案可以参考这篇博客：https://blog.csdn.net/qq_36132127/article/details/81272293

 六、MVCC

MVCC全称Multi-Version Concurrency Control，即多版本并发控制协议。MySQL的大多数事务型存储引擎都不是简单的行级锁，为了提升并发性能的，它们一般都同时实现了并发控制。而且不仅仅是MySQL，包括Oracle、PostgreSQL等其他数据库系统也都实现了MVCC，但各自的实现机制不尽相同，因为MVCC没有统一的实现标准。

MVCC很多情况下避免了枷锁操作，因此开销更低。虽然实现机制不同，但大都实现了非阻塞的读操作。写也只锁定必要的行。MVCC是通过保存数据在某个时间点的快照来实现，不管一个事务要执行多长时间，事务内部每个时间点看到的数据都是一致的。

前面说过MVCC在不同存储引擎实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的，这两个列分别保存行的创建时间和过期时间（或称为“删除时间”）。而且存储并不是实际时间值，而是系统版本号（system version number）。下面来看看InnoDB的SQL操实际是如何来操作这两个隐藏列的：

INSERT ：为新插入的每一行保存当前系统版本号作为行的版本号。

DELETE：为删除的每一行保存当前系统版本号作为行的删除标示。

UPDATE：插入一行新的记录，保存当前系统版本号作为行的版本号，同时保存当前系统版本号到原来的行作为删除标识。（也就是说修改数据实际上是新增一行记录，删除原来的记录）。

SELECT：查找版本早于或等于当前事务版本号的数据行，并且删除把呢不能还未定义。意思就是查找早于当前事务版本的记录，这是在事务内部未对查找的记录删除或修改；如果在查询前当前事务新增的记录是可以被查询到的，在查询前事务内部修改的记录也是可以查询到的，查询前事务内部的删除也可以被查看到。

根据上面的说明，也就是说可重复读并不意味着在事物内部两次相同的读语句可以完全获得相同的结果。

 六、MySQL的事务

在MySQL中默认情况下，每一条SQL语句都当作一个单语句事务，并默认自动提交，可以通过下列语句查看全局和当前会话是否开启自动提交功能。

show global variables like 'autocommit%';
show session variables like 'autocommit%';

并且可以手动关闭当前会话的默认提交：

set autocommit=0;

如果手动关闭了默认提交，执行SQL数据操作语句后就需要手动使用commit提交。

 

 除了默认SQL语句自动默认采用事务机制，可以通过手动开启事务并提交，下面是MySQL事务控制语句的语法：

START TRANSACTION | BEGIN [work]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

START TRANSACTION 或BEGIN：表示开启一个事务。当由于在存储过程中BEGIN END作为SQL语句的包裹关键字，所以为了区分一般使用START TRANSACTION作为事务开启语句。

COMMIT或COMMIT WORK：表示提交事务。也就是说START TRANSACTION 与COMMIT之间的SQL语句对数据的操作称为永久性操作。

ROLLBACK或ROLLBACK WORK：表示事务回滚。也就是撤销事务中在回滚语句之前的数据操作，之后的数据不会被撤销，这一点需要注意。

除了以上三个语句以外，还可以使用标识符用来控制回滚。

SAVEPOINT #表示创建标识符
ROLLBACK TO SAVEPOINT#表示回滚到这个标识符的记录
RELEASE SAVEPOINT #表示删除一个保存点

本来想着写一个示例，但是在感觉没有这个必要了，如果需要的话可以参考下面这两篇博客：

http:/www.zsythink.net/archives/1216

https://www.cnblogs.com/Yiran583/p/7125455.html