事务

介绍

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

四大条件（ACID）

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务处理方法

  1. 用 BEGIN, ROLLBACK, COMMIT来实现

    • BEGIN 开始一个事务
    • ROLLBACK 事务回滚
    • COMMIT 事务确认

  2. 直接用 SET 来改变 MySQL 的自动提交模式:

    • SET AUTOCOMMIT=0 禁止自动提交
    • SET AUTOCOMMIT=1 开启自动提交

事务的隔离界别和事物并发出现的情况

事务的隔离级别

读未提交（READ UNCOMMITTED）

在读未提交隔离级别下，事务A可以读取到事务B修改过但未提交的数据。

读已提交（READ COMMITTED）

在读已提交隔离级别下，事务B只能在事务A修改过并且已提交后才能读取到事务B修改的数据。

可重复读（REPEATABLE READ）

在可重复读隔离级别下，事务B只能在事务A修改过数据并提交后，自己也提交事务后，才能读取到事务B修改的数据。

出现的情况

脏读（Dirty Read）

一个事务读到了另一个未提交事务修改过的数据

会话B开启一个事务，把id=1的name为武汉市修改成温州市，此时另外一个会话A也开启一个事务，读取id=1的name，此时的查询结果为温州市，会话B的事务最后回滚了刚才修改的记录，这样会话A读到的数据是不存在的，这个现象就是脏读。（脏读只在读未提交隔离级别才会出现）

不可重复读（Non-Repeatable Read）

一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值。（不可重复读在读未提交和读已提交隔离级别都可能会出现）

会话A开启一个事务，查询id=1的结果，此时查询的结果name为武汉市。接着会话B把id=1的name修改为温州市（隐式事务，因为此时的autocommit为1，每条SQL语句执行完自动提交），此时会话A的事务再一次查询id=1的结果，读取的结果name为温州市。会话B再此修改id=1的name为杭州市，会话A的事务再次查询id=1，结果name的值为杭州市，这种现象就是不可重复读。

幻读（Phantom）

一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。（幻读在读未提交、读已提交、可重复读隔离级别都可能会出现）

会话A开启一个事务，查询id>0的记录，此时会查到name=武汉市的记录。接着会话B插入一条name=温州市的数据（隐式事务，因为此时的autocommit为1，每条SQL语句执行完自动提交），这时会话A的事务再以刚才的查询条件（id>0）再一次查询，此时会出现两条记录（name为武汉市和温州市的记录），这种现象就是幻读。

总结

MVCC

介绍

MVCC是指多版本并发控制。MVCC是在并发访问数据库时，通过对数据进行多版本控制，避免因写锁而导致读操作的堵塞，从而很好的优化并发堵塞问题。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

以InnoDB为例，InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。

下为RR隔离级别（默认）下，MVCC是如何具体操作的：

  • SELECT

    InnoDB会根据以下两个条件检查每条记录：

    1. InnoDB只查找版本号早于当前事务版本的数据行（也就是行的系统版本小于或者等于事务的系统版本号），这样可以确保事务读取的行，要么是事务开始前已经存在了，要么是事务自身插入或者修改过的。
    2. 行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始前未被删除。

  • INSERT

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

  • DELETE

    InnoDB为删除的每一行保存当前版本号作为行删除标识。

  • UPDATE

    InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

底层

在MySQL中，MVCC只在读取已提交（Read Committed） 和 可重复读（Repeatable Read） 两个事务级别下有效。其是通过Undo日志中的版本链和ReadView一致性视图来实现的。MVCC就是在多个事务同时存在时，SELECT语句找寻到具体是版本链上的哪个版本，然后在找到的版本上返回其中所记录的数据的过程。

首先需要知道的是，在MySQL中，会默认为我们的表后面添加三个隐藏字段：

  • DB_ROW_ID：行ID，MySQL的B+树索引特性要求每个表必须要有一个主键。如果没有设置的话，会自动寻找第一个不包含NULL的唯一索引列作为主键。如果还是找不到，就会在这个DB_ROW_ID上自动生成一个唯一值，以此来当作主键（该列和MVCC的关系不大）；
  • DB_TRX_ID：事务ID，记录的是当前事务在做INSERT或UPDATE语句操作时的事务ID
  • DB_ROLL_PTR：回滚指针，通过它可以将不同的版本串联起来，形成版本链。相当于链表的next指针。

ReadView

