SQL事务管理

1. 事务Demo

1.1 原数据

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |    100 |
|  2 | superman |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

1.2 事务1：部分更改，不提交

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

mysql> update t_user set amount = amount - 100 where username='batman';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

1.3 事务2：查看数据

• 数据没变；

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |    100 |
|  2 | superman |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

1.4 事务1：完成更改并提交

mysql> update t_user set amount = amount + 100 where username='superman';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

1.5 事务2：查看数据

• 数据已变化；

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |      0 |
|  2 | superman |    200 |
+----+----------+--------+
2 rows in set (0.00 sec)

总结

• 在事务1中未提交的更改，在事务2中无法看到；

2. 可重复读Demo

2.1 查看MySQL默认事务隔离级别

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

2.2 事务2：读数据，不提交

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

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |    100 |
|  2 | superman |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> select * from t_user where username = 'superman';
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  2 | superman |    100 |
+----+----------+--------+
1 row in set (0.00 sec)

2.3 事务1：更改数据，不提交

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

mysql> update t_user set amount = amount - 100 where username='batman';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t_user set amount = amount + 100 where username='superman';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.4 事务2：查数据

• 因为事务1未提交，所以事务2中未看到事务1中对数据的更改；

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |    100 |
|  2 | superman |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

2.5 事务1：提交

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

2.6 事务2：查数据

• 仍未看到事务1对数据的更改；

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |    100 |
|  2 | superman |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

2.7 事务2：提交，查数据

• 已看到事务1对数据的修改；

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

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |      0 |
|  2 | superman |    200 |
+----+----------+--------+
2 rows in set (0.00 sec)

可重复读总结

• 事务2中对数据的两次查询之间；
• 在事务2没提交的前提下；
• 即使事务1中对数据的更改已提交；
• 在事务2中仍无法看到事务1对数据的更改；
• 直到事务2提交；

3. 脏读Demo

3.1 设置事务2隔离级别

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

3.2 事务2：查询数据

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |      0 |
|  2 | superman |    200 |
+----+----------+--------+
2 rows in set (0.00 sec)

3.3 事务1：执行事务，不提交

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

mysql> update t_user set amount = amount - 100 where username='superman';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3.4 事务2：查数据

• 可以看到事务1中未提交的更改；

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |      0 |
|  2 | superman |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

3.5 事务1：执行事务，不提交

mysql> update t_user set amount = amount + 100 where username='batman';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3.6 事务2：查数据

• 还是能看到事务1未提交的数据

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |    100 |
|  2 | superman |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

3.7 事务1：提交

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

3.8 事务2：查数据

mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
|  1 | batman   |    100 |
|  2 | superman |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

脏读总结

• 脏读就是事务2可以读到事务1中尚未提交的更改；

4. MySQL的4种隔离级别

• READ UNCOMMITTED: 可以读到未提交的数据；
• READ COMMITTED: 事务2可以读到不一样的数据；
• REPEATABLE READ: 事务2中读到的数据肯定是一样的；
• SERIALIZABLE: 所有的事务操作必须是线性执行；