跳转至

10. 数据库事务和锁

数据库事务

数据库事务(Database Transaction)，一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。

一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在有以下两个目的：

  • 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  • 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

传统关系型数据库 事务 是访问并可能更新各种数据项的一个程序执行 单元 (unit)。

事务由一个或多个步骤组成，一般使用形如 begin transaction 和 end transaction 语句或者函数调用作为事务界限。

事务内的所有步骤必须作为一个单一的、不可分割的单元去执行。

因此事务的结果只有两种：1. 全部步骤都执行完成，2. 任一步骤执行失败则整个事务回滚。

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是说事务需要具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

本地事务

事务仅限于对单一数据库资源的访问控制，架构服务化以后，事务的概念延伸到了服务中。倘若将一个单一的服务操作作为一个事务，那么整个服务操作只能涉及一个单一的数据库资源,这类基于单个服务单一数据库资源访问的事务，被称为 本地事务(Local Transaction)。

一个事务被提交给了DBMS（数据库管理系统），则 DBMS 需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态（要么全执行，要么全都不执行）;

同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行一样。

但在现实情况下，失败的风险很高。在一个数据库事务的执行过程中，有可能会遇上事务操作失败、数据库系统/操作系统失败，甚至是存储介质失败等情况。

这便需要 DBMS 对一个执行失败的事务执行恢复操作，将其数据库状态恢复到一致状态（数据的一致性得到保证的状态）。

为了实现将数据库状态恢复到一致状态的功能，DBMS 通常需要维护事务日志以追踪事务中所有影响数据库数据的操作。

事务应该具有 4 个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。

  • 原子性(Atomicity)：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  • 一致性(Consistency)：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
  • 隔离性(Isolation)：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。（取决于 RDBMS 的隔离级别）
  • 持久性(Durability)： 一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

在同一个 RDBMS 中，用一个常用的 A 账户向 B 账号汇钱 的例子来说明如何通过数据库事务保证数据的准确性和完整性。熟悉关系型数据库事务的都知道从帐号 A 到帐号 B 需要 6 个操作：

1. 从A账号中把余额读出来（500）。

2. 对A账号做减法操作（500-100）。

3. 把结果写回A账号中（400）。

4. 从B账号中把余额读出来（500）。

5. 对B账号做加法操作（500+100）。

6. 把结果写回B账号中（600）。
数据库事务

  • 原子性：一个事务必须被视为一个不可分割的最小工作单位，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，不能只执行一部分操作。

    保证1-6所有过程要么都执行，要么都不执行。一旦在执行某一步骤的过程中发生问题，就需要执行回滚操作。 假如执行到第五步的时候，B账户突然不可用（比如被注销），那么之前的所有操作都应该回滚到执行事务之前的状态。

  • 一致性：一致性要求数据库总是从一个一致性的状态转换为另外一个一致性的状态

    在转账之前，A和B的账户中共有500+500=1000元钱。在转账之后，A和B的账户中共有400+600=1000元。也就是说，数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态。同时一致性还能保证账户余额不会变成负数等。

  • 隔离性：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。（取决于RDBMS的事务隔离级别）

    在A向B转账的整个过程中，只要事务还没有提交（commit），查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。 如果在A给B转账的同时，有另外一个事务执行了C给B转账的操作，那么当两个事务都结束的时候，B账户里面的钱应该是A转给B的钱加上C转给B的钱再加上自己原有的钱。

  • 持久性：事务一旦提交，所做的状态就会永久保存在数据库中，即使系统奔溃，修改的数据也不会丢。

    一旦转账成功（事务提交），两个账户的里面的钱就会真的发生变化（会把数据写入数据库做持久化保存）。

分布式事务

查询会不会开事务？

MySQL 中事务默认是隐式事务（即自动提交autocommit=1），在执行 INSERT、UPDATE、DELETE 等具体数据操作的时候，数据库自动开启事务、提交或回滚事务。也就是如果不是显式开启一个事务，则每条 SQL 语句都形成独立事务。如果该语句执行后没有返回错误，MySQL 会自动执行 COMMIT。但如果该语句返回错误，则根据错误情况执行 COMMIT 或 ROLLBACK。

如果想开启事务，先设置 autocommit=0，然后用 START TRANSACTION、 COMMIT、 ROLLBACK 这些事务控制语句来使用具体的事务。

通过如下方式，可以关闭 autocommit；需要注意的是，autocommit 参数是针对连接的，在一个连接中修改了参数，不会对其他连接产生影响。如果你新开一个命令窗口，会恢复到默认值。

mysql> set autocommit = 0;
Query OK, 0 rows affected

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set

如果关闭了 autocommit，则一个会话中所有的 sql 语句都在一个事务中，直到执行了 commit 或 rollback，该事务结束，同时马上开始下一个事务。

--窗口一：显式开启事务，加锁执行查询。并未提交事务，锁一直维持
start  TRANSACTION;
select *  from test1 for share ;

--窗口二：变更表结构，要获取写锁，一直在等待，Waiting for table metadata lock
alter table test1 add COLUMN test4 VARCHAR(20) ;

--窗口三：查看未提交的事务，有一条记录，正在执行的sql为空
select * from INFORMATION_SCHEMA.INNODB_TRX;

事务的一些关键语句

-- 开始一个新的读写事务:四种写法

-- BEGIN 语句。这个语句本质上应该是辞旧迎新（提交老事务，准备新事务）。
-- 在一个会话中，如果前面的一个事务还没提交就遇到BEGIN语句时，这个BEGIN就会提交老事务，准备新事务。
-- 原因是MySQL不支持嵌套事务。前面的事务没有提交的情况下，又要BEGIN开始一个新事务，前面的事务无处安放，只能被动提交了。  
BEGIN ;       
BEGIN WORK ; 
START TRANSACTION ;
START TRANSACTION READ WRITE ;

-- 开始一个新的只读事务
START TRANSACTION READ ONLY

-- 这两类语句都不需立即创建一致性读视图，事务的启动将延迟至实际需要时。



-- 开启一个新的读写事务：两种写法
START TRANSACTION WITH CONSISTENT SNAPSHOT
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE

-- 开启一个新的只读事务
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY

-- 这两类语句都会先启动事务，随后立即创建一致性读视图。

事务开启时间

一般我们会认为 begin/start transaction 是事务开始的时间点，也就是一旦我们执行了 start transaction，就认为事务已经开始了，其实这是错误的。

上面的实验也说明了这一点。事务开始的真正的时间点(LSN)，是 start transaction 之后执行的第一条语句，不管是什么语句，不管成功与否。

MySQL 一致性读深入研究

注意，事务是否提交和是否有 sql 在执行，并不是一回事

事务保存点

扁平事务（Flat Transaction）是事务类型中最简单的一种，在实际生产环境中，这可能是使用最为频繁的事务。在扁平事务中，所有操作都处于同一层次，其由BEGIN开始，由COMMIT或ROLLBACK结束，其间的操作是原子的，要么都执行，要么都回滚