ReadView一致性视图主要是由两部分组成：所有未提交事务的ID数组和已经创建的最大事务ID组成（实际上ReadView还有其他的字段，但不影响这里对MVCC的讲解）。比如：[100,200], 300。事务100和200是当前未提交的事务，而事务300是当前创建的最大事务（已经提交了）。当执行SELECT语句的时候会创建ReadView，但是在读取已提交和可重复读两个事务级别下，生成ReadView的策略是不一样的：读取已提交级别是每执行一次SELECT语句就会重新生成一份ReadView，而可重复读级别是只会在第一次SELECT语句执行的时候会生成一份，后续的SELECT语句会沿用之前生成的ReadView（即使后面有更新语句的话，也会继续沿用）。

Read view 的几个重要属性:

trx_ids: 当前系统活跃(未提交)事务版本号集合。

low_limit_id: 创建当前read view 时“当前系统最大事务版本号+1”。

up_limit_id: 创建当前read view 时“系统正处于活跃事务最小版本号”

creator_trx_id: 创建当前read view的事务版本号；

其中min_id指向ReadView中未提交事务数组中的最小事务ID，而max_id指向ReadView中的已经创建的最大事务ID

版本链

所有版本的数据都只会存一份，然后通过回滚指针连接起来，之后就是通过一定的规则找到具体是哪个版本上的数据就行了。假设现在有一张account表，其中有id和name两个字段，那么版本链的示意图如下：

而具体版本链的比对规则如下，首先从版本链中拿出最上面第一个版本的事务ID开始逐个往下进行比对：

其中min_id指向ReadView中未提交事务数组中的最小事务ID，而max_id指向ReadView中的已经创建的最大事务ID

  • 如果落在绿色区间（DB_TRX_ID < min_id）：这个版本比min_id还小（事务ID是从小往大顺序生成的），说明这个版本在SELECT之前就已经提交了，所以这个数据是可见的。或者（这里是短路或，前面条件不满足才会判断后面这个条件）这个版本的事务本身就是当前SELECT语句所在事务的话，也是一样可见的；

  • 如果落在红色区间（DB_TRX_ID > max_id）：表示这个版本是由将来启动的事务来生成的，当前还未开始，那么是不可见的；

  • 如果落在黄色区间（min_id <= DB_TRX_ID <= max_id）：这个时候就需要再判断两种情况：

    • 如果这个版本的事务ID在ReadView的未提交事务数组中，表示这个版本是由还未提交的事务生成的，那么就是不可见的；

    • 如果这个版本的事务ID不在ReadView的未提交事务数组中，表示这个版本是已经提交了的事务生成的，那么是可见的。

如果在上述的判断中发现当前版本是不可见的，那么就继续从版本链中通过回滚指针拿取下一个版本来进行上述的判断。

各种事务隔离级别下的Read view 工作方式

RC(read commit) 级别下同一个事务里面的每一次查询都会获得一个新的read view副本。这样就可能造成同一个事务里前后读取数据可能不一致的问题（重复读）

RR(重复读)级别下的一个事务里只会获取一次read view副本，从而保证每次查询的数据都是一样的。

快照读和当前读

快照读

快照读是指读取数据时不是读取最新版本的数据，而是基于历史版本读取的一个快照信息（mysql读取undo log历史版本) ，快照读可以使普通的SELECT 读取数据时不用对表数据进行加锁，从而解决了因为对数据库表的加锁而导致的两个如下问题

1、解决了因加锁导致的修改数据时无法对数据读取问题;

2、解决了因加锁导致读取数据时无法对数据进行修改的问题;

当前读

当前读是读取的数据库最新的数据，当前读和快照读不同，因为要读取最新的数据而且要保证事务的隔离性，所以当前读是需要对数据进行加锁的（Update delete insert select …lock in share mode select for update 为当前读）

锁机制

锁分类

按读写

从数据库的读写的角度来分，数据库的锁可以分为分为以下几种：

  • 独占锁：又称排它锁、X锁、写锁。X锁不能和其他锁兼容，只要有事务对数据上加了任何锁，其他事务就不能对这些数据再放置X了，同时某个事务放置了X锁之后，其他事务就不能再加其他任何锁了，只有获取排他锁的事务是可以对数据进行读取和修改。
  • 共享锁：又称读锁、S锁。S锁与S锁兼容，可以同时放置。
  • 更新锁：又称U锁。它允许再加S锁，但不允许其他事务再施加U锁或X锁，当被读取的数据要被更新时，则升级S锁为X锁。U锁的优点是允许事务A读取数据的同时不阻塞其它事务，并同时确保事务A自从上次读取数据后数据没有被更改，因此可以减少X锁和S锁的冲突，同时避免使用S锁后再升级为X锁造成的死锁现象。注意，MySQL并不支持U锁，SQLServer才支持U锁。

