MySQL4_1事务

发表于 更新于 分类于

事务的使用

事务的状态

事务是一个抽象的概念，它对应一个或多个数据库操作。根据这些操作所执行的不同阶段，我们可以把事务大致划分为以下几个状态：
MySQL-事务状态
如上图可知，事务从活动的开始，直到提交或中止状态时生命周期才算结束，当事务是已提交的，它做的修改会持久生效（事务的持久性），当事务中止，该事务所做的一切修改都会被回滚（原子性）。
1、活动的（active）
事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。
2、部分提交的（partially committed）
当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。
3、失败的（failed）
当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。
4、中止的（aborted）
事务执行到一半出错会变为失败状态，此时，需要进行回滚，即撤销失败事务对当前数据库造成的影响，回滚完毕后，事务就处在了中止的状态。
比如 A 向 B 转账，A 账户扣除后遇到错误，导致 B 账户余额没变，此时需要将 A 账户的扣减操作回滚，恢复到原来的金额。
5、提交的（committed）
当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

语法

1、开启事务

1
2
-- 注意后面的WORK可有可无
BEGIN [WORK];

或者

1
2
3
-- START TRANSACTION和BEGIN的区别主要是前者还能指定事务的访问模式，如果不设置访问模式，
则默认是READ WRITE模式
START TRANSACTION [READ ONLY | READ WRITE | WITH CONSISTENT SNAPSHOT];

需要注意的是，begin/start transaction并不是事务的起点，当执行到第一个操作 InnoDB 表的语句时事务才真正启动。或者可以使用start transaction with consistent snapshot来立刻启动。
2、提交事务

1
COMMIT [WORK];

3、手动中止事务

1
ROLLBACK [WORK];

注意，ROLLBACK 语句是我们程序员手动的去回滚事务时才去使用的，如果事务在执行过程中遇到了某些错误而无法继续执行的话，事务自身会自动的回滚。
4、事务中涉及到的所有表都支持事务
目前只有 InnoDB 和 NDB 存储引擎是支持事务的，如果某个事务中操作的表使用的是不支持事务的表，则对这些表所做的修改将无法被回滚。
5、自动提交

1
SHOW VARIABLES LIKE 'autocommit';

如果这个自动提交开关是打开的，则只要我们不显式使用 START TRANSACTION 或者 BEGIN 语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交。
6、隐式提交
就算 autocommit 开关是关闭的，如果我们输入了某些语句还是会触发隐式的提交，包括：

  • DDL：CREATE、ALTER、DROP
  • 对系统表执行的操作：ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD
  • 事务控制或关于锁定的语句：当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务；或原来 autocommit 为 OFF 的情况下将其修改为 ON，也会触发隐式提交；或者使用 LOCK TABLES、UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。
  • 加载数据的语句：比如我们使用 LOAD DATA 语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。
  • 关于 MySQL 复制的一些语句：使用 START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO 等语句时也会隐式的提交前边语句所属的事务。
    其它的一些语句：使用 ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET 等语句也会隐式的提交前边语句所属的事务。
    7、保存点
    在事务执行的某个阶段设置检查点，下次回滚时可以回滚至此处，而不是从头开始：
    1
    2
    3
    SAVEPOINT 保存点名称;
    ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;
    RELEASE SAVEPOINT 保存点名称;

事务的属性

事务具有以下 4 个基本特征：

原子性(Atomic)

事务中包含的每个操作都被看做一个逻辑单元，这个逻辑单元中的操作要么全部成功，要么全部失败。（记录之前的版本，允许回滚）。

一致性(Consistency)

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。换句话说，事务执行前和事务执行后数据内在的逻辑始终是成立的。比如转帐前后两人的存款总和始终不变。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这时数据库就处于一种不正确的状态，或者说是不一致的状态。例如在一在进行转账的操作中，需要从账户 A 取出 100 元转入账户 B。那么就可以定义一个事务，该事物包括两个操作：第一个操作是从账户 A 中减去 100 元，第二个操作是向账户 B 中转入 100 元。这两个操作要么全做，要么全不做。全做或者全不做，数据库就会处于一致性状态。如果只做一个操作，则逻辑上就会发生错误，减少或增加 100 元，数据库就 处于不一致的状态了。所以说一致性和原子性是密不可分的。

