MySQL8 事务详解

在本章中，我们将介绍以下配方：

在下面的食谱中，我们将讨论 MySQL 中的事务和各种隔离级别。事务是指一组 SQL 语句，它们应该同时成功或失败。事务还应满足原子性、一致性、隔离性和**耐久性（酸性**特性。举一个非常基本的例子，从账户A向账户B转账。假设A有 600 美元，B有 400 美元，B希望从A向自身转账 100 美元。

银行将从A中扣除 100 美元，并使用以下 SQL 代码添加到B（用于说明）：

mysql> SELECT balance INTO @a.bal FROM account WHERE account_number='A';

通过编程检查@a.bal是否大于或等于 100：

mysql> UPDATE account SET balance=@a.bal-100 WHERE account_number='A';
mysql> SELECT balance INTO @b.bal FROM account WHERE account_number='B';

以编程方式检查@b.bal是否为NOT NULL：

mysql> UPDATE account SET balance=@b.bal+100 WHERE account_number='B';

这四个 SQL 行应该是单个事务的一部分，并满足以下 ACID 属性：

  • 原子性：要么所有 SQL 都成功，要么所有 SQL 都失败。不应该有任何部分更新。如果不遵守此属性，并且数据库在运行两个 SQL 后崩溃，A将丢失 100。
  • 一致性：事务必须以允许的方式更改受影响的数据。在本例中，如果带B的account_number不存在，则应回滚整个事务。
  • 隔离：同时发生的事务（并发事务）不应导致数据库处于不一致的状态。每个事务都应该像系统中唯一的事务一样执行。任何事务都不应影响任何其他事务的存在。假设A在传输到B的同时，正好同时传输了这 600 个数据；两项事务均应独立进行，确保在转移金额前保持平衡。
  • 耐久性：数据应持久保存在磁盘上，即使数据库或系统出现故障，也不应丢失。

MySQL 中默认的存储引擎InnoDB支持事务，而 MyISAM 不支持事务。

创建虚拟表和示例数据以了解此配方：

mysql> CREATE DATABASE bank;
mysql> USE bank;
mysql> CREATE TABLE account(account_number varchar(10) PRIMARY KEY, balance int);
mysql> INSERT INTO account VALUES('A',600),('B',400);

要启动事务（一组 SQL），请执行START TRANSACTION或BEGIN语句：

mysql> START TRANSACTION;
or 
mysql> BEGIN;

然后执行您希望在事务中的所有语句，例如将 100 从A转移到B：

mysql> SELECT balance INTO @a.bal FROM account WHERE account_number='A';

Programmatically check if @a.bal is greater than or equal to 100 
mysql> UPDATE account SET balance=@a.bal-100 WHERE account_number='A';
mysql> SELECT balance INTO @b.bal FROM account WHERE account_number='B';

Programmatically check if @b.bal IS NOT NULL 
mysql> UPDATE account SET balance=@b.bal+100 WHERE account_number='B';

确认所有 SQL 都执行成功后，执行COMMIT语句，完成事务并提交数据：

mysql> COMMIT;

如果您在这段时间内遇到任何错误并希望中止事务，您可以发出一条ROLLBACK语句而不是COMMIT。

例如，如果A想要转账到一个不存在的账户，而不是发送到B，您应该中止事务并将金额退还给A：

mysql> BEGIN;

mysql> SELECT balance INTO @a.bal FROM account WHERE account_number='A';

mysql> UPDATE account SET balance=@a.bal-100 WHERE account_number='A';

mysql> SELECT balance INTO @b.bal FROM account WHERE account_number='C';
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT @b.bal;
+--------+
| @b.bal |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

默认情况下，autocommit 为ON，这意味着所有单个语句一经执行就会提交，除非它们位于BEGIN...COMMIT块中。如果 autocommit 为OFF，则需要显式发出COMMIT语句来提交事务。要禁用它，请执行：

mysql> SET autocommit=0;

无法回滚 DDL 语句，例如数据库的CREATE或DROP以及表或存储例程的CREATE、DROP或ALTER。

There are certain statements such as DDLs, LOAD DATA INFILE, ANALYZE TABLE, replication-related statements and so on that cause implicit COMMIT. For more details on these statements, refer https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html.

使用保存点，您可以回滚到事务中的某些点，而无需终止事务。您可以使用SAVEPOINT identifier为事务设置名称，并使用ROLLBACK TO identifier语句将事务回滚到指定的保存点，而无需终止事务。

