mysql事务隔离的级别

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

我们提供的服务有：成都网站制作、成都网站设计、微信公众号开发、网站优化、网站认证、宁江ssl等。为上千余家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的宁江网站制作公司

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事务隔离的级别”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！

网页标题：mysql事务隔离的级别
网站地址：http://scjbc.cn/article/jigpes.html

mysql事务隔离的级别

其他资讯