mysql事务的隔离级别

事务的隔离级别

1. read uncommitted:产生脏读、不可重复读、幻读

   在session-1内开启事务进行插入操作，而不提交

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> insert into a_student values(0,'Li',20,'2018-7-25');
Query OK, 1 row affected

mysql> commit;
Query OK, 0 rows affected

​ 在一个session-2内开启事务进行查询，查到了session-1内未提交的数据；产生了脏读

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

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from a_student where id = 0;
+----+-------+------+---------------------+
| id | sname | sage | stime               |
+----+-------+------+---------------------+
|  0 | Li    |   20 | 2018-07-25 00:00:00 |
+----+-------+------+---------------------+
1 row in set

mysql> commit;
Query OK, 0 rows affected

mysql>    

2.read committed:产生不可重复读，幻读

​ 在session-1中查询一条记录，在session-2修改这条记录，在session-1中查询同一条记录结果发生了变化。

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from a_student where id = 0;
+----+-------+------+---------------------+
| id | sname | sage | stime               |
+----+-------+------+---------------------+
|  0 | Li    |   20 | 2018-07-25 00:00:00 |
+----+-------+------+---------------------+
1 row in set
mysql> select * from a_student where id = 0;
+----+-------+------+---------------------+
| id | sname | sage | stime               |
+----+-------+------+---------------------+
|  0 | Liu   |   20 | 2018-07-25 00:00:00 |
+----+-------+------+---------------------+
1 row in set

mysql> commit;
Query OK, 0 rows affected

​ session-2修改记录。

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected

mysql> update a_student set sname='Liu' where id=0;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected

3.repeatable read:有可能产生幻读

4.串行化、序列化

脏读	不可重复读	幻读
insert	update	insert

隔离等级	脏读	不可重复读	幻读
读未提交	NO	NO	NO
读已提交	YES	NO	NO
可重复读	YES	YES	YES/NO
串行化	YES	YES	YES