mysqlProxy 开启事务会从主库读数据吗

文章目录

• 一、前言
• 二、事务的ACID特性

• 2.1 事务
• 2.2 理论：事务的ACID特性
• 2.3 银行转账解释事务的ACID四特性

• 三、事务的隔离机制

• 3.1 隔离的对象(互斥性的数据库资源)
• 3.2 四种隔离级别和三种错误
• 3.3 四种隔离级别和三种错误

• 3.3.1 序列化读

• 3.3.1.1 隔离级别-序列化读-将整个数据库作为互斥资源（底层锁：数据库锁，先对目标数据库加锁，然后对这个数据库中的表读写）
• 3.3.1.2 隔离级别-序列化读-使用数据库的表作为互斥资源(底层锁：表级锁，先对目标表加锁，然后对这个表读写)

• 3.3.2 隔离级别-可重复读

• 3.3.2.1 隔离级别-可重复读：行级锁
• 3.3.2.2 隔离级别-可重复读造成的问题：幻读

• 3.3.3 读已提交(READ_COMMITTED)

• 3.3.3.1 读已提交-行级锁的commited
• 3.3.3.2 读已提交造成的问题：虚读(不可重复读)

• 3.3.4 读未提交(READ_UNCOMMITTED)

• 3.3.4.1 读未提交-行级锁的uncommited
• 3.3.4.2 读未提交造成的问题：脏读

• 四、事务其他知识

• 4.1 事务的死锁
• 4.2 事务日志 undolog保证原子性 redolog保证持久性（重点004）
• 4.3 MySQL中的事务应用

• 4.3.1 实践：自动提交(AUTOCOMMIT)的开启与关闭
• 4.3.2 实践：设置事务隔离级别
• 4.3.3 问题情景：在事务中混合使用存储引擎
• 4.3.4 两阶段锁定协议：隐式锁定和显式锁定
• 4.3.4 触发器和存储过程

• 4.3.4.1 介绍一下数据库事务存储过程( 特定功能的SQL语句集)？
• 4.3.4.2 谈一谈触发器(一段能自动执行的程序 特殊的存储过程)？

• 4.3.5 数据库并发控制和数据库锁

• 4.3.5.1 数据库并发控制（乐观锁、悲观锁、时间戳）
• 4.3.5.2 数据库锁（行级锁、表级锁、页级锁）

• 五、面试金手指

• 5.1 事务起手式
• 5.2 事务ACID

• 5.2.1 事务ACID
• 5.2.2 银行转账事务ACID
• 5.2.3 小结：ACID四特性底层实现

• 5.3 事务四种隔离级别

• 5.3.1 重点：隔离的对象(互斥性的数据库资源) + 间隙锁

• 5.3.1.1 隔离的对象(互斥性的数据库资源)
• 5.3.1.2 间隙锁(InnoDB是可重复读隔离级别解决幻读错误的神器)

• 5.3.2 面试金手指：四种隔离级别与三种错误

• 5.3.2.1 问题1：解释四种隔离级别
• 5.3.2.2 问题2：四个概念：隔离级别、资源互斥粒度、事务并发能力、数据一致性隔离级别的本质就是资源互斥粒度，就是数据库锁级别？
• 5.3.2.3 问题3：解释一个表：四种隔离级别和三种错误
• 5.3.2.4 实践4：设置事务的隔离级别 + 启动事务
• 5.3.2.5 底层5：事务试图，事务隔离的底层实现

• 5.3.2.5.1 概要：事务试图和表视图，事务试图是事务隔离的底层实现
• 5.3.2.5.2 详细1：事务快照
• 5.3.2.5.3 详细2：版本链(事务试图的基石)：trx_id和roll_pointer
• 5.3.2.5.4 详细3：事务试图ReadView，生成事务试图ReadView的时机不同(事务隔离级别：已提交读和可重复读的本质不同)
• 5.3.2.5.4 详细4：MVCC(从源头上讲，MVCC机制：多版本并发控制)

• 5.4 事务附加三个

• 5.4.1 事务的死锁(类比Java并发死锁)
• 5.4.2 事务日志（undolog撤销保证事务原子性，redolog保证事务持久性）
• 5.4.3 mysql中的事务应用

• 5.4.3.1 实践：自动提交(AUTOCOMMIT)的开启与关闭
• 5.4.3.2 实践：设置事务隔离级别
• 5.4.3.3 问题情景：在事务中混合使用存储引擎
• 5.4.3.4 两阶段锁定协议：隐式锁定和显式锁定

• 六、尾声

一、前言

事务的ACID四个特性：原子性由undo log日志保证，持久性由“redo log日志 + double buffer write(双写缓冲)” 保证，隔离性由 LBCC基于锁的并发控制 +MVCC多版本并发控制 保证，一致性包括数据库自带的完整性约束(如事务执行前后，主键必须唯一)和业务层面的数据完整性约束(如事务执行后，A账户的余额减少1000，B账户的余额增加500，或者A账户的余额为500却减少了1000等)，数据库层面的数据完整性只要保证原子性、持久性、隔离性就可以实现，业务层面的数据完整性，比如银行转账、电商下单，就要结合业务层面来看了。

二、事务的ACID特性

2.1 事务

含义：事务就是一组原子性的SQL查询,或者说一个独立的工作单元。如果数据库能够成功地对数据库应用该组查询的全部语句,那么就执行该组查询。如果其中有任何一条语句因为渣或其 他原因无法执行,那么所有的语句都不会执行。也就是说,事务内的语句,要么全部执行成功(全部执行成功用redo保证持久性),要么全部执行失败(有一个执行失败用undo保证原子性)。

从存储介质上说，虽然数据库和文件都是存放在磁盘上，但是，事务是数据库存储区别于文件存储的重要特征之一，事务会把数据库库从一种状态转换为另一种状态。在数据库提交工作时，可以确保要么所有修改都保存了，要么所有修改都不保存。

在缺省模式下，MYSQL 是 autocommit 模式的，当表类型为InnoDB，也可以使用手动事务。

在缺省模式下，MYSQL 是 autocommit 模式的，所有的数据库更新操作(insert update delete)每条SQL语句自带事务，且自动提交事务（但是select是不带事务的）。但是如果你的 MYSQL 表类型是使用 InnoDB 的话，你的 MYSQL 就可以使用手动事务处理,使用 SET AUTOCOMMIT=0 就可以使 MYSQL 允许在非 autocommit 模式，在非autocommit 模式下，你必须使用 COMMIT 来提交你的更改，或者用 ROLLBACK 来回滚你的更改。

InnoDB两种手动开启事务的方式：

START TRANSACTION;   // 开始事务
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;  // 事务中，第一个sql语句
UPDATE table2 SET summmary=@A WHERE type=1;  // 事务中，第二个sql语句
ROLLBACK;/COMMIT;   // 回滚或提交事务，两种sql语句做一个原子操作一并执行

BEGIN;    // 开始事务
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;  // 事务中，第一个sql语句
UPDATE table2 SET summmary=@A WHERE type=1;  // 事务中，第二个sql语句
ROLLBACK;/COMMIT;  // 回滚或提交事务，两种sql语句做一个原子操作一并执行

小结：InnoDB类型的表，两种开始事务的方式，要么begin，要么start transaction。

使用navicat或者jdbc，当连接断开或会话session关闭，事务就会rollback。

2.2 理论：事务的ACID特性

事务的特性：原子性atomicity、一致性consistency、隔离性isolation、持久性durability，事务的四种特性取英文首字母为ACID，这就是事务的ACID四种特性。

原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。（金手指：原子性通过回滚来实现操作的原子性，而回滚又是通过undolog实现的，所以说，事务的原子性是通过undolog日志实现的）

