RDBMS高级技术之数据库事务

举报
tea_year 发表于 2024/01/16 22:39:12 2024/01/16
【摘要】 本章学习目标​ 理解事务的概念​ 熟练掌握事务的相关操作​ 了解分布式事务的原理和语法在数据库操作中，有些数据对数据的完整性要求高，例如有关钱的操作，必须保证数据的完整性，数据不能出现差错或者丢失的情况，为了解决这一问题，可以使用数据库事务保证数据的完整性，本章将对数据库事务详细讲解。11.1​ 事务管理事务处理机制在程序开发中有非常重要的作用，可以使整个系统更安全，接下来将针对事务的...

本章学习目标

​ 理解事务的概念

​ 熟练掌握事务的相关操作

​ 了解分布式事务的原理和语法

在数据库操作中，有些数据对数据的完整性要求高，例如有关钱的操作，必须保证数据的完整性，数据不能出现差错或者丢失的情况，为了解决这一问题，可以使用数据库事务保证数据的完整性，本章将对数据库事务详细讲解。

11.1​ 事务管理

事务处理机制在程序开发中有非常重要的作用，可以使整个系统更安全，接下来将针对事务的概念和相关管理操作进行详细讲解。

11.1.1​ 事务的概念和使用

在现实生活中，转账汇款是很常见的操作，这实际上就是数据库中两个账户间的数据操作，例如A给B转账1000元，就是A账户的金额减去1000，B账户的金额加上1000，需要使用两条SQL语句完成操作，但是，若其中一条SQL语句出现异常没有执行，则会导致两个账户的金额不同步，数据出现错误。

为了防止上述情况的发生，MySQL中引入了事务，事务是指数据库中的一个操作序列，它由一条或多条SQL语句所组成，这些SQL语句不可分割，只有当事务中的所有SQL语句都被成功执行后，整个事务引发的操作才会被更新到数据库，如果有至少一条语句执行失败，所有操作都将会被取消。

在使用事务前，首先要开启事务，开启事务的SQL语句如下所示。

START TRANSACTION;

以上语句用于开启事务，事务开启后就可以执行SQL语句，SQL语句执行完成后，需要提交事务，提交事务的SQL语句如下所示。

COMMIT;

以上语句用于提交事务，MySQL中SQL语句是默认自动提交的，而事务中的操作语句都需要使用COMMIT语句手动提交，提交完成后，事务才会生效。如果不想提交事务，可以回滚事务，回滚事务的SQL语句如下所示。

ROLLBACK;

以上语句用于事务回滚，但该语句只能回滚未提交的事务操作，不能回滚已提交的事务操作。

接下来通过具体案例演示转账汇款的事务操作，首先需要创建一个账户表account，首先来了解一下表结构，如表11.1所示。

表11.1​ account表

字段

字段类型

说明

id

int

账户编号

name

varchar(30)

账户姓名

money

float

账户余额

表11.1中列出了account表的字段、字段类型和说明，接着创建account表，SQL语句如下所示。

mysql> CREATE TABLE account(
-> id INT PRIMARY KEY,
-> name VARCHAR(30),
-> money FLOAT
-> );

Query OK, 0 rows affected (0.16 sec)

account表创建完成后，向表中插入数据，SQL语句如下所示。

mysql> INSERT INTO account

-> VALUES
-> (1,'A',1000),
-> (2,'B',1000),
-> (3,'C',1000);

Query OK, 3 rows affected (0.06 sec)

Records: 3 Duplicates: 0 Warnings: 0

以上执行结果证明数据插入完成，可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 1000 |

| 2 | B | 1000 |

| 3 | C | 1000 |

+----+------+-------+

3 rows in set (0.02 sec)

从以上执行结果可看出，一共有三个账户，存款金额都为1000，接下来通过具体案例演示如何实现转账汇款功能。MySQL是默认自动提交事务，可以通过SHOW VARIABLES语句查看，SQL语句如下所示。

mysql> SHOW VARIABLES LIKE 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.00 sec)

从以上执行结果可看出，MySQL事务自动提交是开启的状态，如果进行事务操作，需要将自动提交关闭，SQL语句如下所示。

mysql> SET autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

以上执行语句中，0代表OFF，反之1代表ON，通过SHOW VARIABLES语句验证自动提交是否关闭，SQL语句如下所示。

mysql> SHOW VARIABLES LIKE 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.00 sec)

从以上执行结果可看出，MySQL的自动提交事务已关闭。

