事务B就读到了临时数据
演示二
会话A
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance - 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update account set balance = balance + 100 where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 0 | | 2 | 李四 | 100 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql>
会话B（未提交读）
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 0 | | 2 | 李四 | 100 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql> update account set balance = balance - 100 where id = 2; ###卡住了
会话A
mysql> rollback; Query OK, 0 rows affected (0.00 sec)
会话B
###不卡了 Query OK, 1 row affected (6.54 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update account set balance = balance + 100 where id = 1; 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) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 200 | | 2 | 李四 | -100 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql>
演示2：读已提交
mysql> truncate table account; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO account VALUES(1 ,'张三', '100'),(2,'李四', '0'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 100 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec)
会话A&B
mysql> set session transaction_isolation = 'read-committed'; Query OK, 0 rows affected (0.00 sec)
会话B
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 100 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql>
会话A
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance-50 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 50 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql>
会话B
#解决了脏读 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 100 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.01 sec) mysql>
会话A
mysql> commit; Query OK, 0 rows affected (0.01 sec)
会话B
#未解决不可重复读 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 50 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 50 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec)
演示3：可重复读
会话A&B
mysql> set session transaction_isolation = 'repeatable-read'; Query OK, 0 rows affected (0.00 sec) mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec)
会话B
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 50 | | 2 | 李四 | 0 | +----+--------+---------+
会话A
mysql> update account set balance = balance - 10 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 40 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec)
会话B
#避免了脏读 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 50 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql>
会话A
mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 40 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec)
会话B
#解决不可重复读 mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 50 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------+---------+ | id | name | balance | +----+--------+---------+ | 1 | 张三 | 40 | | 2 | 李四 | 0 | +----+--------+---------+ 2 rows in set (0.00 sec) mysql>
