mysql事务隔离的级别

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

这篇文章主要介绍“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,而其他的隔离级别不会。