例11-1​ 通过事务操作，实现账户A转账给账户B一百元钱，SQL语句如下所示。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE account SET money=money-100

-> WHERE name='A';
Query OK, 1 row affected (0.15 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE account SET money=money+100
-> WHERE name='B';
Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0


mysql> COMMIT;

Query OK, 0 rows affected (0.03 sec)

以上执行结果证明汇款成功，首先使用START TRANSACTION语句开启事务，然后执行了更新操作，将A账户减少100元，B账户增加100元，最后使用COMMIT语句提交事务，此时可查看表中数据进行验证，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+
| id | name | money |
+----+------+-------+

| 1 | A | 900 |

| 2 | B | 1100 |

| 3 | C | 1000 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看出，通过事务操作，实现了转账。需要注意的是，如果在执行转账操作过程中，数据库出现故障，为了保证事务的同步性，则事务不会提交，接下来通过具体案例演示这种情况。

例11-2​ 通过事务操作，实现账户A转账给账户C一百元钱，当账户A的数据操作完成后，关闭数据库客户端，模拟数据库宕机，SQL语句如下所示。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)


mysql> UPDATE account SET money=money-100

-> WHERE name='A';

Query OK, 1 row affected (0.11 sec)

Rows matched: 1 Changed: 1 Warnings: 0

以上执行结果可看出，事务开启后，账户A减去了100元，此时可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+
| id | name | money |
+----+------+-------+

| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1000 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看出，账户A的余额从900元变为了800元，账户A的转账操作完成，此时关闭MySQL的客户端，重新进入，再次查看表account中的数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+
| id | name | money |
+----+------+-------+

| 1 | A | 900 |

| 2 | B | 1100 |

| 3 | C | 1000 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看出，账户A的余额又恢复到900，因为利用事务的转账操作没有全部完成，出现了错误，所以为了保证数据的同步性，没有提交前的数据操作都被回退，这就是事务的作用。

11.1.2​ 事务的回滚

在操作一个事务时，如果发现某些操作是不合理的，只要事务还没有提交，就可以通过ROLLBACK语句进行回滚，接下来通过具体案例演示事务的回滚操作。

例11-3​ 通过事务操作，实现账户B转账给账户C一百元钱，当转账操作完成后，使用ROLLBACK语句回滚转账操作，SQL语句如下所示。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)


mysql> UPDATE account SET money=money-100
-> WHERE name='B';
Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE account SET money=money+100

-> WHERE name='C';
Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

从以上执行结果可看到，账户B转账给账户C一百元钱，可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+
| id | name | money |
+----+------+-------+

| 1 | A | 900 |

| 2 | B | 1000 |

| 3 | C | 1100 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看出，账户B减少了100元，账户C增加了100元，转账操作完成，但此时没有进行事务提交，可以使用ROLLBACK语句回滚事务操作，SQL语句如下所示。

mysql> ROLLBACK;

Query OK, 0 rows affected (0.03 sec)

以上执行结果证明事务操作成功回滚，可以查看表中数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 900 |

| 2 | B | 1100 |

| 3 | C | 1000 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看出，账户B和账户C的金额又回到了转账操作之前，这是因为ROLLBACK语句回滚了事务操作。

11.1.3​ 事务的属性

事务有很严格的定义，必须同时满足四个属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这四个属性通常称为ACID特性，四个属性的含义如下所示。

​ 原子性（Atomicity）：事务作为一个整体被执行，包含在其中对数据库的操作，要么全部执行，要么都不执行。

​ 一致性（Consistency）：事务应确保数据库的状态，从一个一致状态转变为另一个一致状态，一致状态的含义是数据库中的数据应满足完整性约束。

​ 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

​ 持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

以上是事务四个属性的概念，为了便于大家理解，接下来以转账的例子来说明如何通过数据库事务保证数据的准确性和完整性，例如账户A和账户B的余额都是1000，账户A转账给账户B一百元，则需要6个步骤，具体如下所示。

​ 从账户A中读取余额为1000。

​ 账户A的余额减去100。

​ 账户A的余额写入为900。

​ 从账户B中读取余额为1000。

​ 账户B的余额加上100。

​ 账户B的余额写入为1100。

对应如上6个步骤理解事务的四个属性，具体如下所示。

​ 原子性（Atomicity）：保证1~6所有过程要么都执行，要么都不执行。一旦在执行某一步骤的过程中出现问题，就需要执行回滚操作。例如执行到第五步时，账户B突然不可用（比如被注销），那么之前的所有操作都应该回滚到执行事务之前的状态。