因此扁平事务是应用程序成为原子操作的基本组成模块

事务是访问并更新数据库中各种数据项的一个程序执行单元，事务会把数据库从一种一致状态转换为另一种一致状态，这就是事务的目的，也是事务模型区别与文件系统的重要特性之一。

我们之前理解的事务操作是原子的，要么都执行，要么都回滚，也就是通常意义上我们理解的事务概念。

扁平事务虽然简单，但在实际生产环境中使用最为频繁，故每个数据库系统都实现了对扁平事务的支持扁平事务的主要限制是不能提交或者回滚事务的某一部分，或分几个步骤提交。

例如用户在旅行网站上进行自己的旅行度假计划。用户设想从杭州到意大利的佛罗伦萨，这两个城市之间没有直达的班机，需要用户预订并转乘航班，或者需要搭火车等待。用户预订旅行度假的事务为：

BEGIN WORK
-- S1：预订杭州到上海的高铁
-- S2：上海浦东国际机场坐飞机，预订去米兰的航班
-- S3：在米兰转火车前往佛罗伦萨，预订去佛罗伦萨的火车

但是当用户执行到 S3 时，发现由于飞机到达米兰的时间太晚，已经没有当天的火车。这时用户希望在米兰当地住一晚，第二天出发去佛罗伦萨。这时如果事务为扁平事务，则需要回滚之前 S1、S2、S3 的三个操作，这个代价就显得有点大。因为当再次进行该事务时，S1、S2 的执行计划是不变的

从限制中看出，如果支持有计划的回滚操作，那么就不需要终止整个事务。因此就出现了带有保存点的扁平事务。

带有保存点的扁平事务，除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求，开销也太大

在标准 SQL 中，SQL 语言国际标准中，SAVEPOINT name语句声明一个 savepoint。ROLLBACK TO SAVEPOINT name语句回滚到 savepoint。RELEASE SAVEPOINT name将使得命名的 savepoint 被放弃，但不影响其他 savepoint。ROLLBACK或COMMIT导致所有savepoint被放弃。

对于一个事务中有多个语句操作序列，如果支持有计划的回滚操作，那么就不需要终止整个事务。因此就出现了带有**保存点的事务**

事务保存点（savepoint），指的是对事务执行过程中做位置保存（类似我们打游戏时的存盘点），如果你写了一大堆的语句，但是有部分是你不想回滚的，想保留修改的状态，但是部分是你想回滚的。

mysql> start transaction; --开启事务
Query OK, 0 rows affected

mysql> insert into classes values(7,'初三七班');
Query OK, 1 row affected

mysql> savepoint point1; --注意：这边设置了一个存盘点
Query OK, 0 rows affected

mysql> insert into classes values(8,'初三八班');
Query OK, 1 row affected

mysql> rollback to point1; --记住这个语法，回滚到存盘点，存盘点之后的语句就丢弃了
Query OK, 0 rows affected

mysql> commit;
Query OK, 0 rows affected

mysql> select * from  classes; --最后输出，确实只有存盘点之前的成功了
+---------+-----------+
| classid | classname |
+---------+-----------+
|       1 | 初三一班  |
|       2 | 初三二班  |
|       3 | 初三三班  |
|       4 | 初三四班  |
|       5 | 初三五班  |
|       6 | 初三六班  |
|       7 | 初三七班  |
+---------+-----------+
7 rows in set

MySQL中的事务

事务不是MySQL的原生支持，而是InnoDB存储引擎带来的 Feature。旧版本MySQL的默认存储引擎 MyISAM 就不支持事务能力。正因为事务能力，MySQL 的功能越发强大，也是因为事务，MySQL经常存在和索引无关的很多慢查询。

前面铺垫了很多数据基础知识，作为MySQL中支持事务的默认存储引擎，InnoDB对表中数据的读写操作都在事务中执行。

MySQL被设计为支持高并发，支持很多客户端同时连接到数据库，这些连接可以同时执行 SQL。如果这些 SQL 都要读写 InnoDB 表，InnoDB 会为每个连接启动一个事务，这意味着需要同时启动很多事务。

对于 OLTP 场景，通常情况下，事务都会很快执行完成。启动事务、执行 SQL、提交事务的整个流程只会持续很短的时间。

以这样一个场景为例：

  • 客户端连接到 MySQL;
  • 客户端执行 begin 语句;
  • 客户端执行一条 update 语句，按主键 ID 更新一条记录;
  • 这个步骤中，InnoDB 会在执行 update 语句之前，真正启动一个事务;
  • 客户端执行 commit 语句;
  • 客户端关闭数据库连接。InnoDB 会在这一步释放事务。

在这个场景下，InnoDB 事务从启动到释放的整个生命周期，有可能只持续 1 ~ 2 毫秒（甚至更短）。

事务对象和事务池

由于要存放**事务 ID**、事务状态、Undo日志编号、事务所属的用户线程**等信息，每个事务都有一个与之对应的对象，我们称之为**事务对象。

每个事务对象都要占用内存，如果每启动一个事务都要为事务对象分配内存，释放事务时又要释放内存，会降低数据库性能。为了避免频繁分配、释放内存对数据库性能产生影响，InnoDB 引入了事务池（Pool），用于管理事务。

顾名思义，事务池中放的是事务对象。事务池也一样有大小限制，不能无限制的存放事务对象。数据库繁忙的时候，有很多很多事务对象，需要多个事务池来管理。事务池多了之后就有如下问题：

  • 事务池的管理分配：怎么创建新的事务池？
  • 客户端创建了一个新的数据库连接，要获取一个新的事务对象，从哪个事务池获取？
  • 多个

为了解决这些问题，InnoDB 又引入了**事务池管理器（PoolManager）**，用于管理事务池：事务池的创建与初始化。

InnoDB 整个生命周期中，事务池管理器只有一个，它有个很重要的属性（m_size），用于指定每个事务池能用多大内存来存放事务对象。这个属性值来源于一个硬编码的常量值。

// https://github.com/mysql/mysql-server/blob/trunk/storage/innobase/trx/trx0trx.cc#L429
/** Size of on trx_t pool in bytes. */
static const ulint MAX_TRX_BLOCK_SIZE = 1024 * 1024 * 4;
这意味着每个事务池能用来存放事务对象的内存是 4194304 字节，也就是 4 M

MySQL 启动过程中，事务池管理器只会创建并初始化一个事务池。这个事务池会放入事务池管理器的 m_pools 属性。这个属性是个数组（vector），用于管理所有事务池。

事务的核心数据结构

https://github.com/mysql/mysql-server/blob/trunk/storage/innobase/include/trx0trx.h#L675

