KWAN的解忧杂货铺

# 1.事务的分类?

从事务理论的角度来说，可以把事务分为以下几种类型:

  • 扁平事务(Flat Transactions)
  • 带有保存点的扁平事务(Flat Transactions with Savepoints)
  • 链事务(Chained Transactions)
  • 嵌套事务(Nested Transactions)
  • 分布式事务(Distributed Transactions)

# 2.什么是事务的 ACID?

ACID 属性：MySQL 是一个支持 ACID（原子性、一致性、隔离性、持久性）属性的数据库管理系统。这些属性确保了一致性。具体来说：

  • 原子性（Atomicity）：一个事务中的所有操作要么全部执行成功，要么全部失败回滚，不会出现部分执行的情况。
  • 一致性（Consistency）：事务执行前后数据库必须保持一致状态。
  • 隔离性（Isolation）：多个事务并发执行时，每个事务看起来好像是在独立的环境中执行，不会相互干扰。
  • 持久性（Durability）：一旦事务提交，其结果将永久保存在数据库中，即使系统故障也不会丢失。

实现方式:

  • 原子性:通过事务和 bin log 和 redo log 来保障
  • 一致性: 通过 undo log 和各种锁和事务来保障
  • 隔离性: 通过 undo log 和 mvcc 保障
  • 持久性:通过 redo log 来保障
#查看提交模式
SELECT @@AUTOCOMMIT;

#是否开启自动提交
SHOW VARIABLES LIKE 'autocommit';
1
2
3
4
5

在 MySQL 中，AUTOCOMMIT是一个系统变量，用于确定是否将每个 SQL 语句作为一个单独的事务自动提交。当AUTOCOMMIT为 1 时，每个 SQL 语句都将作为一个独立的事务自动提交，即执行完即提交。当AUTOCOMMIT为 0 时，需要手动使用COMMIT语句提交事务或使用ROLLBACK语句回滚事务。

# 3.mysql 事务隔离级别?

SQL 标准定义的四个隔离级别为：

  • READ UNCOMMITTED （导致脏读）
  • READ COMMITTED （导致幻读）
  • REPEATABLE READ （默认使用，避免脏读和幻读）
  • SERIALIZABLE （更高级别隔离，避免脏读和幻读）
#查看隔离级别
SELECT @@transaction_isolation;
1
2

InnoDB 存储引擎默认支持的隔离级别是 REPEATABLE READ，但是与标准 SQL 不同的是，InnoDB 存储引擎在 REPEATABLE READ事务隔离级别下，使用 Next-Key Lock 锁的算法，因此避免幻读的产生。这与其他数据库系统（如 Microsoft SQL Server 数据库）是不同的。所以说，InnoDB 存储引擎在默认的 REPEATABLE READ 的事务隔离级别下已经能完全保证事务的隔离性要求，即达到 SQL 标准的 SERIALIZABLE 隔离级别。

隔离级别越低，事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是 READ COMMITTED。

在 InnoDB 存储引擎中，可以使用以下命令来设置当前会话或全局的事务隔离级别：

SET
[GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
|READ COMMITTED
|REPEATABLE READ
|SERIALIZABLE
};
1
2
3
4
5
6
7
8
#设置全局
set global transaction isolation level 隔离级别名称;
#设置session
set session transaction isolation level 隔离级别;
#设置单词
set transaction isolation level 隔离级别;
1
2
3
4
5
6

在 SERIALIABLE 的事务隔离级别，InnoDB 存储引擎会对每个 SELECT 语句后自动加上 LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。

# 4.查看隔离接别

show variables like '%tran%'
1

image-20230920210704995

# 5.RR 和 RC 的区别？