​ 一致性（Consistency）：在转账之前，账户A和B中共有1000+1000=2000元钱。在转账之后，账户A和B中共有900+1100=2000元。也就是说，在执行该事务操作之后，数据从一个状态改变为另外一个状态，同时一致性还能保证账户余额不会变成负数等。

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

​ 持久性（Durability）：一旦转账成功（事务提交），两个账户中的钱就会真正发生变化（会将数据写入数据库做持久化保存）。

另外需要注意的是，事务的原子性与一致性是密切相关的，原子性的破坏可能导致数据库的不一致，但数据的一致性问题并不都和原子性有关。例如转账的例子中，在第5步时，为账户B只加了50元，该过程是符合原子性的，但数据的一致性就出现了问题。因此，事务的原子性与一致性缺一不可。

11.1.4​ 事务的隔离级别

数据库是多线程并发访问的，其明显的特征是资源可以被多个用户共享，当相同的数据库资源被多个用户（多个事务）同时访问时，如果没有采取必要的隔离措施，就会导致各种并发问题，破坏数据的完整性，这就需要为事务设置隔离级别。在MySQL中，事务有四种隔离级别，具体如下所示。

​ Read Uncommitted（读未提交）：是事务中最低的隔离级别，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果，也被称为脏读，这是非常危险的，所以很少用于实际应用。

​ Read Committed（读已提交）：这是大多数数据库管理系统的默认隔离级别，它满足了隔离的简单定义，即一个事务只能看见已经提交事务所做的改变，该隔离级别可以避免脏读，但不能避免重复读和幻读的情况。重复读就是在事务内重复读取了别的线程已经提交的数据，但两次读取的结果不一致，原因是查询的过程中，其他事务做了更新的操作，幻读指在一个事务内两次查询中数据条数不一致，这是因为查询过程中，其他事务做了添加或删除操作。

​ Repeatable Read（可重复读）：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，可以避免脏读和重复读的问题，但不能避免幻读的问题。

​ Serializable（可串行化）：是事务中最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题，实际上，它是在每个读的数据行上加了共享锁。在这个隔离级别，可能导致大量的超时现象和锁竞争，所以很少用于实际应用。

以上列出了数据库事务的四个隔离级别，它们会产生不同的问题，如脏读、不可重复读、幻读和超时等，在MySQL中，实现这四个隔离级别可能产生的问题如表11.2所示。

表11.2​ 隔离级别及问题

隔离级别

脏读

不可重复读

幻读

读未提交

√

√

√

读已提交

×

√

√

可重复读

×

×

√

可串行化

×

×

×

表11.2中列出了每个隔离级别可能出现的问题，接下来将分别演示这些问题，在演示前，首先了解一下隔离级别的相关操作，查看当前会话隔离级别的SQL语句如下所示。

SELECT @@tx_isolation;

设置当前会话隔离级别的SQL语句如下所示。

SET SESSION TRANSACTION ISOLATION LEVEL

{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

以上语法格式中，SESSION代表设置的是当前会话的隔离级别，LEVEL后有四个可选参数，分别对应四个隔离级别。接下来将通过具体案例分别演示四个隔离级别可能出现的问题。

1.脏读

当事务的隔离级别为Read Uncommitted（读未提交）时，可能出现脏读的问题，即一个事务读取了另一个事务未提交的数据，接下来演示脏读的问题，需要打开两个MySQL客户端模拟两个线程操作数据，下面都简称为客户端A和客户端B，首先查看客户端A和客户端B的隔离级别，查询客户端A的隔离级别，SQL语句如下所示。

mysql> SELECT @@tx_isolation;

+-----------------+
| @@tx_isolation |
+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.01 sec)

从以上执行结果可看出，客户端A的隔离级别为REPEATABLE-READ（可重复读），查询客户端B的隔离级别，SQL语句如下所示。

mysql> SELECT @@tx_isolation;

+-----------------+
| @@tx_isolation |
+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.00 sec)

从以上执行结果可看出，客户端B的隔离级别同样为REPEATABLE-READ（可重复读），这是因为MySQL默认隔离级别为REPEATABLE-READ（可重复读），接下来通过不断改变客户端A的隔离级别，在客户端B修改数据，演示各个隔离级别出现的问题。

将客户端A的隔离级别设置为Read Uncommitted（读未提交），SQL语句如下所示。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Query OK, 0 rows affected (0.02 sec)

以上执行结果证明客户端A的隔离级别设置为了Read Uncommitted（读未提交），在客户端A中查询表account的数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 900 |

| 2 | B | 1100 |

| 3 | C | 1000 |

+----+------+-------+

3 rows in set (0.13 sec)

接着在客户端B中进行事务操作，开启事务后，账户A转账给账户C一百元，但不提交事务，SQL语句如下所示。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET money=money-100

-> WHERE name='A';

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE account SET money=money+100

-> WHERE name='C';

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

以上执行结果证明账户A成功转账给账户C一百元，此时通过客户端A查看表account中的数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1100 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看出，在客户端A中查询表account中数据，账户A已经给账户C转账了一百元，但此时客户端B中的事务还没有提交，客户端A读取到了客户端B还未提交事务修改的数据，这就是脏读的问题，这是非常危险的，因为此时客户端B是可以回滚事务的，将客户端B的事务回滚，SQL语句如下所示。

mysql> ROLLBACK;

Query OK, 0 rows affected (0.03 sec)

以上执行结果证明客户端B成功回滚了事务，此时通过客户端A再次查询表account中的数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 900 |

| 2 | B | 1100 |

| 3 | C | 1000 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看到，客户端A又查询到了客户端B事务回滚后的数据，在实际应用中要尽量避免脏读的问题，所以尽量不要将数据库的隔离级别设置为Read Uncommitted（读未提交）。

2.不可重复读

当事务的隔离级别为Read Committed（读已提交）时，可能出现不可重复读的问题，即事务中两次查询的结果不一致，这是因为在查询过程中，其他事务做了更新操作。

将客户端A的隔离级别设置为Read Committed（读已提交），SQL语句如下所示。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Query OK, 0 rows affected (0.00 sec)

以上执行结果证明客户端A的隔离级别设置为了Read Committed（读已提交），在客户端A中开启一个事务，查询表account的数据，SQL语句如下所示。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 900 |

| 2 | B | 1100 |

| 3 | C | 1000 |

+----+------+-------+

3 rows in set (0.00 sec)

接着在客户端B中进行事务操作，开启事务后，账户A转账给账户C一百元，提交事务，SQL语句如下所示。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET money=money-100

-> WHERE name='A';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE account SET money=money+100

-> WHERE name='C';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> COMMIT;

Query OK, 0 rows affected (0.03 sec)

从以上执行结果可看出，客户端B中的事务操作完成，账户A给账户C转账了一百元，此时在客户端A未完成的事务中查询表account中的数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1100 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看出，客户端A查询出了客户端B修改后的数据，这就是说，客户端A在同一个事务中，查询同一个表，两次的查询结果不一致，这就是不可重复读的问题，不过在大多数场景中，这种问题是可以接受的，所以大部分数据库管理系统使用Read Committed（读已提交）隔离级别，例如ORACLE数据库管理系统。

3.幻读

当事务的隔离级别为Repeatable Read（可重复读）时，可能出现幻读的问题，即在一个事务内，两次查询的数据条数不一致，与不可重复读的问题类似，都是因为在查询过程中，其他事务做了更新操作。

将客户端A的隔离级别设置为Repeatable Read（可重复读），SQL语句如下所示。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Query OK, 0 rows affected (0.00 sec)

以上执行结果证明客户端A的隔离级别设置为了Repeatable Read（可重复读），在客户端A中开启一个事务，查询表account的数据，SQL语句如下所示。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1100 |

+----+------+-------+

3 rows in set (0.00 sec)

接着在客户端B中进行更新操作，添加一个账户D，余额为500元，SQL语句如下所示。

mysql> INSERT INTO account

-> VALUES(4,'D',500);

Query OK, 1 row affected (0.05 sec)

从以上执行结果可看出，客户端B中的添加操作完成，此时在客户端A未完成的事务中查询表account中的数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1100 |

+----+------+-------+

3 rows in set (0.00 sec)

从以上执行结果可看出，客户端A中查询account表中数据，并没有出现幻读的问题，这是因为MySQL的存储引擎通过多版本并发控制机制，解决了幻读的问题，因此MySQL的隔离级别为Repeatable Read（可重复读）时，是可以避免幻读的问题。

4.可串行化

当事务的隔离级别为Serializable（可串行化）时，事务的隔离级别最高，在每一行读取的数据上都会加锁，不会出现相互冲突，但这样会导致资源占用过多，出现大量的超时现象。

将客户端A的隔离级别设置为Serializable（可串行化），SQL语句如下所示。

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Query OK, 0 rows affected (0.00 sec)

以上执行结果证明客户端A的隔离级别设置为了Serializable（可串行化），在客户端A中开启一个事务，查询表account的数据，SQL语句如下所示。

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1100 |

| 4 | D | 500 |

+----+------+-------+

4 rows in set (0.00 sec)

接着在客户端B中进行更新操作，添加一个账户E，余额为800元，SQL语句如下所示。

mysql> INSERT INTO account  VALUES(5,'E',800);

客户端B中的添加操作卡住不动，因为此时客户端A的事务隔离级别为Serializable（可串行化），客户端A中的事务还没有提交，所以客户端B必须等待客户端A中的事务提交后，才可以进行添加数据的操作，当客户端A长时间没有提交事务时，客户端B会报错，SQL语句如下所示。

mysql> INSERT INTO account VALUES(5,'E',800);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

从以上报错信息可看出，因为操作超时，导致数据添加失败，这就是隔离级别Serializable（可串行化）可能出现的超时问题，这是比较严重的性能问题，所以实际应用中，事务的隔离级别一般不会设置为Serializable（可串行化）。

11.2​ 分布式事务的使用

MySQL从5.0.3版本开始支持分布式事务（XA事务），目前分布式事务只支持InnoDB存储引擎，一个分布式事务会涉及多个行动，这些行动本身是事务性的，所有行动都必须一起成功完成，或者一起被回滚，接下来将详细讲解MySQL的分布式事务。

11.2.1​ 分布式事务的原理

在MySQL中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。

资源管理器（Resource Manager）：用于提供通向事务资源的途径，数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由RM管理的事务。例如，多台MySQL数据库作为多台资源管理器，或者几台MySQL和几台Oracle服务器作为资源管理器。

事务管理器（Transaction Manager）：用于协调作为一个分布式事务的一部分的事务。TM与管理每个事务的RMs进行通信。在一个分布式事务中，各个单个事务均是分布式事务的“分支事务”。分布式事务和各分支通过一种命名方法进行标识。

MySQL执行分布式事务时，MySQL服务器相当于一个用于管理分布式事务中的资源管理器，与MySQL服务器连接的客户端相当于事务管理器。

要执行一个分布式事务，必须知道分布式事务涉及的资源管理器，并把每个资源管理器的事务执行到事务可以被提交或回滚时，根据每个资源管理器报告的执行情况，所有分支事务必须作为一个原子性操作全部提交或回滚。

用于执行分布式事务的过程使用两阶段提交，发生时间是分布式事务的各个分支需要进行的行动已经执行之后，两个阶段分别如下所示。

​ 在第一阶段，TM告知所有RM进行PREPARE操作，即所有RM被告知即将要执行COMMIT操作，通常，这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行动，然后分支回答自己是否准备好进行COMMIT操作了。

​ 在第二阶段，TM告知所有RM进行COMMIT或者回滚。如果在PREPARE时，有任意一个RM回复无法进行COMMIT操作，那么所有的RM将被告知进行回滚操作，否则所有的RM将被告知进行COMMIT操作。

有些情况下，如果一个分布式事务只有一个RM，那么使用一阶段提交也可以，即该RM会被告知同时进行PREPARE和COMMIT操作。

11.2.2​ 分布式事务的语法和使用

MySQL中与分布式事务相关的SQL语句如下所示。

XA {START|BEGIN} xid [JOIN|RESUME] # 开始一个分布式事务

XA END xid [SUSPEND [FOR MIGRATE]] # 操作分布式事务

XA PREPARE xid # 准备提交事务

XA COMMIT xid [ONE PHASE] # 提交事务

XA ROLLBACK xid # 回滚事务

XA RECOVER [CONVERT XID] # 查看处于PREPARE状态的事务

以上SQL语句中，xid用于标识一个分布式事务，其组成如下所示。

xid: gtrid [, bqual [, formatID ]]

以上语法格式中个，gtrid是必须的，为字符串，表示全局事务标识符；bqual是可选的，为字符串，默认是空串，表示分支限定符；formatID是可选的，默认值为1，用于标识由gtrid和bqual值使用的格式，接下来通过具体案例演示分布式事务的实现。

演示分布式事务将使用两台MySQL，分别为DB1和DB2，首先查看DB1是否支持分布式事务，SQL语句如下所示。

mysql> SHOW VARIABLES LIKE 'innodb_support%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| innodb_support_xa | ON |

+-------------------+-------+

1 row in set (0.00 sec)

从以上执行结果可看出，DB1支持分布式事务。接着查看DB2是否支持分布式事务，SQL语句如下所示。

mysql> SHOW VARIABLES LIKE 'innodb_support%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| innodb_support_xa | ON |

+-------------------+-------+

1 row in set (0.00 sec)

从以上执行结果可看出，DB2同样支持分布式事务。在数据库DB1中启动一个分布式事务的一个分支事务，xid的gtrid为“test”，bqual为“db1”，SQL语句如下所示。

mysql> XA START 'test','db1';

Query OK, 0 rows affected (0.02 sec)

在数据库DB2中启动一个分布式事务的一个分支事务，xid的gtrid为“test”，bqual为“db2”，SQL语句如下所示。

mysql> XA START 'test','db2';

Query OK, 0 rows affected (0.00 sec)+

在数据库DB1中向account表插入账户E，余额为100元，SQL语句如下所示。

mysql> INSERT INTO account

-> VALUES(5,'E',100);

Query OK, 1 row affected (0.02 sec)

以上执行结果证明数据插入完成，接着在数据库DB2中向account表插入账户F，余额为200元，SQL语句如下所示。

mysql> INSERT INTO account

-> VALUES(6,'F',200);

Query OK, 1 row affected (0.00 sec)

以上执行结果证明数据插入完成，此时可以查看表中数据，在DB1中查看表account，SQL语句如下所示。

mysql> SELECT * FROM account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 800 |
| 2 | B | 1100 |
| 3 | C | 1100 |
| 4 | D | 500 |
| 5 | E | 100 |
+----+------+-------+
5 rows in set (0.00 sec)
在DB2中查看表account，SQL语句如下所示。
mysql> SELECT * FROM account;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1100 |

| 4 | D | 500 |

| 6 | F | 200 |

+----+------+-------+

5 rows in set (0.00 sec)

从以上执行结果可看出，因为分布式事务还没有提交，所以分支事务暂时无法查看到其他分支事务添加的数据，接着对数据库DB1进行第一阶段提交，且进入prepare状态，SQL语句如下所示。

mysql> XA END 'test','db1';

Query OK, 0 rows affected (0.00 sec)

mysql> XA PREPARE 'test','db1';

Query OK, 0 rows affected (0.03 sec)

对数据库DB2进行第一阶段提交，且进入prepare状态，SQL语句如下所示。

mysql> XA END 'test','db2';

Query OK, 0 rows affected (0.00 sec)

mysql> XA PREPARE 'test','db2';

Query OK, 0 rows affected (0.03 sec)

此时两个事务的分支都进入准备提交阶段，如果这之前的操作遇到任何错误，都应回滚所有分支的操作，以确保分布式事务的正确性，这里直接提交事务，数据库DB1中提交事务，SQL语句如下所示。

mysql> XA COMMIT 'test','db1';

Query OK, 0 rows affected (0.04 sec)

数据库DB2中提交事务，SQL语句如下所示。

mysql> XA COMMIT 'test','db2';

Query OK, 0 rows affected (0.03 sec)

以上执行结果证明两个事务分支都成功提交，此时可以在两个数据库查询表中数据，数据库DB1中查询表account的数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1100 |

| 4 | D | 500 |

| 5 | E | 100 |

| 6 | F | 200 |

+----+------+-------+

6 rows in set (0.00 sec)

数据库DB2中查询表account的数据，SQL语句如下所示。

mysql> SELECT * FROM account;

+----+------+-------+

| id | name | money |

+----+------+-------+

| 1 | A | 800 |

| 2 | B | 1100 |

| 3 | C | 1100 |

| 4 | D | 500 |

| 5 | E | 100 |

| 6 | F | 200 |

+----+------+-------+

6 rows in set (0.00 sec)

从以上执行结果可看出，分布式事务成功提交，分支事务的添加数据操作成功。

11.3​ 本章小结

本章首先介绍了事务的概念，然后详细介绍了事务管理的相关操作，包括事务的使用、事务的回滚等，接着介绍了事务的属性和事务的隔离级别，大家需要去理解并掌握，最后介绍了分布式事务的原理、语法和存在问题，大家初步了解即可，如果想深入学习，可以查阅，分布式的相关资料。

11.4​ 习题

1.思考题

(1) 请简述事务的概念。

(2) 请简述事务的属性有哪些。

(3) 请简述事务的隔离级别有哪些。

(4) 请简述事务的隔离级别可能会产生什么问题。

(5) 请简述分布式事务的原理。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
SQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入