trx_t: 整个结构体在每个连接都会持有一个，也就是在创建连接后执行第一个事务开始，整个结构体就被初始化了，后续这个连接的所有事务一直复用里面的数据结构，直到这个连接断开。可以说这个结构体贯穿连接的整个生命周期，一直被连接内的事务使用。

trx_lock_t: 在 8.0 中，将 lock 单独从 trx_t 中解耦出来。观察 trx_lock_t 的定义。每个事务都会维护执行过程中加的所有锁（ trx_locks，双向链表），以及发生锁等待时等待的锁（ wait_lock ）

同时，事务启动后，就会把这个trx_t结构体加入到全局事务链表中(trx_sys->mysql_trx_list)，如果是读写事务，还会加入到全局读写事务链表中(trx_sys->rw_trx_list)。在事务提交的时候，还会加入到全局提交事务链表中(trx_sys->trx_serial_list)。

state字段记录了事务四种状态:TRX_STATE_NOT_STARTED, TRX_STATE_ACTIVE, TRX_STATE_PREPARED, TRX_STATE_COMMITTED_IN_MEMORY。

从并发控制的角度来看，事务在其生命周期内可能存在如下状态：

  • TRX_STATE_NOT_STARTED
  • TRX_STATE_FORCED_ROLLBACK
  • TRX_STATE_ACTIVE
  • TRX_STATE_PREPARED
  • TRX_STATE_COMMITTED_IN_MEMORY (alias below COMMITTED)

正常的事务生命周期状态变化： NOT_STARTED -> ACTIVE -> COMMITTED -> NOT_STARTED

用户事务和内部事务

InnoDB 读写表中数据的操作都在事务中执行，开始一个事务的方式有两种：

  • 手动：通过 BEGIN、START TRANSACTION 语句以及它们的扩展形式开始一个事务。
  • 自动：直接执行一条 SQL 语句，InnoDB 会自动开始一个事务，SQL 语句执行完成之后，又会自动提交这个事务。

这两种方式开始的事务，都用来执行用户 SQL 语句，属于 用户事务。正常情况下，用户事务与连接会话都是一对一的。在 当前数据库连接 的操作时序上，事务和事务之间的执行是线性排开依次执行的。

（there is a 1:1 relationship between a transaction handle (trx) and a session (client connection). One session is associated with exactly one user transaction）

当建立了 java.sql.Connection 连接后，可以不限次数执行事务SQL请求 ，由于Connection对象的通信值基于TCP/IP协议的，当初始化后在手动关闭之前和数据库保持心跳存活连接。所以，可以使用Connection对象执行不限次数的SQL语句请求，包括事务请求。这个看似比较简单的表述，在实际使用过程中非常重要，数据库连接池就是基于此特性建立的

InnoDB 有时候也需要自己执行一些 SQL 语句，为了和用户 SQL 做区分，我们把这些 SQL 称为内部 SQL。内部 SQL 也需要在事务中执行，执行这些 SQL 的事务就是**内部事务**。内部事务的最大特点就是不跟用户连接相关联（these transactions would no longer be associated with a session）

InnoDB 有几种场景会使用内部事务，以下是其中主要的三种:参考

  • 如果上次关闭 MySQL 时有未提交，或者正在提交但未提交完成的事务( ACTIVE or PREPARED)，在启动过程中（执行崩溃恢复），InnoDB 会把这些事务恢复为内部事务，然后提交或者回滚。

  • 后台线程(比如 purge system )执行一些操作时，需要在内部事务中执行内部 SQL。以 ib_dict_stats 线程为例，它计算各表、索引的统计信息之后，会使用内部事务执行内部 SQL，更新 mysql.innodb_table_stats、mysql.innodb_index_stats 表中的统计信息。

  • 为了实现原子操作，DDL 语句执行过程中，InnoDB 会使用**内部事务（）**执行内部 SQL，插入一些数据到 mysql.innodb_ddl_log 表中

事务对象分配

InnoDB 用事务池来管理事务对象，用事务池管理器来管理事务池。

不管是用户事务，还是内部事务，真正启动事务之前，都需要通过事务池管理器从某个事务池的事务队列中分配一个事务对象。

分配事务对象之后，InnoDB 还会对事务对象的几个属性再做一次初始化工作，把这几个属性再一次设置为初始值，其实就是对这些属性做了重复的赋值操作。

这些属性中，有必要提一下的是事务状态（trx->state）。出厂设置的事务对象，事务状态是 TRX_STATE_NOT_STARTED，表示事务还没有开始。

事务的启动

并发控制

在计算机科学，特别是程序设计、操作系统、多处理机和数据库等领域，并发控制（Concurrency control）是确保及时纠正由并发操作导致的错误的一种机制。

我们都知道事务的几种性质，数据库为了维护这些性质，尤其是 一致性 和 隔离性 ，一般使用 加锁 这种方式。

同时数据库又是个高并发的应用，同一时间会有大量的并发访问，如果加锁过度，会极大的降低并发处理能力。

所以对于加锁的处理，可以说就是数据库对于事务处理的精髓所在。

数据库管理系统（DBMS）中的并发控制的任务是 确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和一致性以及数据库的统一性。

脏读现象

一句话概况：一个事务读取到了另外事务中未提交的数据。

脏读是指在数据库访问中，当一个事务 A 正在访问数据，并且对数据进行了修改，而这种修改事务还没有提交到数据库中，这时另外一个事务 B 也访问这个数据，然后使用了这个数据。

然后 A 又进行回滚操作，则事务 B 访问的数据是无效的。即上述转账例子中，转账事务进行到一半的时候，此时另外一个事务去读了 A 账号的余额，然后以前的转账事务回滚，A 的余额变回以前的值。

此时后面读余额的事务即为脏读。在高并发情况下和对数据准确性要求较高的情况下，这个现象几乎是不能容忍的。

举一个实际场景中的例子：两个火车票代售点，同时读取 12306 数据库中的某趟列车的车票余票数量为 X，然后同时卖出一张票，然后同时提交了 X-1 到数据库中，这样就造成了卖了两张票，而库中记录只减了一张。生这种情况的原因是因为两个事务读入同一数据并同时修改，其中一个事务提交的结果破坏了另一个事务提交的结果，导致其数据的修改被丢失，破坏了事务的隔离性。并发控制要解决的就是这类问题。

不可重复读

一句话概括：同一事务内前后多次读取，数据内容不一致（第二次读到了其他事务更新提交的数据）

事务 A 在执行读取操作，由整个事务 A 比较大，前后读取同一条数据需要经历很长的时间 。而在事务 A 第一次读取数据，比如此时读取了小明的年龄为 20 岁，事务 B 执行更改操作，将小明的年龄更改为 30 岁，此时事务 A 第二次读取到小明的年龄时，发现其年龄是 30 岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

幻读