"RR"和"RC"是在数据库中用于描述事务隔离级别的缩写：

  1. RR - Repeatable Read（可重复读）： 在 RR 隔离级别下，一个事务在读取数据时会对其加锁，以防止其他事务修改这些数据。这意味着在一个事务内，多次读取同样的数据将得到相同的结果，即使其他事务对数据进行了修改。在 RR 隔离级别下，读取的数据是一致的，但可能导致其他事务的阻塞，因为锁定的数据在事务提交或回滚之前不能被其他事务访问。
  2. RC - Read Committed（读取已提交）： 在 RC 隔离级别下，一个事务在读取数据时不会对其加锁，因此允许其他事务修改这些数据。在 RC 隔离级别下，读取的数据是已提交的数据，即其他事务已经对其进行了提交。这使得在 RC 隔离级别下，读取的数据可能不是一致的，因为其他事务可以在事务执行期间修改数据。RC 隔离级别通常比 RR 隔离级别具有更高的并发性，因为没有锁定数据的操作。

综上所述，RR 隔离级别提供了较高的数据一致性，但可能导致较多的锁冲突和阻塞，从而影响并发性能。而 RC 隔离级别提供了更高的并发性，允许其他事务修改数据，但可能导致读取的数据不一致。选择哪个隔离级别取决于具体的应用需求和对数据一致性和并发性的权衡。在大多数情况下，Read Committed 是一个常见的隔离级别，因为它提供了较好的性能和合理的一致性。

这两个隔离级别的⼀个很⼤不同就是： ⽣成 ReadView 的时机不同 ，READ COMMITTD 在每⼀次进⾏普通 SELECT 操作前都会⽣成⼀个 ReadView，⽽ REPEATABLE READ 只在第⼀次进⾏普通 SELECT 操作前⽣成⼀个 ReadView，数据的可重复读其实就是 ReadView 的重复使⽤。

# 6.mysql 分布式事务?

InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置 SERIALIZABLE。

XA 事务允许不同数据库之间的分布式事务，如一台服务器是 MySQL 数据库的，另一台是 Oracle 数据库的，又可能还有一台服务器是 SQLServer 数据库的，只要参与在全局事务中的每个节点都支持 XA 事务。分布式事务可能在银行系统的转账中比较常见，如用户 David 需要从上海转 10000 元到北京的用户 Mariah 的银行卡中：

#Bank@Shanghai：
UPDATE account SET money=money-10000 WHERE user='David';

#Bank@Beijing
UPDATE account SET money=money+10000 WHERE user='Mariah';
1
2
3
4
5

在这种情况下，一定需要使用分布式事务来保证数据的安全。如果发生的操作不能全部提交或回滚，那么任何一个结点出现问题都会导致严重的结果。要么是 David 的账户被扣款，但是 Mariah 没收到，又或者是 David 的账户没有扣款，Mariah 却收到钱了。

XA 事务由一个或多个资源管理器（Resource Managers）、一个事务管理器（Transaction Manager）以及一个应用程序（Application Program）组成。

  • 资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
  • 事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
  • 应用程序：定义事务的边界，指定全局事务中的操作。

在 MySQL 数据库的分布式事务中，资源管理器就是 MySQL 数据库，事务管理器为连接 MySQL 服务器的

客户端。下图显示了一个分布式事务的模型。

image-20231022232920195

分布式事务使用两段式提交（two-phase commit）的方式。在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行 ROLLBACK 还是 COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次的 PREPARE 操作，待收到所有节点的同意信息后，再进行 COMMIT 或是 ROLLBACK 操作。

MySQL 数据库 XA 事务的 SQL 语法如下：

XA{START|BEGIN}xid[JOIN|RESUME]XAENDxid[SUSPEND[FORMIGRATE]]XAPREPARExid
XACOMMITxid[ONEPHASE]
XAROLLBACKxid
XARECOVER
1
2
3
4

# 7.mysql 自身有的分布式事务?

最为常见的内部 XA 事务存在于 bin log 与 InnoDB 存储引擎之间。由于复制的需要，因此目前绝大多数的数据库都开启了 bin log 功能。在事务提交时，先写二进制日志，再写 InnoDB 存储引擎的重做日志。对上述两个操作的要求也是原子的，即二进制日志和重做日志必须同时写入。若二进制日志先写了，而在写入 InnoDB 存储引擎时发生了宕机，那么 slave 可能会接收到 master 传过去的二进制日志并执行，最终导致了主从不一致的情况。

image-20231021173458653