假设A想转入多个账户；即使向一个账户转账失败，也不应回滚其他账户：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance INTO @a.bal FROM account WHERE account_number='A';
Query OK, 1 row affected (0.01 sec)

mysql> UPDATE account SET balance=@a.bal-100 WHERE account_number='A';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE account SET balance=balance+100 WHERE account_number='B';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SAVEPOINT transfer_to_b;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance INTO @a.bal FROM account WHERE account_number='A';
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE account SET balance=balance+100 WHERE account_number='C';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

### Since there are no rows updated, meaning there is no account with 'C', you can rollback the transaction to SAVEPOINT where transfer to B is successful. Then 'A' will get back 100 which was deducted to transfer to C. If you wish not to use the save point, you should do these in two transactions.

mysql> ROLLBACK TO transfer_to_b;
Query OK, 0 rows affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT balance FROM account WHERE account_number='A';
+---------+
| balance |
+---------+
| 400     |
+---------+
1 row in set (0.00 sec)

mysql> SELECT balance FROM account WHERE account_number='B';
+---------+
| balance |
+---------+
| 600     |
+---------+
1 row in set (0.00 sec)

当两个或多个事务同时发生时，隔离级别定义事务与其他事务所做的资源或数据修改隔离的程度。有四种类型的隔离级别；要更改隔离级别，需要设置具有会话级别作用域的动态tx_isolation变量。

要更改此级别，请执行SET @@transaction_isolation = 'READ-COMMITTED';。

当前事务可以读取另一个未提交事务写入的数据，也称为脏读。

例如，A想在他的账户上增加一些金额，并将其转账到B。假设两个事务同时发生；流程将是这样的。

A最初有 400 美元，想在账户上增加 500 美元后将 500 美元转账到B。

| #事务 1（增加金额） | #事务 2（转账金额） | | 

BEGIN;

| 

BEGIN;

| | 

UPDATE account
 SET balance=balance+500
 WHERE account_number='A';
-- 
SELECT balance INTO @a.bal
 FROM account
 WHERE account_number='A';
 # A sees 900 here

| | 

ROLLBACK;
 # Assume due to some reason the
 transaction got rolled back
-- 
# A transfers 900 to B since
 A has 900 in previous SELECT
 UPDATE account
 SET balance=balance-900
 WHERE account_number='A';
 
# B receives the amount UPDATE account
 SET balance=balance+900
 WHERE account_number='B';
 
# Transaction 2 completes successfully
COMMIT;

|

您可以注意到事务 2已经从事务 1读取了未提交或回滚的数据，导致账户A在本次事务后进入负余额，这显然是不需要的。

当前事务只能读取另一事务提交的数据，也称为不可重复读取。

再举一个同样的例子，A有 400 美元，B有 600 美元。

| #事务 1（增加金额） | #事务 2（转账金额） | | 

BEGIN;

| 

BEGIN;

| | 

UPDATE account SET balance=balance+500
WHERE account_number='A';
-- 
SELECT balance INTO @a.bal
FROM account
WHERE account_number='A';
# A sees 400 here because transaction 1 has not committed the data yet

| | 

COMMIT;
-- 
SELECT balance INTO @a.bal
FROM account
WHERE account_number='A';
# A sees 900 here because transaction 1 has committed the data.

|

您可以注意到，在同一事务中，同一SELECT语句会获取不同的结果。

即使另一个事务提交了数据，事务也会看到第一条语句读取的相同数据。同一事务中的所有一致读取都会读取第一次读取所建立的快照。异常是可以读取同一事务中更改的数据的事务。

当事务启动并执行其第一次读取时，将创建一个读取视图并保持打开状态，直到事务结束。为了在事务结束前提供相同的结果集，InnoDB使用行版本控制和UNDO信息。假设事务 1选择了几行，另一个事务删除了这些行并提交了数据。如果事务 1打开，它应该能够看到它在开始时选择的行。删除的行保存在UNDO日志空间中，以完成事务 1。一旦事务 1完成，这些行将被标记为从UNDO日志中删除。这称为多版本并发控制（MVCC。

再举一个相同的例子，A有 400，B有 600。

| #事务 1（增加金额） | #事务 2（转账） | | 

BEGIN;

| 

BEGIN;
 
SELECT balance INTO @a.bal
FROM account
WHERE account_number='A';
# A sees 400 here

| | 

UPDATE account
SET balance=balance+500
WHERE account_number='A';
-- 
SELECT balance INTO @a.bal
FROM account
WHERE account_number='A';
# A sees still 400 even though transaction 1 is committed

| | 

COMMIT;
-- 
COMMIT;
 
SELECT balance INTO @a.bal
FROM account
WHERE account_number='A';
# A sees 900 here because this is a fresh transaction

|

This applies only to SELECT statements and not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session cannot query them. If a transaction does update or delete rows committed by a different transaction, those changes become visible to the current transaction.

例如：

| #事务 1 | #事务 2 | | 

BEGIN;

| 

BEGIN;

| | 

SELECT * FROM account;
# 2 rows are returned
-- 
INSERT INTO account VALUES('C',1000);
# New account is created
 
COMMIT;

| | 

SELECT * FROM account WHERE account_number='C';
# no rows are returned because of MVCC

| -- | | 

DELETE FROM account WHERE account_number='C';
# Surprisingly account C gets deleted
-- 
SELECT * FROM account;
# 3 rows are returned because transaction 1 is not yet committed

| | 

COMMIT;
-- 
SELECT * FROM account;
# 2 rows are returned because transaction 1 is committed

|

下面是另一个例子：

| #事务 1 | #事务 2 | | 

BEGIN;

| 

BEGIN;

| | 

SELECT * FROM account;
# 2 rows are returned
-- 
INSERT INTO account VALUES('D',1000);
 
COMMIT;

| | 

SELECT * FROM account;
# 3 rows are returned because of MVCC

| -- | | 

UPDATE account SET balance=1000 WHERE account_number='D';
# Surprisingly account D gets updated

| -- | | 

SELECT * FROM account;
# Surprisingly 4 rows are returned

| -- |

这通过锁定所选的所有行来提供最高级别的隔离。此级别类似于REPEATABLE READ，但如果禁用自动提交，则InnoDB会将所有普通SELECT语句隐式转换为SELECT...LOCK IN SHARE MODE。如果启用了自动提交，SELECT是它自己的事务。

例如：

| #事务 1 | #事务 2 | | 

BEGIN;

| 

BEGIN;

| | 

SELECT * FROM account WHERE account_number='A';
-- 
UPDATE account SET balance=1000 WHERE account_number='A';
 # This will wait until the lock held by transaction 1
 on row A is released

| | 

COMMIT;
-- 
# UPDATE will be successful now

|

另一个例子：

| #事务 1 | #事务 2 | | 

BEGIN;

| 

BEGIN;

| | 

SELECT * FROM account WHERE account_number='A';
# Selects values of A
-- 
INSERT INTO account VALUES('D',2000);
# Inserts D

| | 

SELECT * FROM account WHERE account_number='D';
 # This will wait until the transaction 2 completes
-- 
COMMIT;

| | 

# Now the preceding select statement returns values of D

| -- |

因此，serializable 会等待锁并始终读取最新提交的数据。

有两种类型的锁定：

  • 内部锁定：MySQL 在服务器内部进行内部锁定，管理多个会话对表内容的争用
  • 外部锁定：MySQL 为客户端会话提供了显式获取表锁的选项，以防止其他会话访问该表

内锁：主要有两种锁：

  • 行级锁：锁对行级是细粒度的。只有被访问的行被锁定。这允许多个会话同时进行写访问，使它们适合多用户、高并发和 OLTP 应用程序。只有InnoDB支持行级锁。
  • 表级锁：MySQL 对MyISAM、MEMORY和MERGE表使用表级锁，一次只允许一个会话更新这些表。此锁定级别使这些存储引擎更适合于只读、多读或单用户应用程序。

参见https://dev.mysql.com/doc/refman/8.0/en/internal-locking.html 和https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html 了解更多关于InnoDB锁的信息。

外部锁定：您可以使用LOCK TABLE和UNLOCK TABLES语句来控制锁定。

READ和WRITE的表锁定说明如下：

  • READ：当一个表被READ锁定时，多个会话可以从该表中读取数据，而无需获取锁。此外，多个会话可以获取同一个表上的锁，这就是为什么一个READ锁也称为共享锁。当持有READ锁时，任何会话都不能将数据写入表中（包括持有该锁的会话）。如果进行任何写入尝试，它将处于等待状态，直到释放READ锁。
  • WRITE：当一个表被WRITE锁定时，除持有该锁的会话外，其他会话不能从该表读写数据。在释放现有锁之前，任何其他会话都无法获取任何锁。这就是为什么称之为exclusive lock。如果进行任何读/写尝试，它将处于等待状态，直到释放WRITE锁。

所有的锁都被释放，当；UNLOCK TABLES语句被执行或会话终止时。

语法如下：

mysql> LOCK TABLES table_name [READ | WRITE]

要解锁表格，请使用：

mysql> UNLOCK TABLES;

要锁定所有数据库中的所有表，请执行以下语句。它用于对数据库进行一致快照。它冻结对数据库的所有写入：

mysql> FLUSH TABLES WITH READ LOCK;

除了共享锁（一个表可以有多个共享锁），表上不能同时持有两个锁。如果一个表已经有一个共享锁，并且出现了一个排他锁，那么它将被保留在队列中，直到共享锁被释放。当独占锁位于队列中时，所有后续共享锁也会被阻止并保留在队列中。

InnoDB从表读写时获取元数据锁。如果第二个事务请求WRITE LOCK，它将被保留在队列中，直到第一个事务完成。如果第三个事务想要读取数据，它必须等待第二个事务完成。

事务 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employees LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

请注意，COMMIT未被执行。事务保持开放状态。

事务 2:

mysql> LOCK TABLE employees WRITE;

此语句必须等待事务 1 完成。

事务 3:

mysql> SELECT * FROM employees LIMIT 10;

即使事务 3 也不会给出任何结果，因为队列中存在独占锁（它正在等待事务 2 完成）。此外，它正在阻止表上的所有操作。

您可以通过从另一个会话中检查SHOW PROCESSLIST来检查这一点：

mysql> SHOW PROCESSLIST;
+----+------+-----------+-----------+---------+------+---------------------------------+----------------------------------+
| Id | User | Host      | db        | Command | Time | State                           | Info                             |
+----+------+-----------+-----------+---------+------+---------------------------------+----------------------------------+
| 20 | root | localhost | employees | Sleep   |   48 |                                 | NULL                             |
| 21 | root | localhost | employees | Query   |   34 | Waiting for table metadata lock | LOCK TABLE employees WRITE       |
| 22 | root | localhost | employees | Query   |   14 | Waiting for table metadata lock | SELECT * FROM employees LIMIT 10 |
| 23 | root | localhost | employees | Query   |    0 | starting                        | SHOW PROCESSLIST                 |
+----+------+-----------+-----------+---------+------+---------------------------------+----------------------------------+
4 rows in set (0.00 sec)

您可以注意到事务 2 和事务 3 都在等待事务 1。

有关元数据锁的详细信息，请参阅https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html 。使用FLUSH TABLES WITH READ LOCK时也可以观察到同样的行为。

事务 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employees LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

请注意，COMMIT未被执行。事务保持开放状态。

事务 2:

mysql> FLUSH TABLES WITH READ LOCK;

事务 3:

mysql> SELECT * FROM employees LIMIT 10;

即使事务 3 也不会给出任何结果，因为FLUSH TABLES正在等待表上的所有操作完成，然后才能获得锁。此外，它正在阻塞表上的所有操作。

您可以通过从另一个会话中检查SHOW PROCESSLIST来检查这一点。

mysql> SHOW PROCESSLIST;
+----+------+-----------+-----------+---------+------+-------------------------+--------------------------------------------------+
| Id | User | Host      | db        | Command | Time | State                   | Info                                             |
+----+------+-----------+-----------+---------+------+-------------------------+--------------------------------------------------+
| 20 | root | localhost | employees | Query   |    7 | Creating sort index     | SELECT * FROM employees ORDER BY first_name DESC |
| 21 | root | localhost | employees | Query   |    5 | Waiting for table flush | FLUSH TABLES WITH READ LOCK                      |
| 22 | root | localhost | employees | Query   |    3 | Waiting for table flush | SELECT * FROM employees LIMIT 10                 |
| 23 | root | localhost | employees | Query   |    0 | starting                | SHOW PROCESSLIST                                 |
+----+------+-----------+-----------+---------+------+-------------------------+--------------------------------------------------+
4 rows in set (0.00 sec)

对于一致性备份，所有备份方法都使用FLUSH TABLES WITH READ LOCK，如果表上有长期运行的事务，这可能非常危险。

教程来源于Github，感谢apachecn大佬的无私奉献，致敬！

技术教程推荐

技术领导力实战笔记 -〔TGO鲲鹏会〕

消息队列高手课 -〔李玥〕

说透中台 -〔王健〕

现代C++编程实战 -〔吴咏炜〕

Python自动化办公实战课 -〔尹会生〕

技术面试官识人手册 -〔熊燚（四火）〕

计算机基础实战课 -〔彭东〕

Web 3.0入局攻略 -〔郭大治〕

徐昊 · AI 时代的软件工程 -〔徐昊〕

休闲君评测 |  TOOLFK工具网 |  chrome插件网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  豪评测 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630