一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性：在事务完成以后（即事务成功提交之后），该事务所对数据库所作的更改便持久的保存在数据库之中，持久化就是保存到磁盘中，此时即使系统崩渍,修改的数据也不会丢失，持久性由redolog保证，即使flush刷盘是异步的，也不会被回滚(不回滚是重点)（金手指：事务成功提交之后，不会再回滚，回滚是通过undolog实现的，undolog两作用：撤销回滚+mvcc多版本控制）。

原子性和持久性的统一：原子性中，事务执行错误就回滚；持久性中，事务执行正确并完成，就不会再回滚了。

ACID存在的意义(提供数据库正确执行的理论依据) + ACID解释

ACID存在的意义(提供数据库正确执行的理论依据)：ACID是数据库事务 transanction 正确执行的四个基本要素，为数据库事务正确执行提供理论依据，只有同时满足这四个基本要素，才能保证数据库事务一定可以正确执行。

ACID解释

原子性：事件执行过程中不能被打断，所以从原子性知道，事务只有两种状态，事务执行前，事务执行后。原子化保证两点：
（1）如果事务失败，就回滚，原子性通过回滚来实现操作的原子性，而回滚又是通过undolog实现的，所以说，事务的原子性是通过undolog日志实现的。
（2）如果事务成功，就没事。

一致性：由原子性知道，事务只有两种状态，事务执行前，事务执行后，在这两种状态中，数据库的完整性约束没有被破坏。

一致性保证两点：
（1）事务执行中，事务执行失败，磁盘中一定不会持久化；
（2）事务成功执行完成，磁盘一定会持久化。

隔离性：事务的隔离性的要求就是因为事务存在并行执行，如果没有事务的并发执行，就没有隔离性，四个隔离级别就是对事务并发执行的安全性和效率的相互妥协。
隔离性保证五点：就是3.3.5面试金手指的那些东西。
持久性：就是保存到数据库中，这是一种持久化介质，内存就不是一种持久化介质了。
持久性保证两点，
（1）事务完成提交，就持久化到磁盘上了，由redolog重做日志保证。
（2）事务成功提交，就不会回滚了，由undolog撤销日志保证，因为回滚就是undolog实现的。

2.3 银行转账解释事务的ACID四特性

银行应用是解释MySQL事务的一个经典例子。假设一个银行的数据库有两张表:支票(checking)表和储蓄( savings)表,现在要从用户Jane的支票账户转移200美元到她的储蓄账户,那么需要至少三个多骤：

1.检查支票账户的余额高于200美元；
2.从支票账户余额中减去200美元；
3.在储账户余额中增加200美元。

上述三个参骤的操作必须打包在一个事务中,任何一个步骤失败,则必须回滚所有的步骤。

可以用 START TRANSACTION语句开始一个事务,然后要么使用COMMIT提交事务将修改的数据持久保留,要么使用ROLLBACK撤销所有的修改,事务SQL的样本如下：

START TARNSACTION;     -- 第一句，InnoDB类型的表，手动开启事务两种方式：begin; start transaction;                                                         
SELECT balance FROM checking WHERE customer_id = 10233276;                   --   第二句
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276; --   第三句
UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;  --   第四句
COMMIT;                                                       --   第五句 rollback/commit

情景1：原子性：如果执行到第四条话句时服务器崩溃了，会发生什么?
支票表上少了200美元，但是存储表中没有增加200美元，因为在执行存储表增加200美元的时候报错了，如果没有事务失败的回滚机制，就是没有事务的原子性，就造成出错。
原子性通过回滚来实现操作的原子性，而回滚又是通过undolog实现的

情景2：一致性，当执行完第三条语句、第四条语句还未开始时，系统崩溃？
事务有一致性( consistency)， 数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中，一致性确保了即使当执行完第三条语句、第四条语句还未开始时，系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。

情景3：隔离性，当执行完第三条语句、第四条语句还未开始时，另外一个进程要注销所有余额为0的账户，那么结果可能就是银行在不知道这个逻辑的情况下白白给了Jane200美元。
如果没有事务的隔离性，就无法保证事务并行的安全。
有了隔离性，一个事务所做的修改在最终提交以前,对其他事务是不可见的。当执行完第三条语句、第四条语句还未开始时,此时有另外一个账户汇总程序开始运行,则其看到的支票账户的余额并没有被减去200美元。

情景4：持久性，成功执行完成后，数据库崩溃，没有来得及写入磁盘？
事务有持久化机制，这个持久化机制是由redolog日志保证的，没来得及刷盘持久化，redolog日志里面也一定有。

存储引擎：Innodb和MyISAM

程序员可以根据业务是否需要事务处理,来选择合适的存储引擎。对于一些不需要事务的查询类应用,选择一个非事务型的存储引擎(如MyISAM),可以获得更高的性能,即使存储引擎不支持事务,也可以通过LOCK TABLE锁表语句为应用提供一定程度的保护,这些选择程序员都可以自主决定。

三、事务的隔离机制

3.1 隔离的对象(互斥性的数据库资源)

第一，宏观上，隔离的是客户端的操作：隔离性,是指不同的客户端在做事务操作时，理想状态下，各个客户端之间不会有任何相互影响，好像感知不到对方存在一样。

第二，微观上，实际隔离的是客户端操作的互斥性的数据库资源，真正隔离的对象在实现上是数据库资源的互斥性访问，隔离性就是通过数据库资源划分的不同粒度体现的。

无论是Java代码中的多线程锁，还是MySQL中的行锁(包括排他锁写锁，共享锁读锁)、表锁，都是对互斥资源的争夺，Java里面这个互斥资源是临界代码块，MySQL里面这个互斥资源是锁。

3.2 四种隔离级别和三种错误

在SQL标准中定义了四种隔离极别,每一种级别都规定了一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。较低级别的隔离通常可以执行更高的并发,系统的开销也更低；较高级别的隔离保证了较高的安全性，但是可以执行的并发变小，系统开销更高。先引入几个数据库事务隔离相关问题的含义：

脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据。
不可重复读（虚读）：指一个线程中的事务读取到了另外一个线程中提交的update的数据。
幻读：指一个线程中的事务读取到了另外一个线程中提交的insert的数据。

一个是事务读取到其他事务未提交的修改，造成前后两次读取的数据不一致，这种错误称为脏读，因为未提交的数据页还在内存里面，还没有执行刷脏操作，还没有刷新到磁盘里面，这种页称为脏页，这种数据称为脏数据，所以这种错误称为脏读。

一个是事务读取到其他事务已提交的修改，造成前后两次读取的数据不一致，这种错误称为虚读/不可重复读。

一个是事务读取到其他事务已提交的插入，造成前后两次读取的数据不一致，只有插入造成的一个事务中前后两次读取不一致，才称为幻读。

脏读和不可重复读的区别：读取的数据是否已经进行了刷脏操作，即更新操作是否提交。
不可重复读和幻读的区别：一个事务中前后两次读取不一致，是否由另一个事务的插入造成。

下面简单地介绍一下四种隔离级别。

READ UNCOMMITTED(未提交读)
在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。另一个事务可以读取到当前事务未提交的数据,这也被称为脏读( Dirty Read),这个级别会导致很多问题,从性能上来说, READ UNCOMMITTED不会比其他的极别好太多,但却缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用。

READ CONIIED(提交读)
大多数数据库系统的默认都是 READ COMITTED(但 MySQL不是),READ COMMITTED满足前面提到的隔离性的简单定义:一个事务开始时,只能“看见”已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。

REPEATABLE READ(可重复读)
已提交读解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的,但是理论上,已提交读隔离级别还是无法解决另外一个幻读 ( Phantom Read)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行( Phantom Row). InnoDB和 XtraDB存储引擎通过多版本并发控制(MvC, Multiversion Concurrency Control)解决了幻读的问题,。可重复读是MySQL的默认事务隔离级别。

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

串行化走向了另一个极端，锁住整个表，保证任何一个时候只有一个事务来占用表，完全不使用并发，自然不会造成任何的事务并发问题。

