本文主要讨论 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) |