兼容性矩阵如下(+ 代表兼容， -代表不兼容)

右侧是已加的锁 X S U
X - - -
S - + +
U - + -

按粒度

MySQL支持不同级别的锁，其锁定的数据的范围也不同，也即我们常说的锁的粒度。MySQL有三种锁级别：行级锁、页级锁、表级锁。不同的存储引擎支持不同的锁粒度，例如MyISAM和MEMORY存储引擎采用的是表级锁，页级锁仅被BDB存储引擎支持，InnoDB存储引擎支持行级锁和表级锁，默认情况下是采用行级锁。

特点

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。数据库引擎总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁从而避免死锁。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。行锁总是逐步获得的，因此会出现死锁。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

下面详细介绍行锁和表锁，页锁由于使用得较少就不介绍了。

行锁

按行对数据进行加锁。InnoDB行锁是通过给索引上的索引项加锁来实现的，Innodb一定存在聚簇索引，行锁最终都会落到聚簇索引上，通过非聚簇索引查询的时候，先锁非聚簇索引，然后再锁聚簇索引。如果一个where语句里面既有聚簇索引，又有二级索引，则会先锁聚簇索引，再锁二级索引。由于是分步加锁的，因此可能会有死锁发生。

MySQL的行锁对S、X锁上做了一些更精确的细分，使得行锁的粒度更细小，可以减少冲突，这就是被称为“precise mode”的兼容矩阵。（该矩阵没有出现在官方文档上，是有人通过Mysql lock0lock.c:lock_rec_has_to_wait源代码推测出来的。）

行锁兼容矩阵
  • 间隙锁(Gap Lock)：只锁间隙，前开后开区间(a,b)，对索引的间隙加锁，防止其他事务插入数据。
  • 记录锁(Record Lock)：只锁记录，特定几行记录。
  • 临键锁(Next-Key Lock)：同时锁住记录和间隙，前开后闭区间(a,b]。
  • 插入意图锁(Insert Intention Lock)：插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。
右侧是已加的锁(+ 代表兼容， -代表不兼容) G R N I
G + + + +
R + – – +
N + – – +
I – + – +

S锁和S锁是完全兼容的，因此在判别兼容性时不需要对比精确模式。精确模式的检测，用在S、X和X、X之间。从这个矩阵可以看到几个特点：

  • INSERT操作之间不会有冲突：你插入你的，我插入我的。
  • GAP，Next-Key会阻止Insert：插入的数据正好在区间内，不允许插入。
  • GAP和Record，Next-Key不会冲突
  • Record和Record、Next-Key之间相互冲突。
  • 已有的Insert锁不阻止任何准备加的锁。
  • 间隙锁（无论是S还是X）只会阻塞insert操作。
注意点
  • 对于记录锁，列必须是唯一索引列或者主键列，查询语句必须为精确匹配，如“=”，否则记录锁会退化为临键锁。
  • 间隙锁和临键锁基于非唯一索引，在唯一索引列上不存在间隙锁和临键锁。
表锁与锁表的误区

只有正确通过索引条件检索数据（没有索引失效的情况），InnoDB才会使用行级锁，否则InnoDB对表中的所有记录加锁，也就是将锁住整个表。注意，这里说的是锁住整个表，但是Innodb并不是使用表锁来锁住表的，而是使用了下面介绍的Next-Key Lock来锁住整个表。

表级锁

直接对整个表加锁，影响表中所有记录，表读锁和表写锁的兼容性见上面的分析。

MySQL中除了表读锁和表写锁之外，还存在一种特殊的表锁：意向锁，这是为了解决不同粒度的锁的兼容性判断而存在的。

意向锁

因为锁的粒度不同，表锁的范围覆盖了行锁的范围，所以表锁和行锁会产生冲突，例如事务A对表中某一行数据加了行锁，然后事务B想加表锁，正常来说是应该要冲突的。如果只有行锁的话，要判断是否冲突就得遍历每一行数据了，这样的效率实在不高，因此我们就有了意向表锁。

意向锁的主要目的是为了使得 行锁 和 表锁 共存，事务在申请行锁前，必须先申请表的意向锁，成功后再申请行锁。注意：申请意向锁的动作是数据库完成的，不需要开发者来申请。

意向锁是表级锁，但是却表示事务正在读或写某一行记录，而不是整个表， 所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。

意向锁分为意向读锁(IS)和意向写锁(IX)。

表锁的兼容性矩阵
右侧是已加的锁(+ 代表兼容， -代表不兼容) IS IX S X
IS + + + –
IX + + – –
S + – + –
X – – – –