但是现在问题来了——原子性就一定能够保证一致性吗？
答案是否定的：原子性不能完全保证一致性。因为在多个事务并行进行的情况下，即使保证了每个事务的原子性，仍然可能导致数据不一致的结果。例如事务 1 需要将 100 元转入账户 A：先读取 A 的账户余额的值，然后在这个值上加上 100.但是，在这两个操作之间，事务 2 修改了账户 A 的值，为它增加了 100 元，那么最后结果应该是 A 增加了 200 元。但事实上，当事务 1 最终完成时，账户 A 只增加了 100 元，因为事务 2 的执行结果被事务 1 覆盖掉了。所以为了保证并发事务的一致性，就引入了事务的隔离性。(事务开始和结束之间的中间状态不会被其他事务看到)

隔离性(Isolation)

一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，T2 要么在 T1 开始之前就已经结束，要么在 T1 结束之后才开始，这样每个事务都感觉不到有其他事务在并发的执行。关于事务的隔离性数据库提供了多种隔离级别，后面会提到。(适当地破坏一致性来提升性能与并行度 例如：最终一致 ~= 读未提交)

持久性(Durability)

持久性是指一个事务一旦提交，它对数据库中数据的改变就是永久性的。接下来的操作或故障不应该对其执行结果有影响。(每一次的事务提交之后就会保证不丢失)

事务隔离级别

事务并发执行可能遇到的问题

在不保证串行执行的情况下，多个事务的并行执行可能会导致一些问题，按由重到轻分别为：
1、脏写（Dirty Write）：一个事务修改了另一个未提交事务修改过的数据；
2、脏读（Dirty Read）：一个事务读到了另一个未提交事务修改过的数据；
3、不可重复读（Non-Repeatable Read）：一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值；
4、幻读（Phantom）：一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。
5、丢失更新：两个事务在并发下同时更新，后一个事务的更新覆盖了前一个事务的更新；此时前一个事务再去查询时会发现刚才修改的内容没有被修改，好像丢失了更新；丢失更新可以发生在事务的提交和回滚阶段；丢失更新的解决方案是加悲观锁（for update）或乐观锁（）。

隔离级别越低，越严重的问题就越可能发生，按隔离级别的严格程度由轻到严分别为：
1、READ UNCOMMITTED：未提交读。
2、READ COMMITTED：已提交读。
3、REPEATABLE READ：可重复读。
4、SERIALIZABLE：可串行化。

隔离级别 | 脏读 | 不可重复读 | 幻读 | 丢失更新
READ UNCOMMITTED | 会 | 会 | 会 | 会
READ COMMITTED | 不会 | 会 | 会 | 会
REPEATABLE READ | 不会 | 不会 | 会 | 会
SERIALIZABLE | 不会 | 不会 | 不会 | 不会

设置隔离级别

以可重复读为例，设置某次连接的事务隔离级别：

1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

隔离级别与 MVCC、锁之间的关系

接下来我们会讨论如何实现各种隔离级别，原理是利用 MVCC 机制和各种锁，因此这里提前说明一下实现各种隔离级别所需的技术：

隔离级别 | 是否使用 MVCC | 是否使用行锁 | 是否使用间隙锁

  • | - | -
    读未提交 | 否 | 否，忽略其他事务放置的锁 | 否
    读已提交 | 是 | 是 | 否
    可重复读 | 是，与读已提交的区别是创建 ReadView 的时机不同 | 是 | 是
    序列化 | 否，所有事务序列化执行，没有使用 MVCC 的必要了 | 是 | 是

事务隔离级别与MVCC

MVCC 利用 Read View（一致性读视图）来表示数据一个可见的状态，当数据当前的 Read View 是不可见时，能够通过 undo log 串联起来的版本链回溯找到数据可见的版本。

undo log

在 InnoDB 引擎中，聚簇索引都会有 2 个隐藏列：

row_id 并不是必要的，我们创建的表中有主键或者非 NULL 的 UNIQUE 键时都不会包含 row_id 列

  • trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务 id 赋值给 trx_id 隐藏列。
  • roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
    MySQL-undolog

