总结下MySQL的InnoDB引擎在事务隔离方面的特性，然后分别通过mysql操作来分析下各个隔离级别
可以查一下MySQL默认的隔离级别是可重复读REPEATABLE-READ
mysql root@localhost:(none)> show variables like 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set Time: 0.018s
可以通过简单的数据库操作来区分这几个隔离级别，在此首先创建一张`user_center`.`user_info`表，插入如下三条数据
1、先看下默认的可重复读REPEATABLE-READ，可以开两个客户端A和B
事务A查询如下
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
事务B插入一条数据
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user_info values(4, 'lucy', '16666666666', 16);
Query OK, 1 row affected (0.00 sec)
mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info;
+----+-------+-------------+------+
| id | name | phone | age |
+----+-------+-------------+------+
| 1 | lihui | 17777777777 | 18 |
| 2 | lilei | 18888888888 | 19 |
| 3 | litao | 19999999999 | 20 |
| 4 | lucy | 16666666666 | 16 |
+----+-------+-------------+------+
4 rows in set (0.00 sec)
事务A查询的时候，根本就没有lucy这条数据，但是是没法插入的，好像产生了幻觉一样
mysql> insert into user_info values(4, 'lucy', '16666666666', 16); ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
换一种方式，如果事务B插入了数据，但是不提交
首先事务A查询如下
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | | 4 | lucy | 16666666666 | 16 | +----+-------+-------------+------+ 4 rows in set (0.00 sec)
事务B插入一条数据，但是不提交
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into user_info values(5, 'lily', '15555555555', 15); Query OK, 1 row affected (0.00 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | | 4 | lucy | 16666666666 | 16 | | 5 | lily | 15555555555 | 15 | +----+-------+-------------+------+ 5 rows in set (0.00 sec)
然后事务A在插入同一条数据的时候，挂起卡住阻塞了
mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | | 4 | lucy | 16666666666 | 16 | +----+-------+-------------+------+ 4 rows in set (0.00 sec) mysql> mysql> insert into user_info values(5, 'lily', '15555555555', 15); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
此时如果在阻塞的时候，将事务B给commit提交了，那么事务A就会提示和上面一样的错误，已经存在，commit提交之后，会看到插入的数据
mysql> insert into user_info values(5, 'lily', '15555555555', 15);
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info;
+----+-------+-------------+------+
| id | name | phone | age |
+----+-------+-------------+------+
| 1 | lihui | 17777777777 | 18 |
| 2 | lilei | 18888888888 | 19 |
| 3 | litao | 19999999999 | 20 |
| 4 | lucy | 16666666666 | 16 |
| 5 | lily | 15555555555 | 15 |
+----+-------+-------------+------+
5 rows in set (0.00 sec)
可见可重复读的隔离级别下，事务未提交变更对其它事务是不可见的，也就是别的事务（事务B）的修改就算提交了，我的事务（事务A）中也不去读取
2、接着的隔离级别是读未提交read-uncommitted
首先可以修改隔离级别
mysql> set session transaction_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'transaction_isolation'; +-----------------------+------------------+ | Variable_name | Value | +-----------------------+------------------+ | transaction_isolation | READ-UNCOMMITTED | +-----------------------+------------------+ 1 row in set (0.02 sec)
事务A的查询结果
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
事务B修改了lihui的age，但是并没有提交
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set age=28 where name='lihui'
-> ;
ERROR 1146 (42S02): Table 'user_center.user' doesn't exist
mysql>
mysql> update user_info set age=28 where name='lihui';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
事务A却看到了lihui年龄的修改数据28
mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 28 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
此时将事务B回滚，因为还没提交
mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 28 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
再来看看事务A，lihui的age又变成了原始的18
mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
当事务B回滚前，未提交的修改，被事务A读取的就是脏数据，这种就是脏读
3、然后的隔离级别是读已提交read-committed
首先修改隔离级别
mysql> set session transaction_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'transaction_isolation'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.00 sec)
事务A的查询结果
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
事务B修改lihui的age为28
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update user_info set age=28 where name='lihui'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
事务A这时候是看不见变更的
mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
事务B提交
mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info;
+----+-------+-------------+------+
| id | name | phone | age |
+----+-------+-------------+------+
| 1 | lihui | 17777777777 | 28 |
| 2 | lilei | 18888888888 | 19 |
| 3 | litao | 19999999999 | 20 |
+----+-------+-------------+------+
3 rows in set (0.00 sec)
事务A才能看到变更
4、最后的隔离级别是串行serializable
修改隔离级别
mysql> set session transaction_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'transaction_isolation'; +-----------------------+--------------+ | Variable_name | Value | +-----------------------+--------------+ | transaction_isolation | SERIALIZABLE | +-----------------------+--------------+ 1 row in set (0.00 sec)
首先事务A查询
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec)
然后在事务B插入一条新的数据的时候（对比可重复读），阻塞了，原因就是事务A没有提交，将整张表给锁住了
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into user_info values(4, 'lucy', '16666666666', 16); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
事务A进行提交commit，可见上面事务B的确没有insert成功
mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info;
+----+-------+-------------+------+
| id | name | phone | age |
+----+-------+-------------+------+
| 1 | lihui | 17777777777 | 18 |
| 2 | lilei | 18888888888 | 19 |
| 3 | litao | 19999999999 | 20 |
+----+-------+-------------+------+
3 rows in set (0.00 sec)
事务B再进行insert操作，成功，但未提交
mysql> insert into user_info values(4, 'lucy', '16666666666', 16); Query OK, 1 row affected (0.00 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | | 4 | lucy | 16666666666 | 16 | +----+-------+-------------+------+ 4 rows in set (0.00 sec)
此时通过事务A查询，无法看到lucy的数据，并且假如想新插入一条lily数据也会被阻塞，
mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | +----+-------+-------------+------+ 3 rows in set (0.00 sec) mysql> insert into user_info values(5, 'lily', '15555555555', 15); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
只有当事务B提交了之后，事务A才能看到变更以及继续操作
mysql> insert into user_info values(5, 'lily', '15555555555', 15); Query OK, 1 row affected (0.01 sec) mysql> select * from user_info; +----+-------+-------------+------+ | id | name | phone | age | +----+-------+-------------+------+ | 1 | lihui | 17777777777 | 18 | | 2 | lilei | 18888888888 | 19 | | 3 | litao | 19999999999 | 20 | | 4 | lucy | 16666666666 | 16 | | 5 | lily | 15555555555 | 15 | +----+-------+-------------+------+ 5 rows in set (0.00 sec)
所以这种隔离比较直接，事务每次操作锁表，其他事务无法进行写操作
最后，简要总结一下：
读未提交：事务B修改数据就算没提交，事务A也能读取到
读已提交：事务B修改数据必须提交了，事务A才能读取到
可重复读：事务B修改数据就算提交了，事务A不能读取到
串行化：事务B修改数据只要没有提交，事务A就没法修改
