mysql 死锁的问题,求优化意见

[letitbesqzr]

遇到的问题

假设拿医院系统的药品部分举例, 药品表结构大概如下:

现在有多个地方可能会去操作药品的数量:

1. 护士生成请领单 (减存库数量,加冻结数量),可能会涉及到一堆表,那么这些操作都会在一个事务里,在操作存库的时候会把操作的行自动锁定起来,等待事务结束才会释放
2. 手工划价药品 (直接减库存数量) 同上,会操作很多表的数据,一样会锁操作的行
3. 药房发药 (减冻结数量) 同上,会操作很多表的数据,修改请领单状态等等,一样会锁操作的行
4. ......

因为在系统里可能某些药的使用频率是非常高的,比如一些生理盐水、葡萄糖之类的。那么就经常会遇到，护士在生成请领单事务还没完成，药房正在发的药中也含有这些药，这样互相等待直接就被 mysql 的死锁检测机制检测到。

想请问大家遇到这种问题一般会采用什么样的方案来解决？

自己想到的方法以及问题

1. 大事务拆各个小事务,比如药房发药,新事务->首先修改请领单状态->提交。新事务->再去减存库->提交。但是这样如果在减存库的地方异常了,不就不会回滚到 请领单状态
2. 使用同步,我们首先尝试在生成请领单的方法上加上同步,这样多个护士在同时生成请领单的时候的确是不会遇到问题了,但是生成请领单又可能和发药死锁。如果加到减库存的方法，那似乎没什么用？就算减存库同步执行了，但是上层事务还没跑完 也会遇到这问题吧？
3. 使用队列，因为我们现在多项目同时运行,并且都可能去操作存库,所以考虑过使用队列,把所有操作存库都丢到队列去单线程执行。但是改动速度有点大。

不知大家是否还有其他比较好的解决方案

[henry19890701]

保证加锁顺序就可以了，代码应该不怎么需要改

[letitbesqzr]

@henry19890701 那其实也就只有让他同步,单线程的去减存库?

[Mirana]

加锁失败回滚 然后重试

[wind3110991]

感觉这个最主要的问题并不是事务或者是锁，而是你的流程问题：
为什么操作失败就要 回滚到 请领单状态？
建议如果并发量大容易锁，流程中可以穿插多个原子事务，失败回滚到一个中间状态，而不是后台一个接口大包大揽。
事务不是万能的，要符合你的当前使用场景才行

[billlee]

@letitbesqzr #2 这个肯定是要同步的。别说是数据库，就算是内存里的变量，多线程操作也要加锁进临界区啊。

[cjyang1128]

一般死锁问题都是通过保证加锁顺序实现的。除了楼主提的几个方案之外，可以把某些数据存储在 redis 中，因为 redis 是单线程的，所以不存在竞争问题。因为你本质上是 id=>数量的一个映射，所以也可以考虑一下。

[letitbesqzr]

@wind3110991 就比如发药
1. 将请领单状态设置为已发药
2. 各种计费改状态
3. 减少存库
4. 写存库流水
5. 记录日志

那么某一步失败肯定需要回滚到第一个状态，实际情况一个事务里还会做更多的操作，业务非常复杂

[letitbesqzr]

@wind3110991 意思就是，其实大事务拆成一个个短事务是比较常见的做法？

[3dwelcome]

如果是我的话，就用单线程队列。拆分事务只能让逻辑变复杂。代码应该多遵循 KISS 原则，能简单处理的问题，别复杂化。

[ebony0319]

如果是我我会采取队列方式。谁先就应该给谁，不应该抢资源。

[letitbesqzr]

@ebony0319
@3dwelcome
如果放队列的话 也就相当于 操作存库的每条 sql 都不会在事务里了吧？那其实没办法失败后回滚了？

[ryd994]

一种药一个事务
A 药开不出和 B 药没有联系，不需要锁一起

[reus]

手工上锁 + 事务

[msg7086]

上锁超时跳过，回头来重试呗。
万一某个药没货了，病人别的药也不让吃了么……

[jianzhiyao020]

如果，
并发量不是特别大的话，
可以选择序列化事务隔离级别，
绝对不会死锁。
但是伴随来说，
速度会相应降低，
但是应该总比死锁好。

[letitbesqzr]

@jianzhiyao020 试过，但是预算了一下，病人得排队到马路上。

[jianzhiyao020]

@letitbesqzr 那就要概念 hack 了，
例如葡萄糖放在一个地方，
大家都去拿，那是否是会增加堵塞的概率，
可以护士那里放一点，
药房那里放一点，
是不是就不会那么堵塞了，
好了，
我说那么多，
其实就是将葡萄糖分开几个记录存取。

[wind3110991]

@letitbesqzr 如你说的，回滚没有必要从滚到 1 之前啊
比如你在 5 出错了，滚到 4 不就好了，记录下当前状态，在队列等待就好了啊

[ebony0319]

最近又查了一些资料，好像都不满意，想问问你这个问题你们目前的思路么。

[letitbesqzr]

@ebony0319
1. 减短事务
2. 使用了 mq 队列进行增删存库
目前采用上面两种方案已经能够满足目前的请求量了，下面一个预备的方案
3. 存库的数量都丢到 redis 中 程序只是操作 redis 里面的存库 定期同步到数据库