如果执行完 ①、② 后在步骤 ③ 之前 MySQL 数据库发生了宕机，则会发生主从不一致的情况。为了解决这个问题，MySQL 数据库在 bin log 与 InnoDB 存储引擎之间采用 XA 事务。当事务提交时，InnoDB 存储引擎会先做一个 PREPARE 操作，将事务的 xid 写入，接着进行二进制日志的写入。

image-20231021173517982

如果 innodb 存储引擎提交前，MySQL 数据库宕机了，那么 MySQL 数据库在重启后会先检查准备的 UXID 事务是否已经提交，若没有，则在存储引擎层再进行一次提交。

# 8.隐藏列

在内部，InnoDB 为存储的每行数据，增加了如下 3 个字段：

  1. DB_TRX_ID（6 字节）：称为”事务 ID“，标记 insert 或 update 该行数据的最后一个事务的事务 ID。此外，delete 操作在 InnoDB 内部被视为 update。InnoDB 通过标记行中的特殊位（bit）来表示”已删除“。
  2. DB_ROLL_PTR（7 字节）：称为”回滚指针（roll pointer）“，用于指向 undo tablespace 中回滚段（rollback segment）的一条 undo log 记录。若数据行被更新，则该指针指向的 undo log 中包含重建更新之前该行数据所需的信息。回滚段中包含 insert undo logs 和 update undo logs。 Insert undo logs 只在事务回滚时需要，一旦事务提交就可以被丢弃（discarded ）。update undo logs 除了事务回滚时需要之外，也用于构建 InnoDB 一致性读。在一致性读中，需要 update undo logs 中的信息来构建早期版本的数据行。
  3. DB_ROW_ID（6 字节）：称为”行 ID“， 是 MySQL 实例中全局（单个表内，可能不连续）分配的单调递增的值。即作为无主键表的隐式主键。当表中存在仅由 单个整型列 构成的 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，则可在 SELECT 语句中使用 _rowid 来引用索引列的值。即 _rowid 实际为索引列的别名。

以上 3 个隐藏字段，可通过 ibd2sdi（MySQL 8.0 开始提供） 工具来查看。

# 9.显示_rowid

当表中存在仅由 单个整型列 构成的 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，则 _rowid 隐藏列实际为索引列的引用。可在 SELECT 语句中使用 _rowid 来查询索引列的值。这种 _rowid 称为“显式_rowid”。

_rowid 需要表具有的 3 个必备要素：

1. 单列索引
2. 数据类型为整型
3. 索引类型为 `PRIMARY KEY` 或 `UNIQUE NOT NULL` 索引
1
2
3

以下示例中，可以通过 SELECT 语句直接查询的 _rowid，称为 ”显式 _rowid“。

## 1. 单列整型主键的表中，包含隐藏列 _rowid
mysql> CREATE TABLE t_pk(id INT PRIMARY KEY, name VARCHAR(32)) SELECT id, name FROM (VALUES ROW(1,'one'), ROW(2,'two')) AS v(id, name);

mysql> SELECT id, name, _rowid FROM t_pk;
+----+------+--------+
| id | name | _rowid |
+----+------+--------+
|  1 | one  |      1 |
|  2 | two  |      2 |
+----+------+--------+
2 rows in set (0.00 sec)

## 2. 单列整型非空唯一索引的表中，包含隐藏列 _rowid
mysql> CREATE TABLE t_unik(id INT NOT NULL, name VARCHAR(32), UNIQUE KEY(id)) SELECT id, name FROM (VALUES ROW(3,'three'), ROW(4,'four')) AS v(id, name);

mysql> SELECT id, name, _rowid FROM t_unik;
+----+-------+--------+
| id | name  | _rowid |
+----+-------+--------+
|  3 | three |      3 |
|  4 | four  |      4 |
+----+-------+--------+
2 rows in set (0.00 sec)

## 3. 不满足 `单个整型列` 构成的 `主键` 或 `非空唯一索引` 时，表中没有 _rowid 列。
mysql> CREATE TABLE t_c_unik(id VARCHAR(8) NOT NULL, name VARCHAR(32), UNIQUE KEY(id)) SELECT id, name FROM (VALUES ROW('a','three'), ROW('b','four')) AS v(id, name);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT id, name, _rowid FROM t_c_unik;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