一表小结：

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

上表知识ASCII的92标准，实际上，对于MySQL/InnoDB来说，默认的隔离级别是可重复读，这是因为由于InnoDB间隙锁的存在，已经可以保证不会出现幻读，既保证 读一致性，也保证效率。

小结：级别越高，数据越安全，但性能越低。

不可重复读与幻读辨析：不可重复读与幻读比较相似，都是在一个事务中多次读取到不同的数据，核心在于insert.

不可重复读：
又称虚读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，得到了不同的结果。
一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问(即读写)该数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

幻读：
当事务不是独立执行时发生的一种现象，是指事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来影响事务A的读取结果集。
一种更易理解的说法是：第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.一般解决幻读的方法是增加范围锁RangeS，锁定检锁范围为只读，这样就避免了幻读。简单来说，幻读是由插入或者删除引起的。

不可重复读（虚读）和幻读的差别：
大致的区别在于不可重复读是由于另一个事务对数据的更改所造成的，而幻读是由于另一个事务插入或删除引起的。
从总的结果来看, 似乎两者都表现为两次读取的结果不一致.
从控制的角度来看,两者的区别就比较大：对于前者, 只需要锁住满足条件的记录；对于后者, 要锁住满足条件及其相近的记录。

3.3 四种隔离级别和三种错误

3.3.1 序列化读

3.3.1.1 隔离级别-序列化读-将整个数据库作为互斥资源（底层锁：数据库锁，先对目标数据库加锁，然后对这个数据库中的表读写）

如果将整个数据库当做互斥资源的访问，那么，这种访问会有如下性质：

规定同一时间内只能有一个客户端连接数据库进行事务操作，在这个客户端完成事务之前，其他客户端不能对数据库进行事务操作。

当客户端访问数据库时，各个客户端以互斥的方式进行访问。交互方式如下图所示：

这种级别的隔离方式时最理想的，肯定不会存在不同的客户端事务相应影响的情况，因为，所有的客户端在事务操作时，都是以排队的形式进行的。

数据库除了在理论上的严谨性之外，还要看它的实用性。 下面我们介绍下数据库性能的一个衡量标准：TPS: 单位时间内的事务数(Transactions Per Second)，TPS越高，表示数据库的性能越好。

注意：TPS是衡量四种不同隔离级别的性能的关键指标，假设每个客户端的每次事务操作耗时为 T 秒，并且期间没有空闲，那么此时数据库的最大TPS能力就是1/T。
最大TPS = 1 / T (T 为客户端的平均事务操作时间)
例如：T = 10ms, 那么数据库此时的TPS值 为 1 / 0.01 = 100， 即数据库每秒能够完成100个事务操作

问题：使用数据库级别作为互斥资源，有这么必要吗？
回答：使用数据库级别作为互斥资源访问，确实能够完全保证事务的隔离性；但是，在实际的应用场景中，使用这种粗粒度的互斥资源没有必要。

举例：假设数据库 mall 中有两张表：t_user、t_order; 而外部共有4个客户端A、B、C、D。其中，A 和B客户端只操作了t_user表,C 和D客户端只操作了t_order表。

从互斥资源的角度上来讲,客户端访问互斥资源的情况，分别有两对互斥：A <–t_user– B 、C<–t_order–D，在做事务隔离控制时，没有必要使用数据库作为互斥资源；可以将互斥资源进行细分，细分到表这一层级。

3.3.1.2 隔离级别-序列化读-使用数据库的表作为互斥资源(底层锁：表级锁，先对目标表加锁，然后对这个表读写)

接着上面的例子，我们将数据库的表作为互斥资源，细分后的交互方式如下所示：

当我们把锁级别放到表级别之后，在时序操作上，会有两个资源互斥组t_user-[A,B]、t_order-[C,D], 这两个互斥组之间不会受到相互影响，可以并行处理，并行的结果如下图所示：

由于将资源的互斥级别 从数据库级别细化到表级别，数据库的TPS数量也提升了不少，下面我们简单估算一下满负荷状态下的TPS，还是假设客户端的平均事务操作的耗时为T，资源互斥组数量为N，那么：

最大TPS = （1 / T）* N
本例中，若T= 10ms ，N = 2，那么：TPS = (1 / 0.01) * 2 = 200

和将数据库作为互斥资源对比，可以看到，有如互斥粒度降到表级别，TPS也跟着提高。

注意：在真实的事务操作中，可能一个客户端事务会操作多张表，那这多张表的任意一张表都会被当做互斥资源。

在目前主流数据库的实现上，基本上都提供了锁表的方式提供资源互斥资源访问，通过锁全表的方式进行的事务隔离处理，在操作时序上，是排队性质进行的，这种事务隔离的级别最高，即：序列化读(SERIALIZABLE READ)。

小结：我们可以简单地来理解序列化读的实现方式：锁全表，先对目标表加锁，然后对这个表读写

3.3.2 隔离级别-可重复读

3.3.2.1 隔离级别-可重复读：行级锁

锁全表的方式会导致对同一个表操作的客户端事务操作变成排队性质的序列化操作。现在看下另外一个场景，假设现在有客户端A和客户端B，在事务操作时，共同使用一张表T_USER,但是他们操作的行信息有所不同，如下：

上图中，虽然客户端A和客户端B 以互斥的方式访问表T_USER，但是操作的数据行记录并没有真正的互斥，那我们可以继续将锁的粒度细化，从锁表这一级，再次细化到锁行记录这一级，这将进一步提高系统的并发处理能力。经过行锁细化后，其隔离级别就降到了可重复读。

将上述的例子展开，通过模型的方式体现，如下图所示：

客户端A和客户端B 同时尝试访问相同的行数据；而客户端C和客户端D也是同时尝试访问相同的行数据。在此竞争过程中，可以看到，最多可以有两个客户端可以同时访问表T_USER,和序列化读相比，整个客户端的并发量又提高了一个量级！

用客户端时序关系表示如下：

看到这个结果，是不是有这样的感觉：哇塞，既然使用行锁并发能力这么高，为什么还要 锁表方式的序列化读(SERIALIZABLE READ)？解答这个问题之前，我们来看下这种行锁方式有什么问题。

3.3.2.2 隔离级别-可重复读造成的问题：幻读

通过行锁的方式，能够锁定客户端锁操作的行；而在事务进行的过程中，可能会往对应的表中插入新的数据，而这个插入新的数据，起初并不数据锁定范围(造成幻读的根本原因)，所以，使用SQL语句操作数据库数据时，可能会返回更多的满足条件的数据，加入新的行锁，如下图所示：

如上图所示：在同一个事务内，完全相同的两次查询，返回的记录数不一致，好像多读了数据一样，这种情况，称为幻读(Phantom Read)

使用这种行锁的方式进行资源隔离的方式，在数据库隔离级别上被称为 可重复读 （REPEATABLE READ）

注意：虽然使用行锁互斥的方式进行数据库操作，但是会出现幻读的情况，避免幻读的方式，可以使用表级锁—即提高事务的隔离界别—序列化读(SERIALIZABLE READ)

3.3.3 读已提交(READ_COMMITTED)

3.3.3.1 读已提交-行级锁的commited

实际上，数据库在实现原子性(Atomic)时，对于某一表的特定行，其实有两个状态：Uncommited、Commited,我们将资源在行数据的基础上继续细分，如下图所示：

其实，上面讲原子性的时候，就说了，根据原子性，将一个数据表行记录分为未提交和已提交两个状态。

为了进一步提高数据库的并发能力，如上图所示，将在某一行数据上，使用读写分离锁的机制：

虽然客户端A B C D都要操作同一个表中的同一个行记录，但是

客户端B和客户端D对这个行记录进行读操作，使用读锁读取数据，读锁是共享锁，所以可以同时进行；

