本文主要讨论 mysql 中的事务隔离级别。

总述

Innodb 存储引擎实现了四中事务隔离级别。

（一）读未提交（ READ UNCOMMITTED ），简称 RU ，一个事务可以读到另一个事务的未提交更新，也就是脏读的现象。

（二）读已提交（ READ COMMITTED ） , 简称 RC ，也叫不可重复读， Oracle 的默认隔离级别。一个事务中可以读到其他事务的提交 dml 动作。该隔离级别解决了脏读的问题，但是针对提交的 insert 语句等，会出现幻读的现象。

（三）可重复读（ REPEATABLE-READ ）是 mysql 默认的事务隔离级别，简称 RR 。该级别解决了之前存在的幻读，脏读现象。

（四）串行（ SERIALIZABLE ），对读取的数据行加表级共享锁，更新数据时加表级排他锁，基本没什么并发能力了。

RU 实验

mysql5.7.22 默认级别

mysql> show variables like '%iso%';

+-----------------------+-----------------+

| Variable_name | Value |

+-----------------------+-----------------+

| transaction_isolation | REPEATABLE-READ |

| tx_isolation | REPEATABLE-READ |

+-----------------------+-----------------+

2 rows in set (0.00 sec)

两个会话在会话级别设置成读未提交级别。

会话级别修改

mysql> set session tx_isolation='READ-UNCOMMITTED';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%iso%';

+-----------------------+------------------+

| Variable_name | Value |

+-----------------------+------------------+

| transaction_isolation | READ-UNCOMMITTED |

| tx_isolation | READ-UNCOMMITTED |

+-----------------------+------------------+

2 rows in set (0.00 sec)

会话 a

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+-----------------+

| a |

+-----------------+

| 1 |

| 255.255.255.255 |

+-----------------+

2 rows in set (0.00 sec)

会话 b 插入一条数据

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(2);

Query OK, 1 row affected (0.00 sec)

返回会话 a

mysql> select * from test1;

+-----------------+

| a |

+-----------------+

| 1 |

| 2 |

| 255.255.255.255 |

+-----------------+

3 rows in set (0.00 sec)

RC 实验

设置会话级别隔离级别

mysql> set session tx_isolation='READ-COMMITTED';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%iso%';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

| tx_isolation | READ-COMMITTED |

+-----------------------+----------------+

会话 a

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+-----------------+

| a |

+-----------------+

| 1 |

| 2 |

| 255.255.255.255 |

+-----------------+

3 rows in set (0.00 sec)

会话 b 插入一条数据，未提交

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(3);

Query OK, 1 row affected (0.01 sec)

此时会话 a 看不到

mysql> select * from test1;

+-----------------+

| a |

+-----------------+

| 1 |

| 2 |

| 255.255.255.255 |

+-----------------+

3 rows in set (0.00 sec)

会话 b 提交

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

返回会话 a

mysql> select * from test1;

+-----------------+

| a |

+-----------------+

| 1 |

| 2 |

| 255.255.255.255 |

| 3 |

+-----------------+

4 rows in set (0.00 sec)

这种现象就是幻影读了。

RR 实验

mysql> show variables like '%tx%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

| tx_read_only | OFF |

+---------------+-----------------+

2 rows in set (0.00 sec)

会话 a

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

+-----------------+

| a |

+-----------------+

| 1 |

| 2 |

| 255.255.255.255 |

| 3 |

| 4 |

| 5 |

+-----------------+

6 rows in set (0.00 sec)

会话 b 插入一条数据，未提交

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(6);

Query OK, 1 row affected (0.00 sec)

会话 a 没有看到会话 b 新插入的数据

mysql> select * from test1;

+-----------------+

| a |

+-----------------+

| 1 |

| 2 |

| 255.255.255.255 |

| 3 |

| 4 |

| 5 |

+-----------------+

6 rows in set (0.00 sec)

会话 b 提交

mysql> commit;

会话 a 还是没有看到

mysql> select * from test1;

+-----------------+

| a |

+-----------------+

| 1 |

| 2 |

| 255.255.255.255 |

| 3 |

| 4 |

| 5 |

+-----------------+

6 rows in set (0.00 sec)

想要看到的话需要加 for update

mysql> select * from test1 for update;

+-----------------+

| a |

+-----------------+

| 1 |

| 2 |

| 255.255.255.255 |

| 3 |

| 4 |

| 5 |

| 6 |

+-----------------+

7 rows in set (0.00 sec)

串行实验

串行（ SERIALIZABLE ），对读取的数据行加表级共享锁，更新数据时加表级排他锁，基本没什么并发能力了。

设置会话级别事务隔离级别

mysql> set session tx_isolation='serializable';

Query OK, 0 rows affected, 1 warning (0.00 sec)

会话 a 删除一行数据

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from test1 where a='6';

Query OK, 1 row affected (0.00 sec)

会话 bselect 语句都会夯住

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;

会话 c 查看锁的状态

INFORMATION_SCHEMA.INNODB_LOCKS;

+-------------------------+-----------------+-----------+-----------+------------------+
| lock_id                 | lock_trx_id     | lock_mode | lock_type | lock_table       |
+-------------------------+-----------------+-----------+-----------+------------------+
| 421706548341472:158:4:8 | 421706548341472 | S          | RECORD    | `mingdb`.`test1` |
| 14604:158:4:8           | 14604           | X          | RECORD    | `mingdb`.`test1` |
+-------------------------+-----------------+-----------+-----------+------------------+

过了一段时间后，会话 b 返回

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这个时间是由 innodb_lock_wait_timeout 决定的

mysql> show variables like 'innodb_lock_wait_timeout';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 50 |

+--------------------------+-------+

1 row in set (0.01 sec)

ITPub博客

mysql事务隔离级别

总述

RU 实验

RC 实验

RR 实验

串行实验

最新文章