这个undo log就是 MVCC 的核心，undo log 的类型包括：
1、insert undo：在事务正式提交之后就可以被释放掉了，因为要回滚插入操作直接将记录删掉即可；
2、update undo：因为 update undo 还需要支持 MVCC，不能直接释放。

每次修改记录时，都会记录一条 undo log，每条 undo log 都有一个 roll_pointer 属性，因此所有 undo log 实际山可以组成一个链表，称为版本链：
MySQL-版本链
版本链的头节点就是当前记录最新的值，注意每条 undo log 都含有一个事务 id（trx_id）。

Read View (一致性读视图)

Read View 只有在 READ COMMITTED、REPEATABLE READ 两种隔离级别下才能生效。
READ UNCOMMITTED：由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本；
SERIALIZABLE：加锁；
READ COMMITTED、REPEATABLE READ：只能读到已提交的事务修改过的记录，因此需要判断一下版本链中的哪个版本是当前事务可见的。

MySQL-数据版本可见性规则
对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

  • 落在中间部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
  • 落在右边，表示这个版本是由将来启动的事务生成的，不可见；
  • 如果落在左边，若 row trx_id 在数组中，表示这个版本是由还未提交的事务生成的，不可见；若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

总而言之，除了自己的更新总是可见以外，有三种情况：

  • 版本未提交，不可见；
  • 版本已提交，但是是在视图创建后提交的，不可见；
  • 版本已提交，而且是在视图创建前提交的，可见。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。
虽然期间这行数据可能被修改过，但是事务不论在什么时候查询，看到这行数据的结构都是一致的，所以我们称之为一致性读。

前面提过 READ COMMITTED、REPEATABLE READ 都需要维护版本，它们之间的区别主要是生成 ReadView 的时机不同。

READ COMMITTED - 每次读取数据前生成一个 ReadView

1、首先有两个事务在执行：
ID 为 100 的事务：

1
2
3
4
5
6
# Transaction 100
BEGIN;

UPDATE hero SET name = '关羽' WHERE number = 1;

UPDATE hero SET name = '张飞' WHERE number = 1;

ID 为 200 的事务：

1
2
3
4
5
# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

此时，表 hero 中 number 为 1 的记录得到的版本链表如下所示：
MySQL-ReadView版本链

2、上面两个事务正在执行的过程中，如果有一个使用 READ COMMITTED 隔离级别的事务开始执行：

1
2
3
4
5
# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

这个 SELECT1 的执行过程如下：

  • 在执行 SELECT 语句时会先生成一个 ReadView，ReadView 中的 trx_id 列表为[100, 200]；
  • trx_id=100 的事务写入了两个版本，这些版本处在未提交事务集合内，因此不符合可见性要求，跳到下面的版本；
  • 跳到刘备这个版本后，因为 trx_id=80，处于已提交事务集合内，是可见的，因此最终返回的版本就是这条数据。

3、提交其中一个事务
我们把事务 id 为 100 的事务提交一下，就像这样：

1
2
3
4
5
6
7
8
# Transaction 100
BEGIN;

UPDATE hero SET name = '关羽' WHERE number = 1;

UPDATE hero SET name = '张飞' WHERE number = 1;

COMMIT;

然后再到事务 id 为 200 的事务中更新一下表 hero 中 number 为 1 的记录：

1
2
3
4
5
6
7
8
9
# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

UPDATE hero SET name = '赵云' WHERE number = 1;

UPDATE hero SET name = '诸葛亮' WHERE number = 1;

此刻，表 hero 中 number 为 1 的记录的版本链如下所示：
MySQL-ReadView版本链1

4、然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 number 为 1 的记录，如下：

1
2
3
4
5
6
7
8
# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'张飞'

这个 SELECT2 的执行过程如下：

  • 在执行 SELECT 语句时会又单独生成一个 ReadView，该 ReadView 的 trx_id 列表为[200]，此时 trx_id 为 100 的事务已经提交了；
  • 头两个版本诸葛亮、赵云因为版本 trx_id=200 处于未提交事务集合内，因此不可见；
  • 下一个版本张飞可见，因此被返回。

