中秋前某日，夜黑风高，工程师小A正准备下班，突然被加急: 中秋要发月饼，做个系统让员工预订一下吧。一人只能预订两个，杏仁的一共50个，黄桃的有100个。

这是一家传统软件公司，公司CTO有一些技术偏执，坚信MySQL大法好，所以这家公司数据库只有 MySQL ！

毕竟小A是久经沙场的CRUD工程师，这种小业务对于小A而言就好像单手开法拉利一样轻松。

第一步，建表！

月饼余量表 mooncake

id	name	capacity
1	杏仁月饼	50
2	黄桃月饼	100

月饼领取表 people_mooncake_ref

id	user_id	mooncake_id	create_time
1	131	1	2019/9/10 22:22:00
2	231	1	2019/9/10 22:22:00

第二步，撸马！

小A犹如雷布斯附体，三下五除二，撸出了预订的代码。

func orderMoon(moonId int64){
    userId = passport.GetUserID()
    ordered = db.query("select count(1) from people_mooncake_ref where userId = ?",userId)
    Assert (ordered < 2, throw Exception("超出最大预订数量"))
    moon = db.query("select * from mooncake where id = ?", moonId)
    Assert (moon != nil, throw Exception("月饼不存在"))
    Assert (moon.capacity >= 1, throw Exception("月饼余量不足")
    db.execute("update mooncake set capacity = capacity-1 where moonId = ?",moonId)
    db.insert("insert into people_mooncake_ref values(nill,?,?)",userId,moonId) 
}

写完，小A看了看时间，才过去30秒。恩，一次debug，完美运行，美滋滋。正准备回家，突然想起来公司抠门的程度，暗暗心想：

要是月饼发多了，还不得被老板砍死。
要是月饼发少了，XXXX委员会还不得说我侵吞公司财物？
要是哪位员工预订不上，知道是谁写的，360还不得被疯狂diss。
要是哪位员工预订多了，再整个月饼事件，岂不是背大锅。

汗水顺着小A的额头滴下来，小A赶紧掏出垫显示器的《高性能Mysql》，仔细研读。恩，果然有问题。

1.月饼订不完的BUG

一旦第9行代码执行失败了( 网络错误 / 连接超时 / 机器掉电 ... )，月饼就永远被少定一个了。久经沙场的小A一眼看出了问题，两个写操作，没有事务。Easy，加！

加事务！

func orderMoon(moonId int64){
    userId = passport.GetUserID()
    ordered = db.query("select count(1) from people_mooncake_ref where userId = ?",userId)
    Assert (ordered < 2 , throw Exception("超出最大预订数量"))
    moon = db.query("select * from mooncake where id = ?", moonId)
    Assert (moon != nil, throw Exception("月饼不存在"))
    Assert (moon.capacity >= 1, throw Exception("月饼余量不足")
    db.begin()
    db.execute("update mooncake set capacity = capacity-1 where id = ?",moonId)
    db.insert("insert into people_mooncake_ref values(nill,?,?,now())",userId,moonId)
    db.commit()
}

小A愉快地回家了，中秋，卒。后来小B接手了小A的代码。作为一名更资深的CRUD工程师，一眼看出了问题。

2.月饼余量被订到负数的BUG

先查，再改，有前后依赖。在并行的情况下，会出现月饼被扣为负数的情况。这是一类典型的问题，先查询进行判断，然后去修改数据，由于是非原子操作，在并行的情况下会出现非预期结果。

加锁！

加锁无非，要么悲观锁，要么乐观锁。要么可重入，要么不可重入。要么公平，要么非公平。

悲观锁

func orderMoon(moonId int64){
    userId = passport.GetUserID()
    db.begin() // 上移事务开始时机，让X锁对整个事务生效
    ordered = db.query("select count(1) from people_mooncake_ref where userId = ?",userId)
    Assert (ordered < 2, throw Exception("超出最大预订数量"))
    moon = db.query("select * from mooncake where id = ? for update", moonId) // X锁，因为where是主键，锁行
    Assert (moon != nil, throw Exception("月饼不存在"))
    Assert (moon.capacity >= 1, throw Exception("月饼余量不足")
    db.execute("update mooncake set capacity = capacity-1 where id = ?",moonId)
    db.insert("insert into people_mooncake_ref values(nill,?,?,now())",userId,moonId)
    db.commit()
}

乐观锁

func orderMoon(moonId int64){
    userId = passport.GetUserID()
    ordered = db.query("select count(1) from people_mooncake_ref where userId = ?",userId)
    Assert (ordered < 2, throw Exception("超出最大预订数量"))
    db.begin()
    effectiveLine = db.execute("update mooncake set capacity = capacity-1 where id = ? and capacity > 1",moonId) // 根据生效行数，判断是否更新成功
    Assert (moon.capacity != 0, throw Exception("月饼余量不足")
    db.insert("insert into people_mooncake_ref values(nill,?,?,now())",userId,moonId)
    db.commit()
}

这种方式用RDBMS原生的MVCC来进行自旋，更优雅，高效地解决了这个问题。其它办法当然，这种问题还会有很多其它的解决方案，例如：

代码里面加同步: 由于部署了两台机器，卒
修改mysql事务隔离级别为serialized: 由于系统太慢，被员工XX圈吐槽，卒

3.员工预订超过两个月饼的BUG

小B惊奇地发现，虽然解决了超卖的问题，但是同一个员工可能会定超过两个月饼。小E就因为写了个脚本，一下预定了10个月饼，被公司XXX委员会开除。小B深感自责，决定彻底修复这个问题。先检查，再插入。同样会因为非原子操作，而导致检查操作失效。

锁表！

由于insert与update不同，没法把锁加在现有的数据行上面，要解决这个问题，只能寻求其它粒度的锁，比如：锁表。锁表有两种方法。

显式锁表: lock table 'people_mooncake_ref' Read/Write。
隐式锁表: 当for update查询的时候没用上索引，就会锁表。

func orderMoon(moonId int64){
    userId = passport.GetUserID()
    db.begin()
    db.execute("lock table `people_mooncake_ref` WRITE")
    ordered = db.query("select count(1) from people_mooncake_ref where userId = ?",userId)
    Assert (ordered == 0, throw Exception("请勿重复预订"))
    effectiveLine = db.execute("update mooncake set capacity = capacity-1 where id = ? and capacity > 1",moonId) // 根据生效行数，判断是否更新成功
    Assert (effectiveLine < 1, throw Exception("月饼余量不足")
    db.insert("insert into people_mooncake_ref values(nill,?,?,now())",userId,moonId)
    db.execute("unlock tables")
    db.commit()
}

特别要注意的是，当一个事务需要多处锁的时候，要先加上大粒度的锁。先加锁的，后释放。否则会发生死锁的情况。本以为故事到这里就圆满结束了。小B开开心心写完，结果第二天早会被DBA打进医院: 你居然故意加表锁！！！

唯一索引法！

小B可以通过添加唯一索引，通过记录的唯一性来限制员工预订超过两个月饼。修改预订表，增加orderNum字段。

/* 添加顺序号 */ ALTER TABLE `people_mooncake_ref` ADD `order_num` INT  NULL  DEFAULT NULL  COMMENT '顺序号'  AFTER `people_id`; 
/* 添加唯一索引 */ ALTER TABLE `people_mooncake_ref` ADD UNIQUE INDEX `ORDER_UNION` (`people_id`, `order_num`); 
people_mooncake_ref表:

func orderMoon(moonId int64){
    userId = passport.GetUserID()
    orderNum = db.query("select case when max(order_num) is null then 1 else max(order_num) from people_mooncake_ref where userId = ?",userId)
    Assert (orderNum < 2, throw Exception("超出最大可预订数量"))
    db.begin()
    effectiveLine = db.execute("update mooncake set capacity = capacity-1 where id = ? and capacity > 1",moonId) // 根据生效行数，判断是否更新成功
    Assert (effectiveLine < 1, throw Exception("月饼余量不足")
    effectiveLine = db.insert("insert IGNORE into people_mooncake_ref values(nill,?,?,?,now())",userId,orderNum+1,moonId) // 根据生效行数决定是否成功
    Assert (effectiveLine != 0, throw Exception("系统繁忙，请稍后再试"))
    db.commit()
}

其它方法当然，这种问题还会有很多其它的解决方案，例如：

代码里面加同步: 同上
修改mysql事务隔离级别为serialized: 同上
某些RDBMS可以通过insert where子句实现乐观锁，mysql不支持

尾章

小B长长地舒了一口气，再也不会因为系统有问题被各种diss了，把系统扔一边开始刷leetcode。但是毕竟还是太年轻，唯一不变的就是变化，公司来了一个新的CTO，CTO一看系统架构急了，全公司上下开始了一场轰轰烈烈技术革新的运动，堪比福报厂去IOE。

相关文档