风雪围城

MySQL 事务处理

Transactions are not a law of nature; they were created with a purpose, namely to simplify the programming model for applications accessing a database. By using transactions, the application is free to ignore certain potential error scenarios and concurrency issues, because the database takes care of them instead (we call these safety guarantees).

事务是什么

事务，是一组 SQL 语句，是一个满足 ACID （原子性、一致性、隔离性、持久性）特性的独立的执行单位。

  • 原子性（atomicity）：事务内的 SQL 语句“共进退”，构成一个不可分割的可执行单位。事务提交(commit)后，只有事务中所有操作都执行成功，整个事务才算成功。如果事务中任一 SQL 语句执行失败，都需要通过回滚（rollback）讲数据库恢复到原状态。
  • 一致性（consistency）（或者说正确性）:数据库能够从一个正确的状态，迁移到另一个正确的状态（当前的状态满足预定的约束），事务执行前后，数据库的完整性约束没有被破坏。
  • 隔离性（Isolation）:事务之间，不应相互影响。即，一个事务的影响在该事务提交前对其它事务不可见。
  • 持久性（Durability）:事务一旦提交，其结果是永久性的。

在 MySQL 中，不是所有的存储引擎，都只是事务使用。事务的执行，对于 CPU/内存/磁盘都有一定的消耗，因此，是否选用具有事务功能的存储引擎，是根据具体使用场景决定的。
如 InnoDB，是支持事务的，MyISAM 和 MEMEROY 这列存储引擎就不行。

事务中的控制语句

默认情况下，MySQL 的运行模式是自动提交的。即，每条语句所做的更改会自动提交到数据库据，并永久保存下来。事实上，这相当于每条语句都是被隐性地当作一个事务来执行。
如果想要显示地执行事务，那么，需要禁用自动提交模式，并主动告知 MySQL 何时提交更改或何时回滚更改。
更具体地，其控制语句包括：

  • SET autocommit = 0/1:将 autocommit 设置为 1，将开启自动提交模式；将 autocommit 设置为 0 ，将关闭自动提交模式，后面的所有语句，都将是事务的一部分，语句范围，一直到 COMMIT 或者 ROLLBACK. COMMIT/ROLLBACK 之后，又开启一个新的事务。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SET autocommit = 0 ;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO users SET userName = 'Zhangsan';
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT * FROM users;
+----+----------+----------+----------+-----------+
| id | userName | passWord | user_sex | nick_name |
+----+----------+----------+----------+-----------+
| 28 | aa1      | a123456  | MAN      | NULL      |
| 29 | bb1      | b123456  | WOMAN    | NULL      |
| 30 | cc1      | b123456  | WOMAN    | NULL      |
+----+----------+----------+----------+-----------+
3 rows in set (0.00 sec)
  • START TRANSACTION | BEGIN: 这两个语句具有同等效力，旨在挂起自动提交模式，再执行事务中的 SQL 语句。直到 COMMIT 或者 ROLLBACK 之后，该模式又恢复到执行 COMMIT/ROLLBACK 之前的状态。
  • COMMIT: 提交事务，并将对数据库造成永久性修改。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SET autocommit = 0 ;
Query OK, 0 rows affected (0.00 sec)

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

mysql> INSERT users SET userName = 'Zhangsan';
Query OK, 1 row affected (0.01 sec)

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

mysql> SELECT * FROM users;
+----+----------+----------+----------+-----------+
| id | userName | passWord | user_sex | nick_name |
+----+----------+----------+----------+-----------+
| 28 | aa1      | a123456  | MAN      | NULL      |
| 29 | bb1      | b123456  | WOMAN    | NULL      |
| 30 | cc1      | b123456  | WOMAN    | NULL      |
| 34 | Zhangsan | NULL     | NULL     | NULL      |
+----+----------+----------+----------+-----------+
4 rows in set (0.00 sec)
  • ROLLBACK: 回滚事务，并撤销正在进行的未提交的修改。
  • SAVEPOINT identifier: MySQL 可以让你对事务进行部分回滚，SAVEPOINT 即是在标记这个起始位置。
  • RELEASE SAVEPOINT identifier: 删除一个事务的保存点。
  • ROLLBACK TO SAVEPOINT identifier: 回滚到指定的标记点（由 SAVEPOINT 指定的）。
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
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO users SET userName = 'Wanger';
Query OK, 1 row affected (0.00 sec)

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