客户端A和客户端C对这个行记录进行写操作，使用写锁写入数据，写锁是独占锁，数据库事务的写数据操作中，会存在两个环节：Uncommited— Commited,等到要真正commit的时候，再使用写锁以互斥的方式完成事务，把互斥访问资源的时机压缩的更短。

上述的客户端B和客户端D只读取已提交的数据的方式，在隔离级别中，被称为读已提交(READ_COMMITED)。

通过上述的流程，我们的数据库的并发能力又能提高一个量级。

3.3.3.2 读已提交造成的问题：虚读(不可重复读)

但是这个只是想象中的美好而已，接下来看它存在的问题。假设我们有如下的数据库操作：

上述的例子中，reader在一个事务中，相同的查询条件，返回的行记录是同一条，但是这一条的记录的AGE列值从18变成19,虽然是相同的行记录，但是内容不一致，这种现象叫做不可重复读(NO-REPEATABLE-READ)。

虽然读已提交(READ COMMITED)隔离级别的并发读能力提高了很多个量级，但是在一个事务内，会造成不可重复读(NO-REPEATABLE-READ)的情况。

读已提交的不可重复读现象对开发同学有什么启示？
不可重复读会导致一条行数据两次读取数据可能不一致，这就要求我们在数据库事务操作上，不可重复读解决方式：尽可能少用查询出来的结果作为参数执行后续的updateSQL 语句，尽可能使用状态机来保证数据的完整性 + 使用可重复读隔离级别。

3.3.4 读未提交(READ_UNCOMMITTED)

3.3.4.1 读未提交-行级锁的uncommited

上述的读已提交(READ_COMMITTED)的本质，是将资源互斥访问的粒度控制到 committed的行数据上，而实际上，还可以继续将资源互斥的访问粒度，细化到未提交(UNCOMMITED)的行数据上，如下图所示：

3.3.4.2 读未提交造成的问题：脏读

这种方式，由于更细化了资源锁的粒度，其客户端的并发能力又得到了进一步的提升。但是，与此同时，会存在新的问题—脏读现象,具体流程示例如下图所示：

如上图所示：客户端reader在事务的过程中，读取到了其他客户端updater尚未提交的数据，之后客户端reader 可能将其当做已经持久化的数据进行业务操作，而实际上，客户端updater可能将其数据回退（如果updater回退，reader就读到脏数据，如果updater不回退，reader读到的就不是脏数据）。

小结（记住这个就好了 隔离的都搞定了）：
对于序列化，没什么好说，锁库锁表，单数据库下一定是安全的，分布式数据库就不安全，要使用分布式事务。
对于可重复读，锁定行，但是新增加的行是没有锁定的，所以B事务的新增行insert操作可以插在A事务的两个读操作中间，让A事务出现幻读，同一个事务中取出的数据行不同，增多了，但是只有insert可以成功，delete无法成功，因为已有的数据行都被锁住了。
对于已提交读，读不加锁写加锁，所以，事务B的update操作可以插在事务A的两个读操作中间，使其产生不可重复读错误（注意写加锁，读操作和写操作不能插到写操作中间）
对于未提交读，都不加锁。连写操作也可以被打断，读操作中打断写操作，读取尚未被持久化的更新值，可能是脏数据，脏读。

四、事务其他知识

4.1 事务的死锁

死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循坏的现象。小结一句：持有资源的事务等待其他的事务资源，但自己又不释放资源。
情形一，当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁；
情形二，多个事务同时锁定同一个资源时,也会产生死锁。

例如,设想下面两个事务同时处理 StockPrice表，满足第二种情景：

事务1
    START TRANSACTION;
    UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2019-05-01';
    UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2019-05-02';
    COMMIT;

事务2
    START TRANSACTION;
    UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2019-05-02';
    UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2019-05-01';
    COMMIT;

事务的死锁的产生：两个或多个事务访问互斥资源，满足死锁四条件，就死锁了，用java并发死锁对比学习。

如果凑巧,两个事务都执行了第一条UPDATE语句,更新了一行数据,同时也锁定了该行数据,接着每个事务都尝试去执行第二条UPDATE语句,却发现该行已经被对方锁定,然后两个事务都等待对方释放锁,同时又持有对方需要的锁,则陷入死循环。除非有外部因素介入才可能解除死锁。

mysql死锁的解决：优先死锁检测，次之死锁超时，找到后事务回滚。
死锁发生前，死锁检测找到死锁逻辑：InnoDB存储引擎,能检测到死锁的循环依赖,并立即返回一个错误。这种解决方式很有效,否则死锁会导致出现非常慢的查询。
死锁发生前，死锁等待找到死锁逻辑：当查询的时间达到锁等待超时的设定后放弃锁请求,这种方式通常来说不太好。
死锁发生后，事务回滚：
InnoDB目前处理死锁的方法是，遇到死锁后，将持有最少行级排他锁的事务进行回滚(这是相对比较简单的死锁回滚算法)。死锁发生以后,只有部分或者完全回滚其中一个事务,才能打破死锁。对于事务型的系统（如innodb存储引擎）,这是无法避免的,所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

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

4.2 事务日志 undolog保证原子性 redolog保证持久性（重点004）

小结：事务日志（要记忆的东西，一句话小结）
mysql日志系统有三种日志，和事务相关的只有两种，undo撤销回滚保证事务原子性，redolog保证事务持久性。

事务日志可以帮助提高事务的效率。使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘。

事务日志采用的是追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多(kafka也是这样写磁盘的)。事务日志持久以后,内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的,我们通常称之为预写式日志( Write-Ahead logging),修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数据。具体的恢复方式则视存储引擎而定。

4.3 MySQL中的事务应用

MySQL提供了两种事务型的存储引擎：InnoDB和NDB Cluster。

记住1：最常用的两种存储引擎，myisam不支持事务，innodb支持事务；
记住2：mysql中，不仅仅innodb支持事务，支持事务的还有NDB Cluster。

4.3.1 实践：自动提交(AUTOCOMMIT)的开启与关闭

MySQL默认采用自动提交( AUTOCOMMIT)模式,也就是说,如果不是显式地开始一个事务,则每个查询都被当作一个事务执行提交操作,在当前连接中,可以通过设置 AUTOCOMMIT变量来启用成者禁用自动提交模式。

mysql SET AUTOCOMMIT = 1;  // 启动事务自动提交
mysql SET AUTOCOMMIT = 0; // 关闭事务自动提交

1或者ON表示启用,0或者OFF表示禁用。

当AUTOCOMMIT=0时,禁用事务自动提交，所有的查询都是在一个事务中,直到显式地执行COMMIT提交或者ROLLBACK回滚,该事务结束,同时又开始了另一个新的事务。

使用SET AUTOCOMMIT 命令设置自动提交事务的启动与关闭
注意1，修改AUTOCOMMIT对myisam类型非事务型的表,不会有任何影响。对这类表来说，因为没有COMMIT或者ROLLBACK的概念,所以是一直处于AUTOCOMMIT启用的模式。
注意2，还有一些命令,在执行之前会强制执行 COMMIT提交当前的活动事务。
如1：在数据定义语言(DDL)中,如果是会导致大量数据改变的操作,比如 ALTER TABLE。如2：还有LOCK TABLES等其他语句也会导致同样的结果。
如果有需要,请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。

4.3.2 实践：设置事务隔离级别

小结：MySQL可以通过执行 SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效,可以在配置文件中设置整个数据库的隔离级别,也可以只改变当前会话的隔离级别

mysql SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL能够识别所有的4个ANSI隔离级别, InnoDB引擎也支持所有的隔离级别。

4.3.3 问题情景：在事务中混合使用存储引擎

MySQL服务器层不管理事务,事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。

解释：mysql架构分为两层：mysql服务层和mysql存储引擎层

mysql服务层：连接器、缓存、分析器、优化器、执行器

mysql服务层下面才是存储引擎层