REPEATABLE READ - 在第一次读取数据时生成一个 ReadView

READ COMMITTED 会在每次读取数据时生成 ReadView，而 REPEATABLE READ 则只在第一次读取数据时生成一个 ReadView，之后的查询就不会重复生成了。
同上一节中的例子，在 REPEATABLE READ 隔离级别的事务中多次查找这个 number 为 1 的记录

1
2
3
4
5
6
7
8
# 使用REPEATABLE READ隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值仍为'刘备'

这个 SELECT2 的执行过程如下：

  • 因为当前事务的隔离级别为 REPEATABLE READ，而之前在执行 SELECT1 时已经生成过 ReadView 了，所以此时直接复用之前的 ReadView，快照中的 trx_id 列表为[100, 200]。
  • 头几个版本诸葛亮、赵云、张飞、关羽均不符合可见性原则；
  • 下一个版本刘备 trx_id 为 80，符合可见性原则，因此返回。

之后该事务不论何时查找，结果都会是这样。

更新

更新数据都是先读后写的，如果按照之前读取的规则，当前事务应该是读取不到其他更新的事务所做的修改的，但是这样可能会导致其他事务的更新丢失，因此更新操作的读取都是读当前的值，即当前读（current read）。

除了 update 语句外，如果 select 语句加锁也是当前读，可以用 lock in share mock（读锁）或 for update（写锁）语句来加锁。

MySQL-当前读
如上图所示，事务 A、B 刚开始时就创建了视图，事务 C 没有显式使用 begin/commit，表示该 update 语句本身就是一个事务。

  1. 刚开始，id 为 1 的记录的字段 k 值为 1；
  2. 事务 C 更新 k 为 2；
  3. 由于当前读的规则，事务 B 在 update 时能看到 k 为 2，并更新为 3，注意因为更新后最新版本是当前事务写入的，因此当前事务之后的读操作总是可以读到最新的数据。
  4. 事务 A 在继续查询时，依据版本链找到可见的 k 为 1。

可见，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，我们称之为一致性读。

注意，上面的事务 C 更新完后直接提交了，如果不提交，由于二段锁协议，事务 B 将一直等待 C 提交事务。
MySQL-死锁

删除

DELETE 语句并不会立即将记录从页面中清除，而是执行一个所谓的delete mark操作，为此，表的每一行记录都会额外增加一个删除版本号（delete_version）。
1、新增
| - | - | - |

id create_version delete_version
1 1

2、更新
采用 delete+add 的方式实现，将老数据行标志为删除，然后新增一行新的数据：

| - | - | - |

id create_version delete_version
1 1 2
1 2

3、删除
删除会直接将数据的删除版本号更新为当前事务的版本号：

| - | - | - |

id create_version delete_version
1 1 2
1 2 3

4、查询
为了避免查到旧数据或已经被其他事务更改过的数据，需要满足：

  • 当前事务的版本号需要大于等于创建版本号；
  • 当前事务的版本号需要小于删除的版本号。

count(*)

count(*)用于统计表中的数据量，不同的存储引擎的实现方式有所不同：

  • MyISAM：总行数会被存到磁盘，执行 count(*)时直接返回这个值；
  • InnoDB：把数据一行一行读出然后累积计数。

    上述讨论的 count(*)都是不带查询条件的，不然肯定还要用到索引来过滤数据，MyISAM 也不能这么快返回。

InnoDB 不把数据总数存起来的原因是，由于 MVCC，即使多个查询是在同一时刻发生的，它们该返回多少行数据也是不确定的，如下图所示：
MVCC与count星查询
三个会话，最后查出的数据总量是不同的。
show table status命令输出结果中也有一个TABLE_ROWS字段用于显示该表当前有多少行，但是由于误差比较大，不能直接拿来用。

