1. 事務(wù)Demo
1.1 原數(shù)據(jù)
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 100 |
| 2 | superman | 100 |
+----+----------+--------+
2 rows in set (0.00 sec)
1.2 事務(wù)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 事務(wù)2：查看數(shù)據(jù)
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 100 |
| 2 | superman | 100 |
+----+----------+--------+
2 rows in set (0.00 sec)
1.4 事務(wù)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 事務(wù)2：查看數(shù)據(jù)
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 0 |
| 2 | superman | 200 |
+----+----------+--------+
2 rows in set (0.00 sec)
總結(jié)
- 在事務(wù)1中未提交的更改，在事務(wù)2中無法看到；
2. 可重復(fù)讀Demo
2.1 查看MySQL默認(rèn)事務(wù)隔離級別
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 事務(wù)2：讀數(shù)據(jù)，不提交
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 事務(wù)1：更改數(shù)據(jù)，不提交
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 事務(wù)2：查數(shù)據(jù)
- 因?yàn)槭聞?wù)1未提交，所以事務(wù)2中未看到事務(wù)1中對數(shù)據(jù)的更改；
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 100 |
| 2 | superman | 100 |
+----+----------+--------+
2 rows in set (0.00 sec)
2.5 事務(wù)1：提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
2.6 事務(wù)2：查數(shù)據(jù)
- 仍未看到事務(wù)1對數(shù)據(jù)的更改；
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 100 |
| 2 | superman | 100 |
+----+----------+--------+
2 rows in set (0.00 sec)
2.7 事務(wù)2：提交，查數(shù)據(jù)
- 已看到事務(wù)1對數(shù)據(jù)的修改；
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)
可重復(fù)讀總結(jié)
- 事務(wù)2中對數(shù)據(jù)的兩次查詢之間；
- 在事務(wù)2沒提交的前提下；
- 即使事務(wù)1中對數(shù)據(jù)的更改已提交；
- 在事務(wù)2中仍無法看到事務(wù)1對數(shù)據(jù)的更改；
- 直到事務(wù)2提交；
3. 臟讀Demo
3.1 設(shè)置事務(wù)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 事務(wù)2：查詢數(shù)據(jù)
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 0 |
| 2 | superman | 200 |
+----+----------+--------+
2 rows in set (0.00 sec)
3.3 事務(wù)1：執(zhí)行事務(wù)，不提交
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 事務(wù)2：查數(shù)據(jù)
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 0 |
| 2 | superman | 100 |
+----+----------+--------+
2 rows in set (0.00 sec)
3.5 事務(wù)1：執(zhí)行事務(wù)，不提交
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 事務(wù)2：查數(shù)據(jù)
- 還是能看到事務(wù)1未提交的數(shù)據(jù)
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 100 |
| 2 | superman | 100 |
+----+----------+--------+
2 rows in set (0.00 sec)
3.7 事務(wù)1：提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
3.8 事務(wù)2：查數(shù)據(jù)
mysql> select * from t_user;
+----+----------+--------+
| id | username | amount |
+----+----------+--------+
| 1 | batman | 100 |
| 2 | superman | 100 |
+----+----------+--------+
2 rows in set (0.00 sec)
臟讀總結(jié)
- 臟讀就是事務(wù)2可以讀到事務(wù)1中尚未提交的更改；
4. MySQL的4種隔離級別
- READ UNCOMMITTED: 可以讀到未提交的數(shù)據(jù)；
- READ COMMITTED: 事務(wù)2可以讀到不一樣的數(shù)據(jù)；
- REPEATABLE READ: 事務(wù)2中讀到的數(shù)據(jù)肯定是一樣的；
- SERIALIZABLE: 所有的事務(wù)操作必須是線性執(zhí)行；
