首先来首歌曲来放松一下吧！

一、事务

把多条语句作为一个整体进行操作的功能，被称为数据库事务。

数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

与多条语句分别执行而不使用事务的区别：

  1. 不使用事务，若某一句执行出错，则执行成功的语句将会生效
  2. 使用事务，若某一句执行出错，当我们关掉窗口再次打开则会自动触发回滚操作ROLLBACK

事务(transaction)：一组SQL语句

回退(rollback)：撤销指定SQL语句

提交(commit)：指将未存储的SQL语句结果写入数据库表

保留点(savepoint)：指事务处理中设置的临时占位符，你可以对它发布回退(与回退整个事务处理不同)

1、数据库事务的ACID四个特性

  • A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
  • C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
  • I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
  • D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

2、隐式事务

对于单条SQL语句，数据库系统自动将其作为一个事务执行！这种事务被称为隐式事务。

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

3、显示事务

对于多条SQL语句，想要使用事务，必须手动启用，使用BEGIN或START TRANSACTION开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务。

有一句执行失败，系统就会调用ROLLBACK进行回滚。

1
2
3
4
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

做个试验：

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
-- 原来的样子
mysql> SELECT * FROM accounts;
+----+---------+
| id | balance |
+----+---------+
|  1 |     134 |
|  2 |     123 |
+----+---------+
2 rows in set (0.00 sec)

-- 开始测试
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 此时为错误语句
UPDATE accounts SET balance = balance + 100 WHERE d = 2;

-- 现在关闭窗口 重新打开
-- 会发现没有改变，系统自动进行了ROLLBACK操作
mysql> SELECT * FROM accounts;
+----+---------+
| id | balance |
+----+---------+
|  1 |     134 |
|  2 |     123 |
+----+---------+
2 rows in set (0.00 sec)

那些语句可以回退？

  • INSERT
  • UPDATE
  • DELETE
  • SELECT(无意义)
  • CREATE、DROP(事务处理块中可以使用这个语句，但是回退后，他们不会撤销操作)

4、主动失败

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务。

如下方语句，会发现并没有造成修改，其实是做了回滚。

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
mysql> SELECT * FROM accounts;
+----+---------+
| id | balance |
+----+---------+
|  1 |     134 |
|  2 |     123 |
+----+---------+
2 rows in set (0.00 sec)

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

mysql> UPDATE accounts SET balance = balance - 100 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE accounts SET balance = balance + 100 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> SELECT * FROM accounts;
+----+---------+
| id | balance |
+----+---------+
|  1 |     134 |
|  2 |     123 |
+----+---------+
2 rows in set (0.00 sec)

5、使用保留点

简单的事务可能只需要COMMIT或ROLLBACK，但是复杂的事务可能就需要进行部分提交或回退。

保留点可以使你回退到某个位置，而不是全部都进行回退！

  • 保留点越多越好
  • 释放保留点
    保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。

创建保留点（占位符）：

1
SAVEPOINT 保留点名字;

回退到保留点：

1
ROLLBACK TO 保留点名字;

6、更改默认提交行为

默认的MySQL行为是自动提交的，如果要更改，需要使用以下语句：

1
SET autocommit=0

autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。

设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。

不管是平时的默认语句，还是事务，都需要commit之后才会提交，可以用两个终端来测试。

标志为连接专用: autocommit标志是针对每个连接而不是服务器的。

二、隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别：

Isolation Level 脏读（Dirty Read） 不可重复读（Non Repeatable Read） 幻读（Phantom Read）
Read Uncommitted Yes Yes Yes
Read Committed - Yes Yes
Repeatable Read - - Yes
Serializable - - -

1、Read Uncommitted

Read Uncommitted是隔离级别最低的一种事务级别。

在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

先创建一个表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> CREATE TABLE student_of_dirty_read (
    -> id BIGINT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(100) NOT NULL,
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO student_of_dirty_read (name) VALUE ("Alice");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student_of_dirty_read;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

mysql>

然后分别开启两个MySql数据连接，按顺序执行下列命令：

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2 BEGIN; BEGIN;
3 UPDATE students SET name = ‘Bob’ WHERE id = 1;
4 SELECT * FROM students WHERE id = 1;
5 ROLLBACK;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;
  • SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED：意思是设置事务隔离级别为Read Uncommitted
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 第一次
mysql> SELECT * FROM student_of_dirty_read WHERE id = 1;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+
1 row in set (0.00 sec)

-- 第二次
mysql>  SELECT * FROM student_of_dirty_read WHERE id = 1;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

  • 事务B两次输出不一样，读到了为提交的数据！这就是脏读！

  • 可见，在Read Uncommitted隔离级别下，一个事务可能读取到另一个事务更新但未提交的数据，这个数据有可能是脏数据。

2、Read Committed

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

一点区别：

  • Read Uncommitted 隔离级别下，一个事务可以读到另一个事务更新数据后未提交的数据

  • Read Committed 隔离级别下，一个事务在另一个事务更新数据后，不能读取另一个事务更新数据后 **未提交 **的数据(或者说想读取更新后的数据，必须在提交事务后；在提交事务前，只能读取到更新前的数据)

仍然是上面的未修改的表：

分别开启两个MySql数据连接，按顺序执行下列命令：

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2 BEGIN; BEGIN;
3 UPDATE students SET name = ‘Bob’ WHERE id = 1;
4 SELECT * FROM students WHERE id = 1;
5 COMMIT;
6 SELECT * FROM students WHERE id = 1;
7 COMMIT;
  • SET TRANSACTION ISOLATION LEVEL READ COMMITTED：意思是设置事务隔离级别为Read Committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 第一次：
mysql> SELECT * FROM student_of_dirty_read;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

-- 第二次
mysql> SELECT * FROM student_of_dirty_read;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+
1 row in set (0.00 sec)

会发现事务B在事务A没提交的时候，只能读到原始数据，提交后才能读到修改后的数据。由于读到的两次数据不一致，所以这样的重复读会造成问题。

3、Repeatable Read

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

仍然是上面的未修改的表：

分别开启两个MySql数据连接，按顺序执行下列命令：

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 99;
4 INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5 COMMIT;
6 SELECT * FROM students WHERE id = 99;
7 UPDATE students SET name = ‘ccc’ WHERE id = 99;
8 SELECT * FROM students WHERE id = 99;
9 COMMIT;
  • SET TRANSACTION ISOLATION LEVEL REPEATABLE READ：意思是设置事务隔离级别为Repeatable Read
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 第一次：
mysql> SELECT * FROM student_of_dirty_read WHERE id = 99;
Empty set (0.00 sec)

-- 第二次：
mysql> SELECT * FROM student_of_dirty_read WHERE id = 99;
Empty set (0.00 sec)

-- 第三次
mysql> SELECT * FROM student_of_dirty_read;
+----+------+
| id | name |
+----+------+
|  1 | ccc  |
+----+------+
1 row in set (0.00 sec)

第一次读不到，第二次仍然读不到，第三次更新却可以进行，并且查询也可以正常进行，这就是幻读。

4、Serializable

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

5、默认隔离级别

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read.