事务 T1 读取一组满足某些 <搜索条件> 的数据。事务 T2 创建了满足 T1 的 <搜索条件> 的数据项并提交。如果 T1 用相同的<搜索条件>再次读取，得到一组不同于第一次读取的数据。这就叫幻读。

当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。

不可重复读和幻读有什么不同？

注意和幻读定义的两个不同

  • 幻读定义中有 < search condition >
  • 幻读定义中 T2 是“创建数据”，不可重复读的定义中 T2 是修改或者删除数据

在满足 < search condition > 的范围内，修改和删除数据必定是对已经存在的数据行操作，而创建数据则意味着创建之前这个数据项是不存在的。

"创建数据"不仅是insert，还包括update, update 把本来不满足谓词范围的数据项更新成满足谓词范围的数据项，比如：谓词范围是 a>1 and a<5，update a=4 where a=6 就是这样的情况。

数据库隔离级别

ANSI/ISO SQL 定义的 SQL 标准中描述了四种隔离级别，从高到底依次为：

  • 序列化(Serializable)
  • 可重复读(Repeatable reads)
  • 读已提交(Read committed)
  • 读未提交读(Read uncommitted)

对应的隔离级别如下：

隔离级别 脏读现象 不可重复读现象 幻读现象
读未提交读 可能 可能 可能
读已提交 不可能 可能 可能
可重复读 不可能 不可能 可能
序列化 不可能 不可能 不可能

注意，不同的数据库厂商对 SQL 标准中规定的这四种隔离级别的支持不一样，有的 RDBMS 数据库只实现了其中几种隔离级别，我们讨论的 MySQL 虽然支持 4 种隔离级别，但是与 SQL 标准中规定的各级隔离级别允许发生的现象却有些出入。

四个隔离级别从上到下对事务执行的并发程度进行了不同程度的限制，更加严格的限制在带来更好的数据一致性的同时，也会损失更多并发带来的高性能。

实际使用中，隔离级别并不是越高就越好，大多数情况下应用并不需要很高的数据一致性。相反的，在多用户环境下，更强调的是并发度。所以综合考虑选取一个折中的办法往往能达到最优的效果。

在大多数 RDBMS 中，设置的隔离级别可能都是读已经提交。

  • MySQL 默认隔离级别是 REPEATABLE-READ，实际上 MySQL 内部通过 MVCC 和间隙锁解决了幻读的问题。

  • PostgreSQL 内部实际上只支持提交读、可重复读、序列化三种级别，PostgreSQL 内部会将 Read uncommitted 视同为 Read commit 处理。默认是 Read commit

MVCC 机制

MVCC 的核心思想是为每个数据行保存其在不同时间点的版本，并通过版本号或时间戳来标识这些版本。当一个事务开始时，MySQL 会为该事务创建一个独立的事务 ID，并利用这个 ID 去获取数据的一致性快照。

为了实现READ COMMITTED以及REPEATABLE READ，我们需要保证不同的事务在某一时刻只能看到一部分历史数据或者自己所修改的数据，而多版本并发控制（Multiversion concurrency control）通过 undo log 组成的版本链以及 ReadView 帮我们实现了这一点。

对于使用 InnoDB 存储引擎的表来说，每行记录后面会有三个隐藏字段：

  • DB_TRX_ID：表示最后一次修改本记录的事务 ID，占用 6 字节

  • DB_ROLL_PTR：回滚指针，指向 undo log 中本记录的上一个版本，可以通过回滚指针将所有记录串成了一个版本链

  • DB_ROW_ID：自增 ID，如果表没有定义主键或者没有列是唯一非空的，就会生成这个隐藏列，对于我们的 tbl_user 表不存在这个隐藏列

为了实现原子性，每当事务修改一条记录（INSERT、UPDATE 或者 DELETE）时，都会在类型为 FIL_PAGE_UNDO_LOG 的页中添加一条 undo log，之后可以通过遍历日志实现回滚操作。

可重复读隔离级是由 MVCC 实现的，实现的方式是启动事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题。

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

这很好理解，假设你要 update 一个记录，另一个事务已经 delete 这条记录并且提交事务了，这样不是会产生冲突吗，所以 update 的时候肯定要知道最新的数据。

https://cloud.tencent.com/developer/article/2136022

数据库锁

在 DBMS 中，当并发事务同时访问一个资源时，有可能导致在不同事务内部看到的数据不一致。

因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。锁就是其中的一种机制。

加锁的目的就是保证共享资源在任意时间里，只有一个线程访问，这样就可以避免多线程导致共享数据错乱的问题。

操作系统中的锁

多线程访问共享资源的时候，避免不了资源竞争而导致数据错乱的问题，所以我们通常为了解决这一问题，都会在访问共享资源之前加锁。

最底层的两种就是会「互斥锁和自旋锁」，有很多高级的锁都是基于它们实现的，你可以认为它们是各种锁的地基，所以我们必须清楚它俩之间的区别和应用。

互斥锁是一种「独占锁」，比如当线程 A 加锁成功后，此时互斥锁已经被线程 A 独占了，只要线程 A 没有释放手中的锁，线程 B 加锁就会失败，于是就会释放 CPU 让给其他线程，既然线程 B 释放掉了 CPU，自然线程 B 加锁的代码就会被阻塞。

对于互斥锁加锁失败而阻塞的现象，是由操作系统内核实现的。

当加锁失败时，内核会将线程置为「睡眠」状态，等到锁被释放后，内核会在合适的时机唤醒线程，当这个线程成功获取到锁后，于是就可以继续执行。

可以简单理解为当某个事务在操作开始时，锁定某一个资源对象（比如某个表，比如某一行），在这个事务操作结束之前，不允许其他事务操作这个对象。

  • 基于锁的属性分类：共享锁、排他锁。

  • 基于锁的粒度分类：表锁、行锁、记录锁、间隙锁、临键锁。

  • 基于锁的状态分类：意向共享锁、意向排它锁。

  • 还有死锁...

乐观锁与悲观锁

乐观锁和悲观锁是两种思想，用于解决并发场景下的数据竞争问题。

乐观锁

乐观锁不是数据库自带的，需要我们自己去实现。

乐观锁是指操作数据库时(更新操作)想法很乐观，认为这次的操作不会导致冲突。在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加 1。也就是先查询出那条记录，获取出 version 字段，如果要对那条记录进行操作(更新)，则先判断此刻 version 的值是否与刚刚查询出来时的 version 的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将 version 字段的值加 1；如果更新时发现此刻的 version 值与刚刚获取出来的 version 的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

乐观锁认为一般情况下数据不会造成冲突（所以叫"乐观"），所以在数据进行提交更新时才会对数据的冲突与否进行检测。

它本身是种程序设计思想，通过一种标识来决定数据是否可以操作。

悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟 java 中的 synchronized 很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

读锁写锁

读锁是共享的，是互相不阻塞的，多个数据库事务在同一时间读取同一资源，互不干扰。（读与读之间不互斥）

写锁是排他的，会阻塞其他的写锁和读锁，写锁有更高的优先级。（即我在写资源的时候，其他用户无法读写）

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源，锁定的资源越少，系统的并发性更好。

任何时候，让锁定的数据量最小化，理论上就能保证在给定资源上同时进行更改操作，只要被修改的数据彼此不冲突即可。

锁定策略是锁开销和数据安全性之间的平衡，这种平衡会影响性能。大多数商业数据库系统没有提供太多的选择，一般都是在表中施加行级锁（row level lock）， 为了在锁比较多的情况下尽可能地提供更好的性能，的实现方式非常复杂。 锁是数据库实现一致性保证的方法。数据库操作专家必须深入源代码，才能确定合适的配置， 以优化速度与数据安全之间的平衡。

锁的粒度

根据锁定对象的粒度和锁定范围，可以大致分为：

  • 全局锁（整个实例）

  • 表锁（table lock）（被锁定的资源的粒度是表）

  • 行锁 （row lock）（被锁定资源的粒度是行）

-- InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁;
-- 否则，InnoDB使用表锁，在不通过索引(主键)条件查询的时候，InnoDB是表锁而不是行锁。

-- 如果没有使用索引或索引失效，行锁就会变成表锁
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

https://www.jianshu.com/p/1f4940c134b3

全局锁

FTWRL 是 FLUSH TABLES WITH READ LOCK 的简称(FTWRL)，该命令主要用于保证备份一致性备份。

-- 执行后，整个数据库就处于只读状态了，所有写线程都会被阻塞
flush tables with read lock;

为了达到这个目的，它需要关闭所有表对象，因此这个命令的杀伤性很大，执行命令时容易导致库 hang 住。

如果它在主库执行，则业务无法正常访问；如果在备库，则会导致 SQL 线程卡住，主备延迟。

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

共享锁/排它锁

读锁写锁也被称为共享锁和排他锁

InnoDB 实现了行级别的共享锁和排它锁。

  • 读锁是共享的，是互相不阻塞的，多个客户端(严格说应该是不同事务)在同一时间读取同一资源，互不干扰。（即我在读资源的时候，其他人只能读不能写）
  • 写锁是排他的，会阻塞其他的写锁和读锁，写锁有更高的优先级。（即我在写资源的时候，其他用户无法读写）
  • 允许不同事务之间共享加锁读取，但不允许其它事务修改或者加入排他锁。

隐式加锁：InnoDB，在执行事务过程中会自动加锁，当事务 COMMIT 完成或 ROLLBACK 时锁会自动释放。

显式加锁：

-- 全局加锁，对整个数据库实例加锁，这个语句通常也叫FTWRL
-- 整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞
-- 数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。DDL和DML都会被阻塞
Flush tables with read lock ;

-- 全局锁的使用场景：一般是数据库逻辑备份，或某种特殊业务需求要让整个库临时只读。


-- 对某个对象（一般是行锁）显式加读锁，如果这行对某些行加锁的时候，其他事务还在写入，那么这行select会一直等到其他事务提交才会读到最新的数据。
SELECT ... FOR SHARE
-- 等价于 SELECT...LOCK IN SHARE MODE，后面这种写法可以兼容老版本，FOR SHARE这种写法可以支持更多特性。
-- 在 MySQL8.0.22之前， SELECT ... FOR SHARE需要SELECT权限外，还需要 DELETE, LOCK TABLES, or UPDATE 三个之一
-- 在 MySQL8.0.22之后， SELECT ... FOR SHARE只需要select权限



-- 显式加写锁
SELECT...FOR UPDATE

-- 显示加读锁或写锁
-- 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，
-- 那么其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。
lock tables … read/write
-- 释放锁 unlock tables

读锁

-- 注意：这里都是显示开启事务，故意执行事务不提交。
-- session1
start transaction;
select * from test where id = 1 lock in share mode;
-- 在session1线程里面，它自己也无法获得写锁
update emp set ename = 'ALLEN1111' where ename='ALLEN' ;

-- session2
start transaction;
select * from test where id = 1 lock in share mode;

-- 此时 session1 和 session2 都可以正常获取结果，那么再加入 session3 排他锁读取尝试

-- session3:
start transaction;
-- session3的SQL可以换成任意的写语句（update,insert,delete,alter效果都是一样）
select * from test where id = 1 for update;
-- Lock wait timeout exceeded; try restarting transaction -- 在 session3 中则无法获取数据，直到超时或其它事物 commit



-- 通过 information_schema.INNODB_TRX 表里的 trx_mysql_thread_id 可以找到锁表的线程ID
-- 再回 information_schema.`PROCESSLIST` 表里面去查，就可以找到相应的连接。
SELECT * FROM information_schema.INNODB_TRX

写锁

当一个事务加入排他锁后，不允许其他事务加共享锁或者排它锁读取，更加不允许其他事务修改加锁的行。

-- session1:
-- session对资源添加写锁
start transaction;
select * from test where id = 1 for update;

-- session2(session2对test的增删改查任何访问都会被阻塞)
-- 如果session1的事务一直不提交或回滚，那么session2超时
start transaction;
select * from test where id = 1 for update;

锁的释放

MySQL 中释放锁的动作都是隐式的，对于锁的释放工作，MySQL 自己来干，就类似于 JVM 中的 GC 机制一样，把内存释放的工作留给了自己完成。

对于锁的释放时机，在不同的隔离级别中也并不相同，比如在“读未提交”级别中，是 SQL 执行完成后就立马释放锁；而在“可重复读”级别中，是在事务结束后才会释放。

如果完全按照数据库规范来实现 RC 隔离级别，为了保证其他事务可以读到未提交的数据，那就必须得在 SQL 执行完成后，立马释放掉锁，这时另一个事务才能读到 SQL 对应写的数据，但在 InnoDB 引擎中，它基于 MVCC 机制实现了该效果，为此，InnoDB 的 RC 级别中，SQL 执行结束后并不会释放锁。

意向锁

MySQL 支持多种粒度的锁（表锁，行锁）。它允许行级锁与表级锁共存，为了实现多粒度级别的锁定，InnoDB 使用了**意向锁**，它其实就是其中的一种表锁。

假设一张表中有一千万条数据，现在事务 T1 对 ID=8888888 的这条数据加了一个行锁，此时来了一个事务 T2，想要获取这张表的表级别写锁。

写锁必须为排他锁，也就是在同一时刻内，只允许当前事务操作，如果表中存在其他事务已经获取了锁，目前事务就无法满足“独占性”，因此不能获取锁。

-- 表级别的锁

-- 用读锁锁表，会阻塞其他事务修改表数据。
LOCK TABLE my_tabl_name READ;
-- 用写锁锁表，会阻塞其他事务读和写。
LOCK TABLE my_table_name WRITE;