情景：在事务中混合使用了事务型和非事务型的表(例如 InnoDB和 MyISAM表)
（1）在正常提交的情况下不会有什么问题。
（2）在异常提交的情况下产生问题，事务出错表示该事务需要回滚,myisam类型的非事务型的表上的变更就无法撒销,这会导致数据库处于不一 致的状态,这种情况很难修复,事务的最终结果将无法确定。
小结：所以,创建表的时候为每张表选择合适的存储引擎非常重要。
特别注意：在非事务型的表上执行事务相关操作的时候, MySQL通常不会发出提醒,也不会报错。有时候只有回滚的时候才会发出一个警告:“某些非事务型的表上的变更不能被回滚”。但大多数情况下,对非事务型表的操作都不会有提示。

4.3.4 两阶段锁定协议：隐式锁定和显式锁定

小结：InnoDB采用的是两阶段锁定协议( two-phase locking protocol)。在事务执行过程中,随时都可以执行锁定,锁只有在执行COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻被释放。

前面描述的锁定都是隐式锁定, InnoDB会根据隔离级别在需要的时候自动加锁。

另外, InnoDB也支持通过特定的语句进行显式锁定,这些语句不属于SQL规范：

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

MySQL也支持LOCK TABLES和UNLOCK TABLES语句,这是在mysql服务器层实现的,和存储引擎层无关。它们有自己的用途,但并不能替代事务处理。如果应用需要用到事务,还是应该选择事务型存储引擎。

经常可以发现,应用已经将表从 MyISAM转换到InnoDB,但还是显式地使用LocK TABLES语句。这不但没有必要,还会严重影响性能,实际上 InnoDB的行级锁工作得更好。

4.3.4 触发器和存储过程

4.3.4.1 介绍一下数据库事务存储过程( 特定功能的SQL语句集)？

一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

存储过程优化思路：

1. SQL避免小循环：尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2. SQL避免自己创建大循环：查找语句尽量不要放在循环内。
3. 中间结果：中间结果存放于临时表，加索引，表可以加索引，加快速度。
4. 游标Cursor：少使用游标。sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
5. 事务：事务越短越好。sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极地。
6. 异常：使用 try-catch 处理错误异常。

4.3.4.2 谈一谈触发器(一段能自动执行的程序 特殊的存储过程)？

触发器是一段能自动执行的程序，是一种特殊的存储过程。

触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作时触发。诸如：update、insert、delete 这些操作的时候，系统会自动调用执行该表上对应的触发器。

触发器能够自动响应某种行为，所以对于必须对某种行为做出业务级别响应的情况，触发器很合适。

触发器可以分为两类：DML 触发器和DDL 触发器。DML触发器包含了用于对表或视图的insert、update、delete操作做出响应的T-SQL代码；DDL触发器对服务器或数据库事件做出响应而不是数据修改。

存储过程与触发器的区别
第一，触发器和存储过程都是 SQL 语句集，唯一的区别是执行方式不同，
触发器不能用 EXECUTE 语句调用，而是在用户执行 Transact-SQL 语句时自动触发（激活）执行。触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。
第二，触发器是在一个修改了指定表中的数据时执行的存储过程。触发器是特殊的存储过程。
第三，通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。比如，当对某一表进行诸如 UPDATE、INSERT、DELETE 这些操作时，SQLSERVER 就会自动执行触发器所定义的 SQL语句，从而确保对数据的处理必须符合这些 SQL 语句所定义的规则。

【触发器】在 Mysql 表中一共允许有六个触发器，如下：
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

4.3.5 数据库并发控制和数据库锁

4.3.5.1 数据库并发控制（乐观锁、悲观锁、时间戳）

并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳。

乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁就刚好相反，觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间戳就是不加锁，通过时间戳来控制并发出现的问题。

1、乐观锁
乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据，所以不加锁，操作成功则无事，操作失败则回退。

2、悲观锁

悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允许其他用户访问那部分数据。以上悲观锁所说的加“锁”，其实包括两种锁：排它锁（写锁）和共享锁（读锁）。

3、时间戳

时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字段也读出来，当写回去的时候，把该字段加1，提交之前 ，跟数据库的该字段比较一次，如果比数据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁机制，但是这种方法可以大大提高数据库处理的并发量。

4.3.5.2 数据库锁（行级锁、表级锁、页级锁）

1、行级锁

行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时，MySQL 会自动应用行级锁：

1. 锁定：INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT]; 语句允许用户一次锁定多条记录进行更新
2. 解锁：使用 COMMIT 或 ROLLBACK 语句释放锁。

2、表级锁

表级锁定包括表共享读锁（共享锁）与表独占写锁（排他锁）。表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。

3、页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。

表级锁速度快，但冲突多，
行级锁冲突少，但速度慢。

所以取了折衷的页级，一次锁定相邻的一组记录。BDB 支持页级锁。

Mysql 中有哪几种锁？三种：行级锁、表级锁、页级锁
MyISAM 支持表锁；InnoDB 支持表锁和行锁，InnoDb默认为行锁.
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

五、面试金手指

5.1 事务起手式

金手指：起手式三个问题（要记）
1、事务什么时候产生？
mysql中，两个最重要的存储引擎，myiasm中没有事务，innodb中才有事务。
记住1：最常用的两种存储引擎，myisam不支持事务，innodb支持事务；
记住2：mysql中，不仅仅innodb支持事务，支持事务的还有NDB Cluster。

2、为什么innodb中出现了事务？为什么需要事务？事务的好处？
事务的好处就是事务的四个特性，没有事务的时候做不到。
原子性：只存在事务执行前、事务执行后两种状态，事务执行不可打断，所以，可以将多个原子性sql语句放在一个事务中。这在没有事务以前是做不到的。
一致性：接上面知道，事务只有两种状态，事务执行前，事务执行后，在这两种状态中，数据库的完整性约束没有被破坏。这在没有事务之前是做不到的。
隔离性：事务的隔离性就是mysql的隔离性，创造了mysql并行执行。这在没有事务之前是做不到的。
持久性：就是保存到数据库中，这是一种持久化介质，内存就不是一种持久化介质了。这在没有事务之前是做不到的。

3、mysql事务的考点？
事务一共包括三个部分内容：ACID特性、事务的隔离级别、附加死锁、事务日志、MySQL中的事务。

5.2 事务ACID

5.2.1 事务ACID

小结回滚：
原子性中，事务执行错误就回滚；
持久性中，事务执行正确并完成，就不会再回滚了。

金手指：ACID存在的意义(提供数据库正确执行的理论依据)、ACID解释（要记的）
ACID存在的意义(提供数据库正确执行的理论依据)
ACID是数据库事务 transanction 正确执行的四个基本要素，为数据库事务正确执行提供理论依据，只有同时满足这四个基本要素，才能保证数据库事务一定可以正确执行。
ACID解释
原子性：事件执行过程中不能被打断，所以从原子性知道，事务只有两种状态，事务执行前，事务执行后。
原子化记住一点，
（1）如果事务失败，就回滚，原子性通过回滚来实现操作的原子性，而回滚又是通过undolog实现的，所以说，事务的原子性是通过undolog日志实现的。
（2）如果事务成功，就没事。
一致性：接上面知道，事务只有两种状态，事务执行前，事务执行后，在这两种状态中，数据库的完整性约束没有被破坏。
一致性记住两点：
（1）事务执行中，事务执行失败，磁盘中一定不会持久化；
（2）事务成功执行完成，磁盘一定会持久化。
隔离性：事务的并行执行是隔离性的基础，如果事务只能串行执行，那么和事务没半毛钱关系，只有事务并行执行才设计隔离性。
隔离性记住五点：就是3.3.5面试金手指的那些东西。
持久性：就是保存到数据库中，这是一种持久化介质，内存就不是一种持久化介质了。
持久化记住两点，
（1）事务完成提交，就持久化到磁盘上了，由redolog重做日志保证。
（2）事务成功提交，就不会回滚了，由undolog撤销日志保证，因为回滚就是undolog实现的。

