mysql事务隔离的级别

这篇文章主要介绍“mysql事务隔离的级别”，在日常操作中，相信很多人在mysql事务隔离的级别问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql事务隔离的级别”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

ANSI SQL标准定义了4中隔离级别：
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

ANSI隔离级别
隔离级别                        脏读         不可重复读       幻像读
READ UNCOMMITTED      允许          允许               允许
READ COMMITTED          不允许       允许               允许
REPEATABLE READ          不允许       不允许           允许
SERIALIZABLE                不允许       不允许           不允许

以下给出测试的例子。

1.READ UNCOMMITTED

session 1:
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

session 2:
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> set tx_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>隔离级别设置为READ-UNCOMMITTED后，可以看到未提交的数据。

2、READ-COMMITTED

session 1;
mysql> SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:
mysql>  SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 1:
mysql> insert into t values (2);
Query OK, 1 row affected (0.02 sec)

mysql> update t set id=3 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session2:
mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>session1的数据提交后，session2的事务中能够读到最新的数据。

3、REPEATABLE READ

session1:
mysql> SET tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session2:
mysql> SET tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

session 1:
mysql> update t set id=1  where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:

mysql>  select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

==>这里查出的数据还是事务开始时的数据，而不是最新的数据。
这里幻象读的现象被解决了，是因为InnoDb引擎通过间隙锁（nex-key locking)策略防止幻读。
这里和标准的REPEATABLE READ隔离方式的现象有点出入。

4、SERIALIZABLE 

session 1:
mysql> SET tx_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:
mysql> SET tx_isolation = 'SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

==>这里会给表t中的记录加锁（即加锁读），通过下列查询可以看到：

mysql> SELECT trx_id, trx_state, trx_rows_locked, trx_rows_modified FROM information_schema.INNODB_TRX;
+--------+-----------+-----------------+-------------------+
| trx_id | trx_state | trx_rows_locked | trx_rows_modified |
+--------+-----------+-----------------+-------------------+
| 9598   | RUNNING   |               3 |                 0 |
+--------+-----------+-----------------+-------------------+

session 1:
mysql> update t set id=2 where id =1;
Query OK, 1 row affected (27.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

==》这里时间用了27.05 sec，是因为在等待session2释放锁，直到session 1发出commit或者rollback命令后，才能获得锁。

总结：
1、REPEATABLE READ为mysql默认的隔离级别方式,不允许重复读和幻象读。
2、InnoDb引擎通过间隙锁（nex-key locking)策略防止幻读。
3、SERIALIZABLE 隔离方式下会加锁读，类似于select … for update,而其他的隔离级别不会。

到此，关于“mysql事务隔离的级别”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！