MySQL MVCC 设计缺陷

更新时间：2022-08-13 13:58:15

虽然SQL-92规定了四种隔离级别，但是在引入MVCC后，RC/RR都是snapshot isolation下的变体。至于对隔离性和一致性的吐槽，引用沈洵的话：“快照读以更低的代价实现了更高的并发度，却不得不委身在原有事务框架内。其实ACID也只是一个标准,并非真理。”

既然是snapshot isolation，MySQL有什么问题呢，直接上案例：

准备工作：

create table mvcc(x int auto_increment primary key,y int default 1);

insert into mvcc(y)  select 1 from mvcc; -- many times

Query OK, 2097152 rows affected (13.24 sec)

Records: 2097152  Duplicates: 0  Warnings: 0

RC/RR && autocommit = 0

T Session1 Session2

select * from mvcc where y>1;

Empty set

update mvcc set y=3 where y>1;

updating

update mvcc set y=2 

where x=3000000;

select * from mvcc where y>1;

+---------+------+
| x | y |

+---------+------+
| 3000000 | 2 |

+---------+------+
commit

Query OK, 0 rows affected

Query OK, 1 row affected (1.76 sec)

Rows matched: 1 Changed: 1

select * from mvcc where y>1;

+---------+------+
| x | y |

+---------+------+
| 3000000 | 3 |

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

按照snapshot isolation来说session2的trx_id 大于session1的trx_id，从而session2的修改对session1应该是不可见的：即session1应该更新0行，但是MySQL在这里却对y=2进行了更新。

MySQL官方在5.5+的文档也针对该问题做出了Note：

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DMLstatements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction.

根本原因在于MySQL在update/delete/insert/select for update/select lock in share mode时进行的是current read(select_lock_type != LOCK_NONE)而非consistent read。而Oracle解决这个问题的方式是对比current read和consistent read来决定是否进行query restart；对于该案例，session1在T2时更新到x=3000000时发现y发生了变化从而回滚了这个变更并进入query restart，最终结果就是在T3时更新了0行。

因此，如果业务依赖事务但是又不清楚具体数据库的实现细节，很容易就掉到坑里了；比如这个案例，就产生了"错误的"更新。

下面针对MySQL RR 给出一个非谓词更新的一个案例：如果不理解这块事务，在T3时想当然就会觉得结果应该是6000，可能就犯错了。

RR && autocommit=0

T Session1 Session2

begin;

select * from t where x = 88;

+----+------+
| x | y |

+----+------+
| 88 | 3000 |

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

1 row in set (0.00 sec)

update t1 set b = 8000

where x = 88;
Query OK, 1 row affected

Rows matched: 1 Changed: 1
commit

select * from t1 where a = 88;

+----+------+
| x | y |

+----+------+
| 88 | 3000 |

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

1 row in set (0.00 sec)

update t1 set y = y + 3000 where x = 88;
Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

select * from t1 where x = 88;

+----+-------+
| x | y |

+----+-------+
| 88 | 11000 |

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

1 row in set (0.00 sec)

本文转自MIKE老毕 51CTO博客，原文链接：http://blog.51cto.com/boylook/1689256，如需转载请自行联系原作者

上一篇 : ：如何在 Ubuntu Linux 16.04上安装开源的 Discourse 论坛下一篇 : DNN Test

MySQL MVCC 设计缺陷

相关阅读

推荐文章