mysql> INSERT INTO users SET userName = 'Lisi';
Query OK, 1 row affected (0.00 sec)

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

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

mysql> SELECT * FROM users;
+----+----------+----------+----------+-----------+
| id | userName | passWord | user_sex | nick_name |
+----+----------+----------+----------+-----------+
| 28 | aa1      | a123456  | MAN      | NULL      |
| 29 | bb1      | b123456  | WOMAN    | NULL      |
| 30 | cc1      | b123456  | WOMAN    | NULL      |
| 34 | Zhangsan | NULL     | NULL     | NULL      |
| 35 | Wanger   | NULL     | NULL     | NULL      |
+----+----------+----------+----------+-----------+
5 rows in set (0.00 sec)
  • SET TRANSACTION: 设置事务的隔离级别。
隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED 是 是 是
READ COMMITTED 否 是 是
REPEATABLE READ 否 否 否
SERIALIZABLE 否 否 否

事务隔离

InnoDB 存储引擎提供了四种事务隔离级别。

  • READ UNCOMMITTED: 它允许某个事务看到其它事务尚未提交的修改。
  • READ COMMITTED: 它只允许某个事务看到其它事务已经提交的修改。
  • REPEATABLE READ: 如果某个事务两次执行同一条 SELECT 语句，其结果是可重复的。即，即使有其它事务同时插入或修改行，这个事务所看到的结果也是一样的。该隔离级别也是 InnoDB 默认的隔离级别。
  • SERIALIZABLE: 与 REPEATABLE READ 类似，但是其隔离更加彻底。对于某个事务正在查看的行，只有等该事务完成才能被其它事务所修改。

修改默认隔离级别，有两种方式，在服务启动时，使用 –transaction-isolation 选项，或者在服务运行中，使用 SET TRANSACTION 语句。

1
2
3
4
5
6
// 拥有超级管理权力权限的客户端，可以使用它来改变全局隔离级别。之后它会作用域后续的所有客户端连接。
SET GLOBAL TRANSACTION ISOLATION LEVEL level;
// 对当前会话的所有后续事务起作用。
SET SESSION TRANSACTION ISOLATION LEVEL level;
// 对当前事务起作用。
SET TRANSACTION ISOLATION LEVEL level;
1
2
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

隐式提交的 SQL 语句

有些 SQL 语句，会产生一个隐式的提交操作。即，执行完该语句后，会有一个隐式的 COMMIT 操作。
这些语句包括：

  • 数据定义语言（Data Definition Language,DDL）:通常，那些用来创建、更改或删除数据库或其中对象的数据定义语言，由于不能成为事务的一部分，而只会对事务产生隐式影响。具体包括：ALERT TABLE/ DRAP DATABASE/ RENAME TABLE/ SET autocommit/ LOCK TABLES 等。
  • 用来隐式地修改 MySQL 架构的操作：CREATE USER/ DROP USER/ RENAME USER/ SET PASSWORD 等。
  • 管理语句：CHECK TABLE/ OPTIMIZE TABLE/ REPAIR TABLE 等。

参考链接
What are ACID properties in a database?
深入理解数据库事务
Revisit Database Isolation
如何理解数据库事务中的一致性的概念？
《MySQL 技术内幕（第五版）》
《MySQL 技术内幕 InnoDB 存储引擎》

数据库 数据库

© 2024 YueGS