由于上述方法存在的缺陷，当需要获取数据库表记录总数时，我们一般会自己计数：

  1. 用缓存系统计数
    由于更新数据库和更新缓存并不是一个原子操作，可能会导致其他线程读到缓存中的计数但是读不到新数据的情况。
  2. 在数据库保存计数
    由于会话 A 的事务还未提交，对表 C 的技术值的修改对会话 B 就是不可见的，因此可以保证逻辑上是一致的。
    MySQL-使用数据库计数解决统计问题
    注意，最好先插入操作记录再更新计数表，因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少了事务之间的锁等待，提升了并发度。

count(主键ID)、count(*)、count(1)、count(字段)之间的比较：

  • count(主键ID)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
  • count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
    因为不取值、不涉及解析数据行及拷贝字段值的操作，所以count(1)会比count(主键ID)要快。
  • count(字段)：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
    如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
  • count(*)：并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
    因此count(*)会比其他方式更快。

MVCC 小结

从上边的描述中我们可以看出来，所谓的 MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD、REPEATABLE READ 这两种隔离级别的事务在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ 这两个隔离级别的一个很大不同就是生成 ReadView 的时机不同：

  • READ COMMITTD 在每一次进行普通 SELECT 操作前都会生成一个 ReadView；
  • 而 REPEATABLE READ 只在第一次进行普通 SELECT 操作前生成一个 ReadView，之后的查询操作都重复使用这个 ReadView 就好了。

可重复读的核心是一致性读（consistent read），而事务更新数据时，只能用当前读。如果当前的记录的行锁被其他事务占用，则需要进入锁等待。

事务隔离级别与锁

可重复读解决幻读问题 - 间隙锁

MySQL-当前读例子2
注意上图中的 Q1、Q2、Q3 语句末尾带了for update，说明均为当前读，并会加上写锁，可以看到，Q3 语句看到了 Session B 和 Session C 插入的记录，说明发生了幻读的现象。

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。
    当前读包括写操作和加锁的查询语句（for update）。
  2. Q2 读到了 Session B 更新的数据，这个修改结果是可以被当前读看到的，但不能称为幻读，幻读特指新插入的行被其他事务读到了。

    当前读的规则是要读到所有已经提交的记录的最新值。

幻读可能导致的问题及解决办法 - 间隙锁

由于当前读只会对当前可见的那些记录进行加锁，因此上述两种情况引入的新记录都是锁不上的，锁不上一方面导致可重复读的语义被破坏，因为我要把所有满足条件的记录都锁住，但是事务执行期间却有其他事务引入的满足条件的记录没有被锁住；另一方面是数据一致性问题，如下图所示：
MySQL-幻读导致的数据不一致问题
执行期间：

  1. 经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的；
  2. 经过 T2 时刻，id=0 这一行变成 (0,5,5)；
  3. 经过 T4 时刻，表里面多了一行 (1,5,5)；
  4. 其他行跟这个执行序列无关，保持不变。

但是 binlog 中的内容：

  1. T2 时刻，session B 事务提交，写入了两条语句；
  2. T4 时刻，session C 事务提交，写入了两条语句；
  3. T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。

注意 binlog 中 T1 的语句位置变到了最后提交的时候执行。

那么 update 和 insert 引入“新记录”有什么区别呢？为什么只有 insert 这种情况被称为幻读而要单独拿出来讲？其实是因为，就算我们在当前读的时候就算把所有记录都加上锁（极端情况），新插入的记录因为其还未被分配存储空间，所以我们是无法为其加锁的。
MySQL 中解决幻读的方式是间隙锁，在给对应记录加行锁的同时，MySQL 还会给行两边的间隙加间隙锁，行锁和间隙锁又合称Next-Key Lock。
不同于行锁，间隙锁之间是不存在冲突关系的，跟间隙锁存在冲突关系的，是“往这个间隙中插入一条记录”这个操作。

间隙锁的生效条件

  1. 事务的隔离级别是可重复读
  2. 查找过程中访问到的对象才会加锁
    查找并非特指select，不管是delete还是update，语句执行的第一步都是先找到对象，因此这些语句都会用到锁。

间隙锁存在的问题 - 死锁

间隙锁可能会引起死锁，如下图所示：
MySQL-间隙锁引起的死锁……

  1. session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10)；
  2. session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
  3. session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
  4. session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

