Mysql的事务隔离级别

Mysql 有四种事务隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
读未提交（Read uncommitted）	可能	可能	可能
读已提交（Read committed	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

脏读一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，读取到了不想得到的数据了。
幻读第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
不可重复读 数据库访问中，在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

InnoDB默认是可重复读的（REPEATABLE READ），查询当前使用的隔离级别

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.01 sec)

test数据库测试新建一张表person测试

CREATE TABLE `person` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(255) DEFAULT NULL,
  `age` mediumint(9) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

脏读

我们开启两个客服端A、B备用

A 开启事务插入一条数据，不需要提交事务

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

mysql> insert into person (city)values('china');
Query OK, 1 row affected (0.01 sec)

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  1 | china | NULL | NULL |
+----+-------+------+------+
1 row in set (0.00 sec)

B 中并不会查询到A中事务提交的数据，不会出现脏读。

1 2	mysql> select * from person; Empty set (0.00 sec)

B客户端更改为读未提交（Read Uncommitted）那么就能读取到A事务中插入的数据。

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  1 | china | NULL | NULL |
+----+-------+------+------+
1 row in set (0.00 sec)

查询A中隔离级别

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

虽然A中的隔离级别是Repeatable read但是B指定Read Uncommitted级别照样读取到了其中的事务数据。

到此A的隔离级别为Repeatable read,修改B的隔离级别为Read committed

1 2	mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)

不可重复度

之前的例子中手动回滚了事务，此时person表是空的。我们A和B都开启一个事务，A中提交事务，观察B查询数据。

B 开启事务查询数据

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

mysql> select * from person;
Empty set (0.00 sec)

A开启事务插入一条记录

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

mysql> insert into person (city)values('china');
Query OK, 1 row affected (0.01 sec)

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

B事务中查询记录

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  2 | china | NULL | NULL |
+----+-------+------+------+
1 row in set (0.01 sec)

B事务中两次查询到数据不一致。不可重复读就是事务中数据不能进行重复读取，不然会导致数据不一致。

可重复读

同上面操作我们修改B的隔离级别为Repeatable read

1	set session transaction isolation level repeatable read

此时A和B的隔离级别是一致的，重复上面的操作

B 开启事务查询数据

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  2 | china | NULL | NULL |
+----+-------+------+------+
1 row in set (0.00 sec)

A开启事务插入一条记录

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

mysql> insert into person (city)values('china');
Query OK, 1 row affected (0.01 sec)

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

B事务中查询记录

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  2 | china | NULL | NULL |
+----+-------+------+------+
1 row in set (0.00 sec)

B事务中两次查询到数据是一致的。

幻读

A 和B事务中进行操作，步骤接替进行看效果

A

1 2	mysql> begin; Query OK, 0 rows affected (0.00 sec)

B

1 2	mysql> begin; Query OK, 0 rows affected (0.00 sec)

A

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  2 | china | NULL | NULL |
|  3 | china | NULL | NULL |
+----+-------+------+------+
2 rows in set (0.01 sec)

B

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  2 | china | NULL | NULL |
|  3 | china | NULL | NULL |
+----+-------+------+------+
2 rows in set (0.00 sec)

A

1 2	mysql> insert into person (id,city)values(1,'xm'); Query OK, 1 row affected (0.00 sec)

B

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  2 | china | NULL | NULL |
|  3 | china | NULL | NULL |
+----+-------+------+------+
2 rows in set (0.00 sec)

A

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

B

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  2 | china | NULL | NULL |
|  3 | china | NULL | NULL |
+----+-------+------+------+
2 rows in set (0.00 sec)

A

mysql> select * from person;
+----+-------+------+------+
| id | city  | age  | name |
+----+-------+------+------+
|  1 | xm    | NULL | NULL |
|  2 | china | NULL | NULL |
|  3 | china | NULL | NULL |
+----+-------+------+------+
3 rows in set (0.00 sec)

B

1 2	mysql> insert into person(id,city)values(1,"xm2"); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

仔细对比可以发现，即使A中提交了新数据，但是B中并不会找到新增加的数据，所以天真的以为可以添加一条新数据，这时候mysql报出来主键重复了，就像是产生幻觉一样。

在B查询语句可以知道数据库只有两条记录，如果我们在B中更新记录

1
2
3

mysql> update person set city='b';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

咦！怎么有三条记录更新，不是两条吗。这同样产生了幻觉。

*注意 *当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。

当然如果使用update语句，mysql会加锁会保证数据一致。

A

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

mysql> update person set city='a' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

B等待A释放锁或者超时

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

mysql> update person set city='b' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction