C#操作MySQL出现死锁、Commit未释放锁的问题排查求助

核心问题点 #

• SQL语法错误：你写的查询语句存在两处低级笔误，p..barcode_id多了一个点符，WHERE条件里的players.in_use和表别名p不匹配，会导致查询异常，先修复这部分问题。
• 无索引导致锁范围过大：你用的InnoDB引擎在Repeatable Read隔离级别下，FOR UPDATE加锁的范围完全依赖索引命中情况。如果你的p_barcode表没有针对company_id、barcode、in_use建立联合索引，查询会扫全表/大范围间隙，加的是表级锁或者大量间隙锁，多线程并发时必然会出现锁冲突。
• 无排序的LIMIT 1导致取行顺序混乱：SELECT ... LIMIT 1 FOR UPDATE没有加ORDER BY主键的逻辑，多个线程并发查询时，InnoDB返回的行顺序不固定，很容易出现「线程A持有id=1的行锁等待id=2的锁、线程B持有id=2的行锁等待id=1的锁」的循环等待，这是最典型的死锁触发场景。
• 事务块内加Sleep导致锁/资源释放延迟：你的Thread.Sleep(50)写在了using (var transaction)块内部，事务提交后还要占着事务对象50ms才会触发Dispose。你用的.NET Framework版旧MySql.Data驱动存在事务上下文清理的已知bug，事务对象未Dispose前，长连接上的锁状态不会同步更新，导致前一个事务的锁释放延迟，下一次开事务时直接触发冲突。
• 资源清理不严谨：DataReader没有用using块包裹，异常场景下可能出现未关闭的情况，导致连接上下文挂起，事务无法正常结束。

修复方案 #

1. 先修复SQL笔误，查询语句调整为：

SELECT p.barcode, p.barcode_id 
FROM p_barcode p 
WHERE p.company_id = 1 
AND SUBSTRING(p.barcode,1,2) = 'OK' 
AND p.in_use = 0 
ORDER BY p.barcode_id
LIMIT 1 
FOR UPDATE;

2. 给p_barcode表加联合索引，确保查询只锁命中的单行：

CREATE INDEX idx_company_barcode_inuse ON p_barcode(company_id, barcode, in_use);

3. 将Thread.Sleep(50)移到using (var transaction)块外部，事务提交后立刻释放事务对象。
4. 更换MySql驱动：.NET Framework版的旧MySql.Data驱动存在大量事务、连接管理的bug，换成兼容.NET Framework的新版MySqlConnector驱动，就能解决你说的「主动Dispose比using靠谱」的问题，本质是驱动的Dispose逻辑已经修复。
5. 若要彻底避免长连接上下文脏读问题，可调整为每次事务开启前新建连接、事务结束后关闭连接，.NET的连接池会自动复用连接，性能损耗极低，还能避免连接上下文残留的问题。
6. 后续排查死锁可直接在MySQL中执行show engine innodb status，查看最近的死锁日志，可直接定位到冲突的SQL和锁类型。

内容的提问来源于stack exchange，提问作者Philip Johnson