MySQL 解决幻读的方法

MySQL 幻读的详解、实例及解决办法

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(25) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

InnoDB 支持事务, MyISAM不支持事务;

事务T1

mysql> select @@global.tx_isolation, @@tx_isolation;

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

| @@global.tx_isolation | @@tx_isolation |

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

| REPEATABLE-READ | REPEATABLE-READ |

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

1 row in set, 2 warnings (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where id = 1;

Empty set (0.00 sec)

mysql> insert into users values(1, 'big_cat');

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

这里就出现了幻读

mysql> select * from users where id = 1;

Empty set (0.00 sec)

解决方法: 使用" for update "行锁解决

mysql> select * from users where id = 1 for update;

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

| id | name |

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

| 1 | big_cat |

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

1 row in set (0.00 sec)

事务T2

mysql> select @@global.tx_isolation, @@tx_isolation;

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

| @@global.tx_isolation | @@tx_isolation |

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

| REPEATABLE-READ | REPEATABLE-READ |

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

1 row in set, 2 warnings (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into users values(1, 'big_cat');

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

step1 T1: SELECT * FROM `users` WHERE `id` = 1;

step2 T2: INSERT INTO `users` VALUES (1, 'big cat');

step3 T1: INSERT INTO `users` VALUES (1, 'big cat');

step4 T1: SELECT * FROM `users` WHERE `id` = 1;

T1 ：主事务, 检测表中是否有 id 为 1 的记录, 没有则插入, 这是我们期望的正常业务逻辑。

T2 ：干扰事务, 目的在于扰乱 T1 的正常的事务执行。

在 RR 隔离级别下, step1、step2 是会正常执行的, step3 则会报错主键冲突, 对于 T1 的业务来说是执行失败的, 这里 T1 就是发生了幻读,

因为 T1 在 step1 中读取的数据状态并不能支撑后续的业务操作, T1："见鬼了，我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以"。

T1 不敢相信的又执行了 step4, 发现和 setp1 读取的结果是一样的(RR下的 MMVC机制)。此时, 幻读无疑已经发生, T1 无论读取多少次, 都查不到 id = 1 的记录, 但它的确无法插入这条他通过读取来认定不存在的记录

(此数据已被T2插入), 对于 T1 来说, 它幻读了。

其实 RR 也是可以避免幻读的, 通过对 select 操作手动加 行X锁(SELECT ... FOR UPDATE 这也正是 SERIALIZABLE 隔离级别下会隐式为你做的事情), 同时还需要知道, 即便当前记录不存在,

比如 id = 1 是不存在的, 当前事务也会获得一把记录锁(因为InnoDB的行锁锁定的是索引, 故记录实体存在与否没关系, 存在就加 行X锁, 不存在就加 next-key lock间隙X锁), 其他事务则无法插入此索引的记录, 故杜绝了幻读。

在 SERIALIZABLE 隔离级别下, step1 执行时是会隐式的添加 行(X)锁 / gap(X)锁的, 从而 step2 会被阻塞, step3 会正常执行, 待 T1 提交后, T2 才能继续执行(主键冲突执行失败), 对于 T1 来说业务是正确的,

成功的阻塞扼杀了扰乱业务的T2, 对于T1来说他前期读取的结果是可以支撑其后续业务的。

所以 mysql 的幻读并非什么读取两次返回结果集不同, 而是事务在插入事先检测不存在的记录时, 惊奇的发现这些数据已经存在了, 之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思, 第一次select是读取, 第二次的 insert 其实也属于隐式的读取, 只不过是在 mysql 的机制中读取的, 插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读, 幻读则是说 读-写, 用写来证实读的是鬼影。

方法一: RR级别下防止幻读

RR级别下只要对 SELECT 操作也手动加行(X)锁即可类似 SERIALIZABLE 级别(它会对 SELECT 隐式加锁), 即大家熟知的：

# 这里需要用 X锁， 用 LOCK IN SHARE MODE 拿到 S锁 后我们没办法做 写操作

SELECT `id` FROM `users` WHERE `id` = 1 FOR UPDATE;

如果 id = 1 的记录存在则会被加行(X)锁, 如果不存在, 则会加 next-lock key / gap 锁(范围行锁), 即记录存在与否, mysql 都会对记录应该对应的索引加锁, 其他事务是无法再获得做操作的。

这里我们就展示下 id = 1 的记录不存在的场景, FOR UPDATE 也会对此 "记录" 加锁, 要明白, InnoDB 的行锁(gap锁是范围行锁，一样的)锁定的是记录所对应的索引, 且聚簇索引同记录是直接关系在一起的。

T1事务

set @@session.tx_isolation=2;

select @@tx_isolation;

select * from users;

begin;

//这里使用行锁解决

select id from users where id = 1 for update;

insert into users values(1, 'big_cat');

commit;

T2事务

set @@session.tx_isolation=2;

select @@tx_isolation;

begin;

inset into users values (1, 'big_cat');

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

commit;

id = 1 的记录不存在, 开始执行事务：

step1: T1 查询 id = 1 的记录并对其加 X锁

step2: T2 插入 id = 1 的记录, 被阻塞

step3: T1 插入 id = 1 的记录, 成功执行(T2 依然被阻塞中), T1 提交(T2 唤醒但主键冲突执行错误)

T1事务符合业务需求成功执行, T2干扰T1失败。

方法二: SERIALIZABLE级别杜绝幻读

在此级别下, 我们便不需要对 SELECT 操作显式加锁, InnoDB会自动加锁, 事务安全, 但性能很低

T1事务

set @@session.tx_isolation=3;

select @@tx_isolation;

begin;

select * from users where id = 2;

insert into users values(2, 'big_cat');

commit;

T2事务

set @@session.tx_isolation=3;

select @@tx_isolation;

begin;

inset into users values (2, 'big_cat');

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

commit;

step1: T1 查询 id = 2 的记录, InnoDB 会隐式的对齐加 X锁

step2: T2 插入 id = 2 的记录, 被阻塞

step3: T1 插入 id = 2 的记录, 成功执行(T2 依然被阻塞中)

step4: T1 成功提交(T2 此时唤醒但主键冲突执行错误)

T1事务符合业务需求成功执行, T2干扰T1失败。

总结:

RR 级别作为 mysql 事务默认隔离级别, 是事务安全与性能的折中, 可能也符合二八定律(20%的事务存在幻读的可能, 80%的事务没有幻读的风险), 我们在正确认识幻读后, 便可以根据场景灵活的防止幻读的发生。

SERIALIZABLE 级别则是悲观的认为幻读时刻都会发生, 故会自动的隐式的对事务所需资源加排它锁, 其他事务访问此资源会被阻塞等待, 故事务是安全的, 但需要认真考虑性能。

InnoDB的行锁锁定的是索引, 而不是记录本身, 这一点也需要有清晰的认识, 故某索引相同的记录都会被加锁, 会造成索引竞争, 这就需要我们严格设计业务sql，尽可能的使用主键或唯一索引对记录加锁。

索引映射的记录如果存在, 加行锁, 如果不存在, 则会加 next-key lock / gap 锁 / 间隙锁, 故InnoDB可以实现事务对某记录的预先占用, 如果记录存在, 它就是本事务的,

如果记录不存在, 那它也将是本是无的, 只要本是无还在, 其他事务就别想占有它。