可见，两个 session 进入了互相等待的状态，形成了死锁，可见，死锁会导致同样的语句锁住更大的范围，这其实是影响了并发度的。
如果需要避免这种死锁，可以：

  • 将隔离级别设置为读提交，但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。

事务隔离级别的选择

为什么选择RR？

为什么MySQL选择REPEATABLE READ作为默认隔离级别？

  • 主从复制：使用GapLock避免主从不一致，先执行update语句的事务可以通过GapLock阻塞后执行的事务。
  • 兼容：RR可以兼容statement格式的binlog，通过GapLock避免主从不一致；而RC无法避免。

为什么选择RC？

MySQL 默认隔离级别是RR，为什么阿里这种大厂会改成RC？
RR和RC区别

  • RC每次读取都会重新生成一个快照，总是读取行的最新版本
  • 一致性读：RC支持“半一致读”，一条update语句，如果 where 条件匹配到的记录已经加锁，那么InnoDB会返回记录最近提交的版本，由MySQL上层判断此是否需要真的加锁，因此可以大大减少锁冲突。
  • 锁机制：RC隔离级别不解决幻读问题，不会加Gap Lock和Next-Key Lock，因此锁粒度相对较小，不容易形成死锁；
  • 主从同步：RC仅支持row格式的binlog

理由

  • 提升并发：半一致读和锁机制
  • 减少死锁：锁机制

QA

如何正确地删除表中的大量数据

比如，现在想要删除表中的前 10000 条数据，有以下三种方法：
* 直接执行 delete from T limit 10000;
一个大的删除语句，单个语句占用时间长，锁的时间也比较长，且大事务还会导致主从延迟。
* 在一个连接中循环执行 20 次 delete from T limit 500;
相对第 1、3 两种方式较好。
* 在 20 个连接中同时执行 delete from T limit 500。
这 20 个连接互相之间可能会产生锁冲突。

下面的更新为什么没有成功

1
2
3
4
5
6
mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);

以下事务可能并不能将所有 c 都置为 0。

1
2
3
4
begin;
select * from t;
update t set c = 0 where id = c;
commit;

比如：
MySQL-更新丢失
其中，事务 A 开始后，在还未对数据进行加锁的情况下，被事务 B 修改了数据，这时，读操作由于快照读规则，只能读取到老版本数据，而写操作由于当前读规则可以读取到事务 B 写入的数据。
现实中，给数据加乐观锁时也有可能会出现这种情况。

MySQL 实现各隔离级别的方式

读未提交和串行化比较简单，读未提交完全没有做任何事务控制，串行化一个个事务轮流执行。
读已提交隔离级别下，事务会在每次执行读操作前创建一个 ReadView，记录当前正在执行中的所有事务的 trx_id 集合——当然包括当前事务，读取数据时有一个回溯版本的过程：若判断数据当前版本在 ReadView 内，则说明该事务正在执行中，不可读，因此回溯到上一个版本，直到找到一个版本不在 ReadView 内。
可重复读和读已提交原理类似，只是可重复读是在第一次执行读操作的时候生成 ReadView 的。

MySQL 怎么实现事务的 ACID

  1. 一致性
    数据库通过原子性、隔离性、持久性来保证一致性，也就是说 ACID 四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。
  2. 原子性
    利用 undo log，如果事务需要回滚则使用 undo log 撤销所有已经成功执行的 sql 语句。

    对 delete 语句回滚即重新 insert 原语句；
    对 update 回滚即 update 成旧值；
    对 insert 语句回滚即 delete 该记录。

  3. 持久性
    利用 redo log，事务提交前先将记录写入 redo log，提交时将 redo log 刷盘，宕机时会将 redo log 中的内容恢复到数据库，再根据 undo log 和 bin log 的内容决定应该回滚数据还是提交数据。
    相对数据页来说，redo log 有以下好处：
    • redo log 体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
    • redo log 是一直往末尾进行追加，属于顺序 IO。效率显然比随机 IO 来的快。
  4. 隔离性
    利用锁和 MVCC 机制保证隔离性。

为什么最好不要有长事务

一般当系统判断当没有事务再使用到某些回滚日志时，这些回滚日志会被删除。那么系统怎么判断一个回滚日志不会再被使用到呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。