mysql中事务的隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
不可重复读（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否

查看事务隔离级别的sql:

select @@tx_isolation;

  1. mysql> select @@tx_isolation;
  2. +----------------+
  3. | @@tx_isolation |
  4. +----------------+
  5. | READ-COMMITTED |
  6. +----------------+
  7. 1 row in set, 1 warning (0.01 sec)
  9. mysql>

设置不同的隔离级别；

①：set global（session） transaction isolation level read committed;

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@tx_isolation
    -> ;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

②：set global(session) tx_isolation=2;(事务的隔离级别的顺序)

mysql> set global tx_isolation=2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql>


隔离级别与锁的算法：

InnoDB中每种隔离级别都会使用一种锁的算法来保证对数据的隔离和保护
可重复读（repeatable-read）—>Next-KeyLock锁的算法
不可重复读（read-committed）—>Record Lock锁的算法
扩展：事务的隔离级别越低，请求锁的时间就越少保持锁的时间就越短，但是在mysql中锁的级别与事务的性能无关

脏读：

脏读是指读取事务为提交的数据，在事务的隔离级别为read-uncommitted时出现。<br />    列：脏读(需要将事务的隔离级别设置read-uncommitted)<br />①：设置事务的隔离级别为read-uncommitted<br />  **  session 1**

mysql> set session tx_isolation=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

session 2

mysql> set session tx_isolation=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

②：
在session1中开启事务，并查询test表中的记录

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

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)

③ :session2中在test表中 插入一条数据,同时不提交事务

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

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into test values (4,'aa');
Query OK, 1 row affected (0.00 sec)

mysql>

④：再次在session 1 中查询test中的记录,发现查询到了session2未提及的数据

mysql> select  * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
|  4 | aa   |
+----+------+
4 rows in set (0.00 sec)

mysql>

不可重复读：

   也就是说读取的数据时不重复的，是指在本事务未提及的情况下读取到气体事务已提交的数据,需要事务的隔离级别为；READ-COMMITTED<br />    列：不可重复读：<br />    ①：设置事务的隔离级别为READ-COMMITTED<br />session 1

mysql> set session tx_isolation=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

session 2

mysql> set session tx_isolation=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

②：在开启事务的前提下session 1查询test中的记录,同时session 2在test 表中插入一条数据并提交

session 1

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

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)

session 2

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

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into test values (4,'cc');
Query OK, 1 row affected (0.00 sec)

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

mysql>

③：session 1 在没有提交事务的前提下再次读取表test中的数据，出现不可重复读现象

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
|  4 | cc   |
+----+------+
4 rows in set (0.00 sec)

mysql>

可重复读（mysql默认的事务隔离级）：

 读取的数据都是当前生成的快照，在没有提交事务的前提下是重复的。是指只有在事务都提交的请况下才能读取到提交的数据 需要事务的隔离级别为：REPEATABLE-READ(mysql默认的事务隔离级别)<br />  列:可重复读<br />    ①：设置事务的隔离级别为REPEATABLE-READ<br />        session 1

mysql> set tx_isolation=2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql>

    session 2

mysql> set tx_isolation=2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql>

②：开启事务的前提下 session 1 读取test表中记录，session 2 插入一条记录并提交
session 1

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

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
|  4 | cc   |
|  5 | ee   |
+----+------+
5 rows in set (0.00 sec)

session 2

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

mysql> insert into test values (6,'ff');
Query OK, 1 row affected (0.00 sec)

③：session 1再次读取，发现没有读取到session 2提交的数据

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
|  4 | cc   |
|  5 | ee   |
+----+------+
5 rows in set (0.00 sec)

④：当session 1提交事务之后才读取到session 2提交的数据 

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | zx   |
|  2 | zx   |
|  3 | cc   |
|  4 | cc   |
|  5 | ee   |
|  6 | ff   |
+----+------+
6 rows in set (0.00 sec)

mysql>

幻读：

 是指在read-committed的锁的算法锁的算法为record lock此时锁定的只是单行记录(和不可重复读类)。