5.2.2 银行转账事务ACID

情景1：原子性：如果执行到第四条话句时服务器崩溃了，会发生什么?
支票表上少了200美元，但是存储表中没有增加200美元，因为在执行存储表增加200美元的时候报错了，如果没有事务失败的回滚机制，就是没有事务的原子性，就造成出错。
原子性通过回滚来实现操作的原子性，而回滚又是通过undolog实现的

情景2：一致性，当执行完第三条语句、第四条语句还未开始时，系统崩溃？
事务有一致性( consistency)，
数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中，一致性确保了,即使当执行完第三条语句、第四条语句还未开始时，系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。

情景3：隔离性，当执行完第三条语句、第四条语句还未开始时，另外一个进程要删除支票账户的所有余额,那么结果可能就是银行在不知道这个逻辑的情况下白白给了Jane200美元。
如果没有事务的隔离性，就无法保证事务并行的安全。
有了隔离性，一个事务所做的修改在最终提交以前,对其他事务是不可见的。当执行完第三条语句、第四条语句还未开始时,此时有另外一个账户汇总程序开始运行,则其看到的支票账户的余额并没有被减去200美元。

情景4：持久性，成功执行完成后，数据库崩溃，没有来得及写入磁盘？
事务有持久化机制，这个持久化机制是由redolog日志保证的，没来得及刷盘持久化，redolog日志里面也一定有。

5.2.3 小结：ACID四特性底层实现

小结：ACID四特性底层实现，拉高逼格
事务的原子性是由undolog撤销回滚实现的；
事务的一致性没啥好讲。
事务的隔离性是由隔离级别实现的；
事务的持久性保证事务执行完后一定能持久，是由redolog重做日志实现的。

5.3 事务四种隔离级别

5.3.1 重点：隔离的对象(互斥性的数据库资源) + 间隙锁

5.3.1.1 隔离的对象(互斥性的数据库资源)

第一，宏观上，隔离的是客户端的操作：隔离性,是指不同的客户端在做事务操作时，理想状态下，各个客户端之间不会有任何相互影响，好像感知不到对方存在一样。

第二，微观上，实际隔离的是客户端操作的互斥性的数据库资源，真正隔离的对象在实现上是数据库资源的互斥性访问，隔离性就是通过数据库资源划分的不同粒度体现的。

5.3.1.2 间隙锁(InnoDB是可重复读隔离级别解决幻读错误的神器)

对于MySQL/InnoDB来说，默认的隔离级别是可重复读，这是因为由于InnoDB间隙锁的存在，已经可以保证不会出现幻读。

可重复读隔离级别下的幻读错误
事务B查询appId为testappid的数据，发现不存在，事务B准备后续插入testappid的记录 事务A插入一条appId为testappid的数据 事务B执行插入appId为testappid的数据，失败，此时事务B再去执行查询，发现确实没有appId为testappid的数据，但就是插入不进去，这就是幻读。
为了解决幻读的问题，有以下两种方法：

方案一：在读取数据的事务开启时，锁定整张表。这就是事务隔离的最高级别：Serializable，序列化。

方案二：在Repeatable read级别下，添加共享锁或排他锁，innoDB引擎会主动加间隙锁，从而避免幻读。

所以，避免幻读更好的方法是第二种：RR+间隙锁 （RR的意思就是可重复读隔离级别）

间隙锁是什么？

间隙锁：锁定一个范围，但不包括记录本身，在RR级别下生效，带锁操作时（带锁读、更新、删除），InnoDB会主动使用间隙锁。根据匹配条件的不同，间隙锁的范围如下:
1、匹配条件为范围，锁定该范围
2、匹配条件为等值" == “，匹配结果不为空，锁定被匹配的记录所在的开区间
3、匹配条件为等值” == "，匹配结果为空，锁定该记录左右两侧的开区间

索引B+树叶子节点的数据是顺序排列的，两个叶子之间的数据称为间隙，在RR级别下带锁读取时，Mysql将符合查询条件的间隙锁起来，避免别的事务在叶子之间插入新的数据破坏RR。

如何不使用间隙锁：

1、索引：使用唯一索引，等值匹配时，Mysql不会再添加间隙锁。
2、下调隔离级别：隔离级别设置成RC，读已提交，隔离级别下调一个级别，就没有间隙锁了。
3、Mysql主动使用插入意向锁：对于插入操作，mysql尝试先加一把插入意向锁，它不会阻止其他事务的插入操作，提高了并发插入的效率。获取插入意向锁的前提是没有间隙锁，一旦有select … for update、select … lock in share mode、delete、update语句，插入意向锁就失效了。

5.3.2 面试金手指：四种隔离级别与三种错误

5.3.2.1 问题1：解释四种隔离级别

SERIALIZABLE 序列化读，隔离级别最高，客户端以互斥的方式访问数据库资源，统一时间内，同一个资源只能被一个客户端访问，好像客户端在排队请求访问，所以称为序列化读。
解释：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
这是最高程度的隔离级别，效率最低，安全性最高，一定不会出现事务并行执行的错误。

REPEATABLE_READ 可重复读，可重复读使用行级锁，行级锁能够保证，一个客户端在一个事务内，多次访问同一个资源时，返回结果是一样的，顾名思义，称为可重复读，这种隔离级别可能会造成幻读现象（新插入的行记录，没有被行级锁锁住）。
解释：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

READ_COMMITTED 读已提交，客户端在一个事务内，每次查询读取的数据都是从数据库读取最新的已提交的数据；

READ_UNCOMMITTED 读未提交，客户端在一个事务内，可以读取到其他客户端事务的尚未提交的数据；

5.3.2.2 问题2：四个概念：隔离级别、资源互斥粒度、事务并发能力、数据一致性隔离级别的本质就是资源互斥粒度，就是数据库锁级别？

问题2：四个概念：隔离级别、资源互斥粒度、事务并发能力、数据一致性隔离级别的本质就是资源互斥粒度，就是数据库锁级别？

对上述的四种事务隔离级别的阐述中，我们使用了从资源互斥访问的角度做了解释。隔离级别越低，资源互斥粒度控制的越细造成两个问题，第一，客户端事务的并发能力就越高，第二，降低数据的一致性。

事务的并发数和数据数据一致性这两个是两个相反的理想指标。而数据库研发的方向就是尽可能提高同时提高两个指标，尽可能减少之间的反作用影响。

5.3.2.3 问题3：解释一个表：四种隔离级别和三种错误

问题3：解释一个表：四种隔离级别和三种错误

四个概念：隔离级别、资源互斥粒度
序列化读：表级锁，读写都要加锁，都是独占锁
可重复读：行级锁，读写都要加锁，都是独占锁
已提交读：行级锁，只有写操作最后committed才加锁（读是共享锁，写是独占锁）
未提交读：行级锁，写操作也不加锁，一个事务的updater写操作被另一个事务reader操作打断，如果updater回退就造成reader读到的是脏数据，如果updater没有回滚reader读到的就是真实数据。

区分可重复读隔离级别和不可重复读(又称虚读)错误？
可重复读，顾名思义，同一个事务内，多次访问同一个资源时，返回结果是一样的；
不可重复读（虚读）错误：由于新插入的数据行的是不加锁的 + update 更改数据的值，导致，在同一个事务内，多次访问同一个资源时，返回结果是不一样；

彻底分析这个表格，彻底理解锁级别制造的资源互斥级别，资源互斥级别制造的隔离级别（3*4=12个）

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

1.1 为什么序列化不会出现幻读？表级锁，读写都是独占锁，原子化数据库事务操作，类似java中的synchronized或lock，当然不会产生同一事务的两次reader中，被另一个事务的insert打断，因为数据库事务操作被原子化了。

1.2 为什么序列化不会出现不可重复读(虚读)？表级锁，读写都是独占锁，原子化数据库事务操作，类似java中的synchronized或lock，当然不会产生同一事务的两次reader中，被另一个事务的updater打断，因为数据库事务操作被原子化了。

1.3 为什么序列化不会出现脏读？表级锁，读写都是独占锁，原子化数据库事务操作，类似java中的synchronized或lock，当然不会产生一个事务的updater中，被另一个事务的reader打断，因为数据库事务操作被原子化了。

2.1 为什么可重复读出现幻读错误？
根本原因：新插入的数据行的是不加锁的。
原因：从隔离级别的定义入手，使用行级锁，读写都是独占锁，锁定表中满足where子句条件的行记录，原子化对其操作。
情景描述：事务A锁定满足where子句条件的行记录，但是新插入的数据行的是不加锁的，假设新插入的数据行也满足where子句条件，事务A中第二次查询就多了一个行记录。
解决方式一种：设置隔离级别为序列化读

2.2 为什么可重复读不出现不可重复读（虚读）错误？
原因：从隔离级别的定义入手，使用行级锁，读写都是独占锁，锁定表中满足where子句条件的行记录，原子化对其操作。所以虚读情景无法实现，updater作用对象是已存在行，已存在且满足where子句的行记录已经被锁住了。

2.3 为什么可重复读不出现脏读错误？
原因：从隔离级别的定义入手，使用行级锁，读写都是独占锁，锁定表中满足where子句条件的行记录，原子化对其操作。所以脏读情景无法实现，reader作用对象是已存在行，已存在且满足where子句的行记录已经被锁住了。

3.1 为什么已提交读出现不可重复读（虚读）错误？
根本原因：从隔离级别的定义入手，使用读写分离锁：写操作committed加上独占锁，但是读操作没有加锁，共享锁等于没加。reader读取满足where子句的行记录的时候，并没有对这些满足where子句的行记录加锁，这使得另外一个事务B可以对这些行记录修改，这是已提交读和可重复的根本区别。
情景描述：事务A中两次reader操作中间事务B提供一次updater操作，由于当前数据库读不加锁，所以updater可以在两次reader之间，打断reader，但是写加锁，updater无法被reader打断，updater一定原子操作，所以两次reader行记录的值不同。
不可重复读解决方式两种：尽可能少用查询出来的结果作为参数执行后续的updateSQL 语句，尽可能使用状态机来保证数据的完整性 + 使用可重复读隔离级别

3.2 为什么已提交读出现幻读错误？
根本原因：从隔离级别的定义入手，使用读写分离锁：写操作committed加上独占锁，但是读操作没有加锁，共享锁等于没加。幻读情景可以满足，新插入的行是不加锁的，可以在两次reader中执行insert操作，reader即使加锁也没有，因为新插入的行是不加锁的，何况现在read都不加锁（加了个共享锁没屁用，等于没加）。

3.3 为什么已提交读不出现脏读错误？
根本原因：从隔离级别的定义入手，使用读写分离锁：写操作committed加上独占锁，脏读情景无法满足，updater操作无法被reader打断。

4.1 为什么未提交读会造好脏读？
根本原因：从隔离级别的定义入手，写操作也不加锁。事务B中，写操作并没有对满足where子句的行记录加锁，所以事务A中可以对这些行记录reader操作。
情景描述：一个事务的updater写操作被另一个事务reader操作打断，如果updater回退就造成reader读到的是脏数据，如果updater没有回滚reader读到的就是真实数据，锁造成脏读。
解决方式：升级为已提交读，对写操作updater加独占锁，这样reader就无法打断了。

4.2 为什么未提交读会造成不可重复读(虚读)？
根本原因：从隔离级别的定义入手，写操作也不加锁，读操作也不加锁。
情景描述：事务A中两次reader读操作，可以加一个updater操作，因为读操作没加锁。
解决方式：升级为可重复读，对行级锁，这样

4.3 为什么未提交读隔离级别会造成幻读？
根本原因：从隔离级别的定义入手，写操作也不加锁，读操作也不加锁。
情景描述：事务A中两次reader读操作，可以加一个insert操作，读操作加锁也没用，新插入的不再加锁范围内，何况读操作没加锁。
解决方式：升级为序列化读，直接对表加锁

5.3.2.4 实践4：设置事务的隔离级别 + 启动事务

第一，修改事务的隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;//等级就是上面的几种

第二，启动事务两种方式：

1. 第一，显式启动事务/手动启动事务， begin 或 start transaction，配套的提交语句是commit，回滚语句是rollback。
2. 第二，隐式启动事务/自动启动事务，set autocommit=0，这个命令会将这个线程的自动提交关掉，意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。

注意：
使用长事务是需要注意的，因为一旦set autocommit=0自动开启事务，所有的查询也都会在事务里面了，有慢SQL那数据库也容易被拖垮的。
我最近就遇到了这样的问题，数据库经常收到报警，其中就有长事务导致的问题。

后面所有的知识都是基于InnoDB的，因为MyISAM不支持事务。

5.3.2.5 底层5：事务试图，事务隔离的底层实现

5.3.2.5.1 概要：事务试图和表视图，事务试图是事务隔离的底层实现

事务视图，这是事务隔离实现的根本，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。就是下面说的ReadView。

注意：在MySQL里，有两个“视图”的概念：
一个是view，它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view … ，而它的查询方法与表一样，这种视图与表查询管理，放在【表与视图】上。
另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，已提交读）和RR（Repeatable Read，可重复读）隔离级别的实现，这就是【事务与视图】。

5.3.2.5.2 详细1：事务快照

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”，注意，这个快照是基于整库的。

你肯定会说，这怎么可能？如果一个库有100G，那么我启动一个事务，MySQL就要拷贝100G的数据出来，这个过程得多慢啊，这谁顶得住啊？可是你回头一想，平时的事务执行起来不是很快么？

实际上，数据库并不需要拷贝出这100G的数据，那快照怎么实现的？

第一，对于数据库的每一个事务，InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id，它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

第二，对于数据库每一个行记录，每行数据也都是有多个版本的，每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。

第一点和第二点的关系：
每一个事务的transaction id和每一个行记录的trx_id的关系：
1、得到transaction id，事务开始的时候，即start transaction，向InnoDB的事务系统申请transaction id，注意这个transaction id是按申请顺序严格递增的，完成第一步，得到transaction id；
2、得到trx id，当使用这个开始的事务更新数据，生成一个新的数据版本，并且把transaction id赋值给这个新的数据版本的事务ID，记为row trx_id，完成第二步，得到新的数据版本的trx id。

这是一个隐藏列，还有另外一个roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的数据版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

为什说对整个数据库的快照并不需要拷贝整个数据库？
事务与快照：在可重复读隔离级别下，事务在启动的时候就“拍了个快照”，注意，这个快照是基于整库的。
快照与redo日志：每一个事务对数据行记录进行改动，都会记录一条undo日志，只要拷贝当前的undo日志就好了。

5.3.2.5.3 详细2：版本链(事务试图的基石)：trx_id和roll_pointer

trx_id和roll_pointer都在InnoDB的聚簇索引中，大概就长这样：

undo log的回滚机制也是依靠这个版本链，每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

这个图的查看方法，当前数据库中某个行记录的数据是 name字段为敖丙，id无意义，name是业务字段，size是数据类型长度，trx id是被执行的事务transaction id赋值，roll pointer是指向历史版本的指针，就是一个地址。
后面的2 3 4 是这个行记录的历史版本信息，都在undo日志里，但是1 在当前数据库中，回到历史版本只要有undo日志在就行了，快照也是这个意思。

5.3.2.5.4 详细3：事务试图ReadView，生成事务试图ReadView的时机不同(事务隔离级别：已提交读和可重复读的本质不同)

