mysql的四种隔离级别

隔离级别

英文

说明

问题

未提交读

read uncommitted (RU)

A事务已执行，但未提交；B事务查询到A事务的更新后数据；A事务回滚。

脏读

已提交读

read committed (RC)

A事务执行更新；B事务查询；A事务又执行更新；B事务再次查询时，前后两次数据不一致。

不可重复读

可重复读

repeatable read (RR)

A事务无论执行多少次，只要不提交，B事务查询值都不变；B事务仅查询B事务开始时那一瞬间的数据快照。

幻读

串行化

serializable

不允许读写并发操作，写执行时，读必须等待。

默认隔离级别

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

设置隔离级别

# 设置read uncommitted级别：
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

# 设置read committed级别：
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

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

# 设置repeatable read级别：
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

# 设置serializable级别：
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

设置mysql事务隔离级别_异步