-- 行级别的锁（查询走索引）

-- 读锁
SELECT ... FOR SHARE
-- 写锁
SELECT ... FOR UPDATE


-- 考虑这样的一个例子：

-- 事务A锁住了表中的一行，让这一行只能读，不能写。（对某行加读锁）

-- 事务B申请整个表的写锁。（如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。）

-- 数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

-- 数据库内部要怎么判断这个冲突呢？（即B事务准备对全表加写锁之前的检测）

-- step1：判断表是否已被其他事务用表锁锁表。
-- step2：遍历表中的每一行是否已被行锁锁住。（由于这样的效率很低，需要遍历整个表。）

-- 于是意向锁出现了，在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

-- 在意向锁存在的情况下，上面的判断可以改成

-- step1：不变
-- step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。


-- 所以准确来讲，SELECT ... FOR SHARE 持有的是 IS lock（意向读锁）
-- 所以准确来讲，SELECT ... FOR UPDATE 持有的是 IX lock（意向写锁）

-- 意向锁实现的背景是多粒度锁的并存场景。

-- 注意：申请意向锁的动作是数据库内部完成的，就是说，事务A申请一行的行锁的时候，数据库内部会自动先开始申请表的意向锁，不需要我们自己实现。
-- 意向锁是表级锁的一种，它是由数据库引擎自行维护的，用户自己无需也无法操作意向锁。

间隙锁

间隙锁（Gap Lock）是 InnoDB 存储引擎为了实现可重复读隔离级别而引入的一种锁机制。它锁住的是索引记录之间的间隙（而不是索引记录本身），防止其他事务在这个间隙中插入新的数据。

为什么需要间隙锁？

防止幻读: 幻读是指在一个事务中，两次读取同一条数据，却得到了不同的结果。间隙锁通过锁定索引记录之间的间隙，防止其他事务在该间隙中插入新的数据，从而避免幻读的发生。

间隙锁的特性

  • 锁定范围: 间隙锁锁定的是索引记录之间的间隙，而不是索引记录本身。
  • 锁类型: 间隙锁是排它锁，其他事务不能在锁定的间隙中插入新的数据。
  • 触发条件: 当事务执行范围查询时，InnoDB 会自动为符合条件的索引记录之间的间隙加上间隙锁。
  • 锁的释放: 事务提交或回滚时，间隙锁会自动释放。

MDL 锁

在 MySQL 使用过程中，不免有对表进行更改的DDL操作(alter/drop table)。

有的时候我们会发现，一条简单的对表增加字段的操作，会执行很长时间，严重时甚至导致整个数据库挂掉。

在出现问题时查看 show processlist 的时候，可以看到简单的所谓的Waiting for table metadata lock之类的状态，有些情况很难排查。

元数据锁是 server 层的表级锁。主要用于隔离 DML 和 DDL 操作之间的干扰。MDL 锁不仅仅是作用在 table 上，也作用在 schemas/

每执行一条 DML、DDL 语句时都会申请 MDL 锁，DML 操作需要 MDL 读锁，DDL 操作需要 MDL 写锁（MDL 加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到 MDL 锁后，直到事务执行完才会将锁释放。

其实 MDL Lock 是 MySQL 上层一个非常复杂的子系统，有自己的死锁检测机制。

考虑如下两个 session:

session1 session2
BEGIN
SELECT * FROM XXX;
sleep(60);
DROP TABLE XXX
SELECT * FROM XXX
  • session1 显示开启事务，两次查询间隔60秒。
  • session2 在这两次查询之间执行删表语句。

如果 DROP TABLE 成功执行了，那会话 1 的第二个 select 会出错，这明显不是我们想要的结果。

所以为了避免此类问题，MySQL5.5 版本加入了 MDL(Metadata Lock)：

  • 当对一个表做 CRUD 操作的时候，加 MDL 读锁（MDL_SHARED_READ）；

  • 当要对表做结构变更操作（DDL）（alter, drop）的时候，加 MDL 写锁（MDL_EXCLUSIVE）。

  • 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。

  • 读写锁之间、写锁之间互斥，用来保证变更结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

在这个例子中，show processlist 可以看到， session2 的 DROP 语句一直在等待 Waiting for table metadata lock

踩坑实战一

session1 session2 session3 session4
begin
select * from user limit 1
select * from user limit 1
alter table user add address varchar(32)
select * from user limit 1
  • session1 执行 select ，持有 MDL 读锁（由于显示开启事务，事务并未结束，所以一直持有 MDL 读锁）。
  • session2 执行 select，也要持有 MDL 读锁，由于 MDL 读锁不互斥，所以也可以拿到，正常执行事务完成，释放 MDL 读锁
  • session3 要持有 MDL 写锁，由于 session1 的 MDL 读锁并未释放，所以 session3 被阻塞，状态是Waiting for table metadata lock

  • session4 也会被阻塞（如果有 MDL 写锁被阻塞，后续对这个表的所有请求都会被阻塞）

  • 原因剖析：应该是 MySQL 内部维护了一个 MDL 队列，避免 MDL 写锁一直请求不到。

  • （如果没有先来后到的话，后面的读写请求不停的过来进行查询，这个 DDL 会一直被阻塞）
  • 问题注意：如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到 MDL 锁后，直到事务执行完才会将锁释放。

（这里有种特殊情况如果事务中包含 DDL 操作，mysql 会在 DDL 操作语句执行前，隐式提交 commit，以保证该 DDL 语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放，例如 id 44 的语句改为，此时一旦 alter 语句执行完成会马上提交事务（autocommit=1），后面的 select 就在本次事务之外，其执行完成后不会持有读锁）

排查思路

对于这种情况，排查思路就是最简单的查看关于这个表上操作的所有进程和事务；

-- 查事务
select * from information_schema.innodb_trx  where trx_query like '%table_name%';

-- 查进程
select * from information_schema.PROCESSLIST where info like '%table_name%';

踩坑实战二

我们知道，如果一个事务没有提交，会阻塞后面的 DDL 操作。

那么，是不是我们执行 select * from information_schema.innodb_trx 查询不到正在执行的事务，就不会出现 MDL 阻塞的情况了呢?

显然不是。来看一个例子（查询一个不存在的列）：

session1 session2
begin;
select unknown from user;
alter table user add column address varchar(20)

我们发现，session2 阻塞，等待获取 MDL 写锁。查看正在运行的事务，发现并没有事务在运行。

mysql> mysql> select * from information_schema.innodb_trx\G;
Empty set (0.00 sec)

因为information_schema.innodb_trx中不会记录执行失败的事务(查询不存在的列，语句未提交等)，但是在这个执行失败的事务回滚前，这个事务它依然持有 MDL，所以 DDL 操作依然会被阻塞。

排查思路

这个时候我们可以通过查找performance_schema.events_statements_current表来找到相关的语句和会话信息，然后 kill 掉

-- 使用 sys.schema_table_lock_waits 排查的时候一定要注意认真看
select * from sys.schema_table_lock_waits ;

SELECT * FROM PERFORMANCE_SCHEMA.events_statements_current WHERE SQL_TEXT LIKE '%dim_admin_area%' ;



SELECT
    *
FROM
    information_schema.`PROCESSLIST`
WHERE
    id IN ( SELECT PROCESSLIST_id FROM PERFORMANCE_SCHEMA.threads WHERE thread_id IN ( SELECT thread_id FROM PERFORMANCE_SCHEMA.events_statements_current WHERE SQL_TEXT LIKE '%dim_admin_area%' ) )
总结

MySQL 在进行一些 alter table 等 DDL 操作时，如果该表上有未提交的事务（无论正常事务还是失败事务）都会出现 Waiting for table metadata lock ，而一旦出现 metadata lock，该表上的后续操作都会被阻塞。

Online DDL

在 MySQL 使用过程中，根据业务的需求对表结构进行变更是个普遍的运维操作，这些称为 DDL 操作。常见的 DDL 操作有在表上增加新列，或给某个列添加索引。

给一个表加字段，或者修改字段，或者加索引，都需要扫描全表的数据。对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。因为大表 DDL 往往会耗时很久。

从前面的描述可以得到一个结论：事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

如何安全的给一个表进行 DDL 呢？

首先要解决的是长事务：事务不提交，就会一直占着 MDL 锁。在 information_schema.innodb_trx 中，可以查到当前执行中的事务。

如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

在 MySQL 5.6 之前，MySQL 的 DDL 操作会按照原来的表复制一份，并做相应的修改。

例如，对表 A 进行 DDL 的内部实现具体过程如下：

  • 按照表 A 的定义新建一个表 B
  • 对表 A 加写锁
  • 在表 B 上执行 DDL 指定的操作
  • 将 A 中的数据拷贝到 B
  • 释放 A 的写锁
  • 删除表 A
  • 将表 B 重命名为 A

在 2-4 的过程中，如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。

此外，由于 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。

MySQL 5.6 版本引入了 Online DDL 特性。

在 MySQL 5.6 之前，执行 DDL 的唯一方法是逐行复制行。INPLACE DDL 主要由 InnoDB 处理，而逐行 COPY 在服务器层处理。

这种情况，往往需要对整个表加锁，这会导致长时间的阻塞，影响业务的正常运行。

在 MySQL 5.7，Online DDL 在性能和稳定性上不断得到优化，比如通过 bulk load 方式来去除表重建时的 redo 日志等。

到了 MySQL 8.0，Online DDL 已经支持秒级加列特性，该特性来源于国内的腾讯互娱 DBA 团队。

查阅官方文档得知，快速加列即 Instant Add Column ，该功能自 MySQL 8.0.12 版本引入，是由腾讯游戏 DBA 团队贡献。注意一下，此功能只适用于 InnoDB 表。

基本上，在 MySQL8.0 上，不需要再用 pt-osc 和 gh-ost 等工具，大多数情况都可以直接 online DDL 了。

概括来说，在 MySQL 8.0 上，Online DDL 有 2 种划分维度（其实就是两种参数）：

  • 一是 DDL 期间运行的并发程度
  • 二是 DDL 的执行算法

先说 DDL 时的**业务 DML 操作运行程度**（Permits Concurrent DML），可以通过 LOCK 关键字来指定 DDL 期间加锁程度，可选：

  • LOCK=NONE 允许查询和 DML 操作；以避免在冗长的 DDL 操作期间使表不可用。
  • LOCK=SHARED 允许查询，不允许 DML 操作；
  • LOCK=DEFAULT 由系统决定，选择最宽松的模式（默认是这种）；
  • LOCK=EXCLUSIVE 阻止并发查询和 DML。在尽可能短的时间内完成 DDL 操作，并且不需要并发查询和 DML 访问，则使用此子句。

另一种划分方式为是否拷贝数据，可分为如下几种：

  • 仅修改元数据：包括修改表名，字段名等；
  • ALGORITHM=COPY：采用拷表方式进行表变更，与 pt-osc/gh-ost 类似；
  • ALGORITHM=INPLACE：仅需要进行引擎层数据改动，不涉及 Server 层；
  • ALGORITHM=INSTANT：与第一种方式类似，仅修改元数据。目前仅支持在表最后增加新列；
  • ALGORITHM=DEFAULT：由系统决定，选择最优的算法执行 DDL。 用户可以选用上述算法来执行，但本身受到 DDL 类型限制，如果指定的算法无法执行 DDL，则 ALTER 操作会报错。
-- 索引的增删改查（只能新增和删除，不能直接改）
-- 在线加索引：操作期间，可以正常读写。
CREATE INDEX name ON table (col_list) ;

ALTER TABLE tbl_name ADD INDEX name (col_list) ;
-- 创建普通索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
-- 创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column_list);

CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);






-- 修改字段数据类型（修改字段长度）
-- ALTER  TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度  新默认值  新注释;
alter table db_qhdata_policy.temp_policy_org_base  MODIFY   apprdate   varchar(50) ;
-- 即使是大表，alter线程持续 copy to tmp table 状态很长时间， alter并没有阻塞其他线程的读请求






-- 字段重命名
ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

从流程上看，Online DDL 可分为 3 个阶段：

  • 初始化阶段，确定 DDL 操作支持的最优 LOCK 和 ALGORITHM 设置，并与用户指定的设置相比，若无法办到则报错；
  • 执行阶段，如果需要拷表或修改引擎层数据，则该阶段是最耗时的阶段；
  • 提交阶段，该阶段会加锁进行新旧表切换；

目前可用的 DDL 操作工具包括

  • Percona 开源的 pt-osc
  • github 开源的 gh-ost，
  • MySQL 原生提供的在线修改表结构命令 Online DDL。

pt-osc 和 gh-ost 均采用拷表方式实现，即创建个空的新表，通过 select+inser t 将旧表中的记录逐次读取并插入到新表中。

不同之处在于处理 DDL 期间业务对表的 DML 操作（增删改）。

从 MySQL 8.0.28 开始, InnoDB 支持 ALTER TABLE ... RENAME COLUMN 操作使用 ALGORITHM=INSTANT。

从 MySQL 8.0.29 开始, InnoDB 支持 ALTER TABLE ... DROP COLUMN 操作使用 ALGORITHM=INSTANT。

在 8.0.29 之前， instant 添加列，只能是加在表的最后一列，从 8.0.29 开始，可以在表的任意位置添加。

在 8.0.29 之后，2 千万的表在任一位置即时添加列在秒级内完成。

ALTER TABLE sbtest1 ADD COLUMN k2 int(10) AFTER k,ALGORITHM=INSTANT;

MySQL 8.0.29 开始，ALTER TABLE … ALGORITHM=INSTANT 支持删除某列。