接下来可以说一下事务隔离级别和MVCC的关系了（已提交读 + 可重复读），
MVCC的定义：所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用读已提交（READ COMMITTD）、可重复读（REPEATABLE READ）这两种隔离级别的事务，在执行普通的SELECT操作时访问记录的版本链的过程（mvcc的定义与意义），这样子可以使不同事务的读-写、写-读操作并发执行(读已提交：两个read之间update；可重复读：两个read之间insert)，从而提升系统性能。
MVCC只是两个隔离级别select操作访问记录的版本链过程，
下面看版本链，事务id大家可以看出，三个事务(trxid 分表为 200 40 10)分别作了不同的事情。
在读提交隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的，在这个隔离级别下，事务在每次查询开始时都会生成一个独立的ReadView。

可重复读，对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句读取数据时生成一个ReadView，之后的查询就不会重复生成了，所以一个事务的查询结果每次都是一样的。

注意：事务中的视图，只能作用已提交读和可重复读
（1）读未提交隔离级别下直接返回记录上的最新值，没有视图概念，也就是上图中的一栏，脏读，幻读，不可重复读都有可能发生。
（2）串行化隔离级别，底层直接使用加锁的方式(表级锁)来避免并行访问。
小结，mvcc多版本并发控制 里面跟事务隔离级别相关的，只有可重复读和读已提交这两种。

项目开发中，公司的数据库隔离级别默认都是读已提交，不过我们会在一些场景开启可重复读，序列化很少见。

可重复读和序列化读的应用：
可重复读我们之前都是在跟订单金额相关的场景去开启的，还有很多数据修改过程也会用可重复度，因为很多值是需要查询出来，依据那个值做别的操作的，如果多次查询的结果值不一样，那后者也会受到影响。
序列化被称为数据库隔离级别的黄金标准，直接对表加锁，它是绝大多数商业数据库系统中提供的最高隔离级别，使用表级锁保证原子性读写表，一些高度广泛部署的系统甚至无法提供隔离级别与可序列化一样高，金融的场景居多，性能也是最差的，毕竟，银行取钱没有在乎多几秒。

附：如何查看现在使用的数据库的隔离级别：

show variables

小结：这两个隔离级别的一个很大不同就是：生成事务试图ReadView的时机不同。
（1）READ COMMITTD在每一次进行普通SELECT操作前都会生成一个事务试图ReadView；
（2）REPEATABLE READ只在第一次进行普通SELECT操作前生成一个事务试图ReadView，数据的可重复读其实就是ReadView的重复使用。

5.3.2.5.4 详细4：MVCC(从源头上讲，MVCC机制：多版本并发控制)

金手指：隔离性通过MVCC实现，而MVCC通过undolog实现，持久性通过redolog实现。
该句子有误，隔离性中只有已提交读和可重复读由MVCC实现，未提交读和可序列化不是由MVCC实现。

金手指：解释一下MVCC
从源头上讲，MVCC机制：多版本并发控制
事务的隔离级别依赖于MVCC（Multi-Version Concurrency Control）实现，用于提高读操作的并发量。
原理：每个事务都会有一个事务ID，事务ID随时间自增，每行记录有两个隐藏列，维护两个版本号（事务ID），每一行记录可以存在多个版本（记录在undolog中），增删查改时围绕这两个版本号操作。
1、create_version：创建该行的事务版本号
2、delete_version：删除该行数据的事务版本号。
以RR级别为例，MVCC的读取、插入、删除、更新：
1、读取：只会读取版本号比当前事务版本号小，删除版本号必须比当前事务版本号大的行记录。
2、插入：生成一行新记录并记录当前事务的版本号至create_version。
3、删除：将当前的系统版本号设置为这一行的delete_version。
4、更新：拷贝旧记录，生成该行数据的新拷贝，将当前事务的版本号设置为新行的create_version、旧行的delete_version
在RR级别下，Mysql对该行在事务开始时的版本号做一个快照，以后只读取该快照。在RC隔离级别下，MySQL只读取最新的版本号。一个称为快照读、一个称为当前读。
MVCC的优点：支持无锁的并发读，提高并发量
MVCC的缺点：对于同一行记录需要维护多个版本，耗费更多空间

5.4 事务附加三个

5.4.1 事务的死锁(类比Java并发死锁)

死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循坏的现象。小结一句：持有资源的事务等待其他的事务资源，但自己又不释放资源。
情形一，当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁；
情形二，多个事务同时锁定同一个资源时,也会产生死锁。

mysql死锁的解决：优先死锁检测，次之死锁超时，找到后事务回滚。
死锁前，死锁检测找到死锁逻辑：InnoDB存储引擎,能检测到死锁的循环依赖,并立即返回一个错误。这种解决方式很有效,否则死锁会导致出现非常慢的查询。
死锁前，死锁等待找到死锁逻辑：当查询的时间达到锁等待超时的设定后放弃锁请求,这种方式通常来说不太好。
死锁后，事务回滚：
InnoDB目前处理死锁的方法是，遇到死锁后，将持有最少行级排他锁的事务进行回滚(这是相对比较简单的死锁回滚算法)。死锁发生以后,只有部分或者完全回滚其中一个事务,才能打破死锁。对于事务型的系统（如innodb存储引擎）,这是无法避免的,所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

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

5.4.2 事务日志（undolog撤销保证事务原子性，redolog保证事务持久性）

小结：事务日志（要记忆的东西，一句话小结）
mysql日志系统有三种日志，和事务相关的只有两种，undo撤销回滚保证事务原子性，redolog保证事务持久性。

5.4.3 mysql中的事务应用

5.4.3.1 实践：自动提交(AUTOCOMMIT)的开启与关闭

使用SET AUTOCOMMIT 命令设置自动提交事务的启动与关闭
注意1，修改AUTOCOMMIT对myisam类型非事务型的表,不会有任何影响。对这类表来说，因为没有COMMIT或者ROLLBACK的概念,所以是一直处于AUTOCOMMIT启用的模式。
注意2，还有一些命令,在执行之前会强制执行 COMMIT提交当前的活动事务。
如1：在数据定义语言(DDL)中,如果是会导致大量数据改变的操作,比如 ALTER TABLE。如2：还有LOCK TABLES等其他语句也会导致同样的结果。
如果有需要,请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。

5.4.3.2 实践：设置事务隔离级别

小结：MySQL可以通过执行 SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效,可以在配置文件中设置整个数据库的隔离级别,也可以只改变当前会话的隔离级别

5.4.3.3 问题情景：在事务中混合使用存储引擎

MySQL服务器层不管理事务,事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。

解释：mysql架构分为两层：mysql服务层和mysql存储引擎层

mysql服务层：连接器、缓存、分析器、优化器、执行器

mysql服务层下面才是存储引擎层

情景：在事务中混合使用了事务型和非事务型的表(例如 InnoDB和 MyISAM表)
（1）在正常提交的情况下不会有什么问题。
（2）在异常提交的情况下产生问题，事务出错表示该事务需要回滚,myisam类型的非事务型的表上的变更就无法撒销,这会导致数据库处于不一 致的状态,这种情况很难修复,事务的最终结果将无法确定。
小结：所以,创建表的时候为每张表选择合适的存储引擎非常重要。
特别注意：在非事务型的表上执行事务相关操作的时候, MySQL通常不会发出提醒,也不会报错。有时候只有回滚的时候才会发出一个警告:“某些非事务型的表上的变更不能被回滚”。但大多数情况下,对非事务型表的操作都不会有提示。

5.4.3.4 两阶段锁定协议：隐式锁定和显式锁定

小结：InnoDB采用的是两阶段锁定协议( two-phase locking protocol)。在事务执行过程中,随时都可以执行锁定,锁只有在执行COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻被释放。

六、尾声

MySQL事务部分五个考点：事务的ACID特性、事务的隔离机制、死锁、事务日志、MySQL中的事务应用，前两个是重点，特别是事务隔离级别。

天天打码，天天进步！