InnoDB事务隔离级别分析

总结下MySQL的InnoDB引擎在事务隔离方面的特性,然后分别通过mysql操作来分析下各个隔离级别

NewImage

可以查一下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)中也不去读取

NewImage

 

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读取的就是脏数据,这种就是脏读

NewImage

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才能看到变更

NewImage

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)

所以这种隔离比较直接,事务每次操作锁表,其他事务无法进行写操作

NewImage

 

最后,简要总结一下:

读未提交:事务B修改数据就算没提交,事务A也能读取到

读已提交:事务B修改数据必须提交了,事务A才能读取到

可重复读:事务B修改数据就算提交了,事务A不能读取到

串行化:事务B修改数据只要没有提交,事务A就没法修改

发表评论