mysql事务隔离级别

今天被问到mysql都有哪些事务级别，什么是幻读，在什么情况下会出现幻读，-_-#，好吧，不记得了，赶紧恶补下。

安装mysql

前一段刚入手mac还没安装mysql呢。

//安装命令(默认安装最新版本)
brew install mysql
//启动mysql
mysql.server start
//关闭mysql
mysql.server stop
//进入mysql命令行默认下root没有密码
mysql -u root -p
//退出mysql命令行命令
quit;

mysql事务级别

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

InnoDB默认是可重复读。

SELECT @@global.tx_isolation,@@session.tx_isolation;//查询全局隔离级别,局部隔离级别

前期准备

create database test;//创建test数据库
use test;
create table user(
id int primary key,
name varchar(20));//创建表

各种读

• 脏读：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

窗口A：
mysql> select @@session.tx_isolation;//查询事务级别
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
mysql> start transaction;//开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user values(1,'wrh');
Query OK, 1 row affected (0.00 sec)
窗口B：
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
mysql> select * from user;
Empty set (0.00 sec)
mysql> set session transaction isolation level read uncommitted;//设置为未提交读
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
mysql> select * from user;//可以查看到1中没有提交事务
+----+------+
| id | name |
+----+------+
|  1 | wrh  |
+----+------+
1 row in set (0.00 sec)

• 不可重复读：在同一事务中，多次读取同一数据结果不同。事务A、事务B同时开启事务，事务A查询表中一条数据，事务B修改这条数据后提交，在事务A再次读取这条数据时读取到的就是事务B修改过的数据。

窗口A：
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | wrh  |
+----+------+
1 row in set
窗口B：
mysql> start transaction;
Query OK, 0 rows affected
mysql> insert into user values(2,'w');
Query OK, 1 row affected
窗口A：
mysql> select * from user;//读取不到未提交的数据
+----+------+
| id | name |
+----+------+
|  1 | wrh  |
+----+------+
1 row in set
窗口B：
mysql> commit;
Query OK, 0 rows affected
窗口A：
mysql> select * from user;//读取到事务B提交的数据
+----+------+
| id | name |
+----+------+
|  1 | wrh  |
|  2 | w    |
+----+------+
2 rows in set

如果窗口B的事务级别是可重复读，那么在事务A提交后，事务B读取到的结果依然不变，这个应该是在同一事务中对相同查询做了优化导致的。
- 幻读：顾名思义，就是产生幻觉的意思；^_^；事务A对表中的数据进行了修改，修改涉及到表中所有数据；事务B也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作事务A的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例子1：

窗口A：
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user;
Empty set
窗口B：
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
mysql> start transaction;
Query OK, 0 rows affected
mysql> insert into user values(1,'w');
Query OK, 1 row affected
窗口A：
mysql> select * from user;//重复读，结果不变
Empty set
窗口B：
mysql> commit;
Query OK, 0 rows affected
窗口A：
mysql> insert into user values(1,'r');//查询时不是没有数据嘛？一定是幻觉。
1062 - Duplicate entry '1' for key 1

例子2：

窗口A：
mysql> start transaction;
Query OK, 0 rows affected
mysql> select * from user where name='r';
+----+------+
| id | name |
+----+------+
|  1 | r    |
+----+------+
1 row in set
窗口B：
mysql> start transaction;
Query OK, 0 rows affected
mysql> delete from user where name='r';
Query OK, 1 row affected
mysql> commit;
Query OK, 0 rows affected
窗口A：
mysql> select * from user where name='r';
+----+------+
| id | name |
+----+------+
|  1 | r    |
+----+------+
1 row in set
mysql> update user set name='w' where name='r';//纳尼，我的数据哪里去了,+_+#
Query OK, 0 rows affected
Rows matched: 0  Changed: 0  Warnings: 0

针对例子2这种情况，我们可以通过给查询结果行加锁来避免，在select语句后面加上for update来给查询到的行加锁(悲观锁)，这样其他事务就不能对这条数据进行变更；但因为其悲观锁的特征，使用要慎重，避免出现死锁等问题。
还有一点需要注意的是for update、lock in share mode都会读取到最新的数据。

隔离级别

1. 未提交读:允许脏读,可能读取到其他会话中未提交事务修改的数据
2. 已提交读:只能读取到已经提交的数据
3. 可重复读:同一个事务内的查询都是事务开始时刻一致的
4. 可串行化:读(select)写会相互都会阻塞,-_-##效率一定很低。

这四个级别逐渐增强，每个级别解决一个问题；事务级别越高,性能越差，通常使用read committed即可。