# 10.隐式_rowid

当表中未设置主键时，InnoDB 会为数据行生成一个 6 字节长度（最大为 $2^{48}$-1，即 281474976710655）的无符号数（dictsys.row_id）作为 _rowid 列的值，只是这个 _rowid 无法显式通过 SELECT 获取。此种情况的 _rowid 称为 ”隐式 _rowid“。此_rowid 也是表的隐式主键。

分配的无符号数由 InnoDB 变量 dictsys.row_id 在 MySQL 实例中全局分配（由所有无主键的表共享）。当自增到最大值 $2^{48}$-1 后，会重新复位从 0 开始。当表中出现相同的 _rowid 时，新插入的数据会根据 _rowid 覆盖掉原有的旧数据。现象类似于根据 _rowid 进行更新覆盖。

安装 gdb 工具，可用于修改全局变量 dictsys.row_id 的值。

image-20230905001911260

  • 当 dictsys.row_id = 1，向无主键表插入数据时，InnoDB 将 dictsys.row_id = 1 的值作为数据行的隐式主键，并逐行自增。因此，第一次插入的 3 行数据的 _rowid 值分别为 1、2、3；
  • 当 dictsys.row_id = 281474976710656（即 $2^{48}$）， 已超过最大值 $2^{48}$-1。InnoDB 在插入数据时将 dictsys.row_id 复位为 0。因此，第二次插入的 3 行数据的 _rowid 值分别为 0、1、2；
  • 第二次插入的 3 行数据与第一次插入的 3 行数，_rowid 出现了重复值（即 1、2）。于是，InnoDB 在第二次插入数据时，根据 _rowid 的值覆盖了第一次插入的 2 行数据（图中蓝色箭头部分）。

# 11.自增 ID 用完怎么办?

指定了自增主键:

可以在创建表的时候，直接声明 AUTO_INCREMENT 的初始值。4294967295 是 2 的 32 次方减去 1。

具体来说：2^32 - 1 = 4294967296 - 1 = 4294967295

create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967295;
1

当再次插入时，使用的自增 ID 还是 4294967295，报主键冲突的错误,主键重复了。

insert into t1 values(null);
1
SQL 错误 [1062] [23000]: Duplicate entry '4294967295' for key 'chatbot_3.PRIMARY'
1

没有指定主键:

如果是这种情况，InnoDB 会自动帮你创建一个不可见的、长度为 6 字节的 row_id，而且 InnoDB 维护了一个全局的 dictsys.row_id，所以未定义主键的表都共享该 row_id，每次插入一条数据，都把全局 row_id 当成主键 id，然后全局 row_id 加 1

该全局 row_id 在代码实现上使用的是 bigint unsigned 类型，但实际上只给 row_id 留了 6 字节，这种设计就会存在一个问题：如果全局 row_id 一直涨，一直涨，直到 2 的 48 幂次-1 时，这个时候再+1，row_id 的低 48 位都为 0，结果在插入新一行数据时，拿到的 row_id 就为 0，存在主键冲突的可能性。

所以，为了避免这种隐患，每个表都需要定一个主键。

# 12.Update 语句执行过程

UPDATE 语句是用于修改数据库表中已有数据的 SQL 语句。以下是 UPDATE 语句执行的一般过程，具体细节可能根据数据库管理系统（DBMS）的不同而有所不同：

  1. 解析 SQL 语句： 首先，数据库管理系统会解析 UPDATE 语句，以确定要修改的目标表，要更新的列以及更新条件。解析过程还包括验证表和列的存在以及权限检查，确保用户有权执行这个更新操作。

  2. 执行 WHERE 条件： 如果 UPDATE 语句包含 WHERE 子句，数据库会执行这个子句，以确定哪些行将被更新。只有满足 WHERE 条件的行才会被更新。如果没有指定 WHERE 条件，UPDATE 将会影响表中的所有行，这通常是一个危险的操作，因此要谨慎使用。

  3. 锁定行： 在开始更新之前，数据库通常会锁定满足 WHERE 条件的行，以确保在更新过程中没有其他会话可以修改这些行。这可以防止并发问题，如丢失更新或死锁。

  4. 执行更新： 一旦行被锁定，数据库会执行实际的更新操作。这包括将新数据写入表中，用新值替换旧值。更新可以包括单个列或多个列，具体取决于 UPDATE 语句中的设置。

  5. 事务处理： 更新通常是在事务内执行的，这意味着它们要么全部成功，要么全部失败。如果有任何问题（例如，约束违反、数据类型错误等），事务将回滚，不会更改数据。否则，如果一切正常，事务将提交，数据将永久性地更新。

  6. 释放锁： 在事务完成后，数据库会释放行级锁，允许其他会话访问这些行。

