1. 事情起因

我们开发的一个应用，在使用按键精灵模拟用户操作的时候，发现库存操作会存在主键重复插入的情况，实际上该条商品是有库存的，很奇怪。

2. 我们第一反应想是不是应用层问题，结果不是

首先我们想到的是MyBatis用法有问题，检查了下MyBatis发现感觉没有问题，但是还是不确定
线上我们打开SQL日志，发现SQL是完全正确的，那就更加奇怪了
我们想是不是MySQL的问题，内心感觉是不可能的，但是SQL和系统日志表现来看应用层没有问题，不得不怀疑是MySQL的问题，最后发现真是MySQL的问题，其实也不是MySQL的问题，是我们使用的问题，一个索引引起的问题，非常隐蔽

3. 那是不是MySQL的问题，果然是

3.1 在了解这个问题之前首先我们得知道几个知识点

MySQL 在RC隔离级别下的锁是怎么样的
select … for Update的用法
MySQL索引
一条 select 语句 where 条件应用索引的情况下，MySQL 是如何执行的

3.2 先来重现案发现场

CREATE TABLE `inventory` (
  `inventory_item_id` bigint(20) unsigned NOT NULL,
  `location_id` bigint(20) unsigned NOT NULL ,
  `merchant_id` bigint(20) unsigned NOT NULL ,
  `available` decimal(16,4) NOT NULL ,
  `created_at` datetime NOT NULL ,
  `update_at` datetime NOT NULL ,
  `price` decimal(16,8) DEFAULT NULL ,
  `assist_price` decimal(16,8) DEFAULT NULL ,
  `money` decimal(16,4) DEFAULT NULL ,
  `assist_available` decimal(16,4) DEFAULT NULL,
  PRIMARY KEY (`inventory_item_id`,`location_id`),
  KEY `idx_merchant_id_location_id` (`merchant_id`,`location_id`),
  KEY `idx_merchant_id_inventory_item_id` (`merchant_id`,`inventory_item_id`),
  KEY `idx_merchant_id_available` (`merchant_id`,`available`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO mars_core_inventory_level (inventory_item_id, location_id, merchant_id, available, created_at, update_at, price, assist_price, money, assist_available) VALUES (661552509022453761, 661551935363301376, 661551935182946304, -346.0000, '2022-07-29 10:06:09', '2022-07-29 13:44:50', 0.00000000, 0.00000000, 0.0000, 0.0000);
INSERT INTO mars_core_inventory_level (inventory_item_id, location_id, merchant_id, available, created_at, update_at, price, assist_price, money, assist_available) VALUES (672339754159583233, 661551935363301376, 661551935182946304, -543.0000, '2022-07-29 10:06:09', '2022-07-29 13:44:50', 0.00000000, 0.00000000, 0.0000, 0.0000);

3.3 结合知识点分析

在 MySQL 中，当你遇到使用 FOR UPDATE 锁定行后，随后的 UPDATE 语句修改了索引值，并且发现查询结果比预期少了一条数据的情况，这主要涉及到两个核心概念：事务隔离级别和索引维护。这些因素共同影响了数据的可见性和一致性。

事务隔离级别（Transaction Isolation Levels） MySQL 支持几种事务隔离级别，其中 Read Committed 是其中之一。在 Read Committed 隔离级别下，事务的行为如下：
- 非锁定读：默认情况下，SELECT 语句执行非锁定读，只能看到在该语句执行时已经提交的数据。
- 锁定读：使用 SELECT … FOR UPDATE 时，会锁定涉及的行直到事务结束，防止其他事务修改这些行。

在 Read Committed 隔离级别下，一个事务中的修改（即使涉及索引值的改变）在提交之前对其他事务是不可见的。这意味着如果在一个事务中修改了索引值并提交，其他并发执行的事务在这个事务提交前是看不到这些修改的。

索引维护（Index Maintenance）当 UPDATE 语句修改了表中的索引列时，MySQL 需要更新相关的索引。这涉及到：
- 删除旧索引条目：从索引中移除旧值。
- 添加新索引条目：在索引中插入新值。

这种索引更新是即时发生的，一旦事务提交，新的索引值就会生效，旧的索引值就会被删除。这可能导致在事务提交后，基于旧索引值的查询无法找到这些行，因为它们已经不再匹配旧的索引条件。

影响数据可见性的原理

在使用 FOR UPDATE 的情况下，如果事务 A 在修改了索引值后提交，那么这些更改在事务 A 提交之前对其他事务是不可见的。如果另一个事务 B 在事务 A 提交之前已经开始执行并使用了旧的索引值进行查询，它可能不会看到事务 A 修改的行，因为这时候 B 的视图（view）还是基于旧的索引值。

3.4 现在分析上述情况

准备环境，文档中的环境为Server version: 8.0.29 MySQL Community Server - GPL
上面的select语句应用的索引是idx_merchant_id_available，我们画张图分析见从图中可以看出因为update语句导致select for update 锁范围内的数据产生了变化，大致本应该条件匹配的数据未返回。
我们再次做个实验验证1的想法，我们将sessionA中两句update的available的值都超出sessionB中的锁范围，预期应该是返回一个空数组第二张图结果来看符合猜想，分析思路基本正确。

3.5 解决问题

解决问题就非常简单，直接删除idx_merchant_id_available即可
升级隔离级别到RR，但是发生这种情况SessionB会进行死锁检验，并抛错ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

4. 总结

索引字段选择要谨慎，原则上应该选择创建好基本不会变的字段作为索引，经常性变的字段作为索引基本没用，而且因为经常性变导致索引重建，会产生额外的性能问题
这种现象的根本原因是事务隔离级别和索引维护的交互作用，导致在高并发环境下可能出现数据可见性问题。理解这些原理有助于设计更健壮的数据库交互逻辑，尤其是在涉及频繁修改索引列的应用中。在设计数据库操作时，考虑到这些因素，可以选择适当的隔离级别，或者重新设计数据访问逻辑，以确保数据的一致性和可靠性。

公众号名称：怪味Coding

微信扫码关注或搜索公众号名称

记一次MySQL数据库在RC隔离级别下，数据查询会变少问题