为了支持 ALTER TABLE … ALGORITHM=INSTANT 的新特性，InnoDB redo log 格式对于所有 DML 操作都发生了变化。

新的 redo 日志格式引入了一个设计缺陷，会导致 instant add/drop columns 的表数据损坏。

https://opensource.actionsky.com/20220809-mysql/

https://zhuanlan.zhihu.com/p/115277009

https://blog.csdn.net/weixin_45238761/article/details/125343029

https://cloud.tencent.cn/developer/article/2407101

MySQL 原子 DDL

在 MySQL8.0 之前的版本中，这些元数据被存放在许多不同的文件中（.FRM，.PAR，.OPT，.TRN，.TRG 文件等），这就导致了一系列弊端，包括数据可能不一致、API 接口的复杂性等等，在之前的月报中也有详细描述。元数据被放在许多不同的文件中，导致数据可能不一致的具体表现为：

  • Server 层的 metadata 和 Storage Engine 层的 metadata 数据不一致

  • InnoDB 中的 metadata 和数据不一致

  • Binlog 和数据不一致

MySQL 5.6/5.7 的用户可能会发现，create 一张表过程中发生 crash，重启后创建一张同名新表时，会发现创建失败。这是因为过去 MySQL 5.6/5.7 的 DDL 操作不是原子的，一张表创建失败后会遗留下 ibd 文件。

对于复合的 DDL，比如 DROP TABLE t1, t2;执行过程中如果遇到 server crash，有可能出现表 t1 被 DROP 掉了，但是 t2 没有被 DROP 掉的情况。

即便是一条 DDL，比如 CREATE TABLE t1(a int);也可能在 server crash 的情况下导致建表不完整，有可能在建表失败的情况下遗留.frm 或者.ibd 文件。

MySQL 8.0 对 DDL 的实现重新进行了设计，最大的改进是 DDL 操作支持原子特性。由于 MySQL 是一个多引擎数据库，在 engine 层（SE）和 server 层（SL）都维护了自己的数据字典对象。

MySQL 8.0 之前的版本 DDL 是非原子性的，对于多条 sql 构成的 ddl 语句比如

rename table t1 to t1_bak,t2 to t2_bak;

执行过程中如果遇到系统异常 crash，有可能出现表 t1 被 rename，但是 t2 没有被 rename 的情况。出现该情况的原因就是 MySQL 不支持原子的 DDL。

为了实现 DDL 原子性，MySQL 8.0 使用 Innodb 表存储相关的数据字典信息，这些数据字典表默认不可见，查看方法参照文档

什么是原子 DDL?

当执行 DDL 时，数据字典更新、存储引擎操作和二进制日志中的写操作被合并到一个原子事务中，该事务要么完全执行，要么完全不执行。

这提供了更好的可靠性，未完成的 ddl 不会留下任何不完整的数据。

原子 DDL 不是事务 DDL。DDL 语句，无论是原子语句还是其他语句，都会隐式地结束当前会话中活动的任何事务，就像在执行语句之前执行了 COMMIT 一样。这意味着 DDL 语句不能在另一个事务、事务控制语句（如 START TRANSACTION ... COMMIT ）中执行，也不能与同一事务中的其他语句结合执行。

-- MySQL5.7
START TRANSACTION;
    select * from mytest.t2_34;
    alter table mytest.t2_34  ADD INDEX idx_b(b) USING BTREE;
    -- 对于MySQL5.7，如果事务中包含DDL操作，mysql会在DDL操作语句执行后，隐式提交commit。以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放
    select * from mytest.t2_34;
ROLLBACK; --这里的rollback语句其实已经不能回滚上面的DDL了，因为已经提交了。


--对于MySQL8.0，执行上述语句。发现会出现MDL阻塞

--然后查询会话和事务，发现是被一个sleep阻塞
 select * from information_schema.processlist;
 select * from information_schema.innodb_trx;

-- 对于这个  5.7和8.0的表现是一致的
START TRANSACTION;
    select * from db_test.test;
    create  table db_test.test1  like db_test.test;
    create  table db_test.test1  like db_test.test;
    select * from db_test.test1;
ROLLBACK;

https://www.cnblogs.com/radondb/p/16688994.html

支持的 DDL 语句

原子 DDL 支持与表相关的 DDL 语句和与表无关的 DDL 语句。

与表相关的 DDL 操作需要存储引擎的支持，而与表无关的 DDL 操作不需要存储引擎的支持。目前，只有 InnoDB 存储引擎支持原子 DDL。

与表相关的原子 DDL 包括数据库、表空间、表、索引的 CREATE、ALTER 以及 DROP 语句，以及 TRUNCATE TABLE 语句。

与表无关的原子 DDL 包括：

存储过程、触发器、视图以及用户定义函数（UDF）的 CREATE 和 DROP 语句，以及适用的 ALTER 语句。

帐户管理语句：用户和角色的 CREATE、ALTER、DROP 语句，以及适用的 RENAME 语句，以及 GRANT 和 REVOKE 语句。

以下语句不支持原子 DDL 操作：

非 InnoDB 存储引擎上的表相关 DDL 语句。

INSTALL PLUGIN 和 UNINSTALL PLUGIN 语句。 INSTALL COMPONENT 和 UNINSTALL COMPONENT 语句。 CREATE SERVER、ALTER SERVER 以及 DROP SERVER 语句。

原子 DDL 的特性

原子 DDL 语句具有以下特性：

  • 与 DDL 操作相关的数据字典更新、存储引擎操作和二进制日志写入合并为一个单一的原子操作。

即使服务器在操作过程中停止，该操作也会提交，并将适用的更改持久化到数据字典、存储引擎和二进制日志中，或者回滚。

将可能存在的元数据更新、二进制日志写入以及存储引擎操作组合成单个事务。

DDL 操作过程中不存在 SQL 层的中间提交操作。

如果存在的话： 数据字典、过程、事件以及用户定义函数的缓存状态与 DDL 操作的状态一致，意味着 DDL 操作成功或者回滚时，缓存都会进行相应更新。 DDL 操作涉及的存储引擎相关修改不会执行中间的提交操作，而是作为 DDL 事务的一部分进行处理。 存储引擎支持 DDL 操作的重做和回滚，这些操作发生在 DDL 操作的 Post-DDL 阶段。

从 MySQL 8.0.21 开始，在支持原子 DDL 的存储引擎上，当使用基于行的复制时， CREATE TABLE ... SELECT 语句将作为一个事务记录在二进制日志中。以前，它被记录为两个事务，一个用于创建表，另一个用于插入数据。

如果服务器在两个事务之间或插入数据时发生故障，就会导致复制出一个空表。随着原子 DDL 支持的引入， CREATE TABLE ... SELECT 语句现在可以安全地用于基于行的复制，并允许用于基于 GTID 的复制。