总的来说，UPDATE 语句的执行过程包括解析 SQL 语句、确定要更新的行、锁定行以防止并发问题、执行实际的更新操作，然后通过事务来确保操作的原子性和一致性。这些步骤有助于维护数据库的完整性和数据的一致性。不同的数据库管理系统可能会有不同的优化和实现细节，但这是一般情况下的执行过程。

# 13.for update 的原理

SELECT ... FOR UPDATE 是一种在 SQL 数据库中常见的查询模式，用于锁定一或多行数据以便进行更新。当你执行一个 SELECT ... FOR UPDATE 查询时，数据库将对选中的行加锁，直到事务结束（提交或回滚）才会释放。这样做的目的是为了防止多个事务并发修改相同的数据，从而保证数据的一致性。

工作原理:

假设有一个简单的 users 表：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    balance INT
);
1
2
3
4
5

现在假设你想要更新某个用户的 balance 字段。在这种情况下，你可能会使用以下查询：

SELECT balance FROM users WHERE id = 1 FOR UPDATE;
1

执行这个查询后，数据库将锁定 id = 1 的那一行。其他事务如果也试图对这一行进行 FOR UPDATE 查询或更新，将会被阻塞，直到该锁被释放。

然后，在同一事务中，你可以安全地更新这一行：

UPDATE users SET balance = balance - 100 WHERE id = 1;
1

最后，你可以提交这个事务，从而释放锁并使得其他事务可以访问这一行。

COMMIT;
1

注意事项:

  1. 死锁风险: 如果多个事务试图以不同的顺序锁定多行，可能会出现死锁。解决这个问题的一种方法是总是以相同的顺序锁定行。

  2. 隔离级别: SELECT ... FOR UPDATE 的行为可能会因数据库的隔离级别而异。在某些隔离级别下，它可能不会如你所预期的那样工作。

  3. 数据库支持: 并非所有的数据库都支持 SELECT ... FOR UPDATE。对于不支持的数据库，你需要使用其他机制来实现相同的效果。

  4. 锁的范围: 在一些数据库中，你可以通过 NOWAIT 或其他选项来控制锁的行为。例如，SELECT ... FOR UPDATE NOWAIT 将会立即报错，如果所选行已经被另一个事务锁定。

  5. 索引: 如果可能，使用索引来加速 SELECT ... FOR UPDATE 查询。如果没有合适的索引，数据库可能需要进行全表扫描，这将对性能产生严重影响。

  6. 只锁定需要的行: 为了提高性能和减少锁冲突，尽量只锁定你确实需要更新的行。

通过合理地使用 SELECT ... FOR UPDATE，你可以在并发环境中更安全地进行数据修改。

# 14.Mysql 执行流程

  • 连接器：建立连接，管理连接、校验用户身份；
  • 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
  • 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
  • 执行 SQL：执行 SQL 共有三个阶段：
    • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
    • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
    • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

image-20230921171953431

# 15.什么是词法解析器

解析器会做如下两件事情。

第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，例如，SQL 语句 select username from userinfo，在分析之后，会得到 4 个 Token，其中有 2 个 Keyword，分别为 select 和 from：

关键字 非关键字 关键字 非关键字
select username from userinfo

第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

image-20230921172146072

如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错。比如，我下面这条查询语句，把 from 写成了 form，这时 MySQL 解析器就会给报错。

上次更新: 11/2/2024, 2:13:44 PM

← 04-MySQL分库分表 →