库存只剩 1 件，但却有 10 个人买到？

网路商城特卖会常常会推出特定商品限量 1 组的抢购活动，在抢购过程除了会产生高并发的问题外，同时也需要考虑到「商品会不会超卖」，在现实生活中听起来很不可思议；但在网路的世界里，如果SQL逻辑没有设计好，真的可能发生明明只限 1 组却卖了 10 组商品的超卖事件。

大纲

1. 关联式资料库要如何设计避免超卖？

   • 1.1 面试官为什麽会问？
   • 1.2 面试官想从答案确认什麽？
   • 1.3 笔者提供的简答

2. 回答问题所需具备的知识

   • 2.1 什麽是悲观锁（Pessimistic Lock）？
   • 2.2 什麽是乐观锁（Optimistic Lock）？
   • 2.3 在 MySQL DB 实作悲观锁
   • 2.4 在 MySQL DB 实作乐观锁

3. 衍伸问题

   • 3.1 悲观锁、乐观锁优缺点

1. 关联式资料库要如何设计避免超卖？

1.1 面试官为什麽会问？

这算是资料库的常见面试题，对金融、电商相关产业来说都快要变成必考题了。

通常问完这题後会紧接着询问：「如果是用 noSQL 有什麽处理方案？」这部分会在明天的文章跟大家讲解。

1.2 面试官想从答案确认什麽？

• 是否有资料库 Transaction 的概念
• 了解悲观锁（Pessimistic Lock）、乐观锁（Optimistic Lock）的概念
• 知道悲观锁（Pessimistic Lock）、乐观锁（Optimistic Lock）优缺点、使用情境

1.3 笔者提供的简答

我会依照不同的情境来做设计，如果写入需求较高，我会选择使用悲观锁（Pessimistic Lock）；因为它是直接使用资料库 Transaction 的机制，保证了资料的稳定性。

如果读取需求较高，我会选择使用乐观锁（Optimistic Lock）；因为它可以承受较高的吞吐量，不过它锁的特性会导致购买顺序未必是先到先得，只是保证不会超卖。

2. 回答问题所需具备的知识

2.1 什麽是悲观锁（Pessimistic Lock）？

就跟字面上的意思一样，非常的悲观；它认为 Table 里面的 data 非常不安全，无时无刻都在变动，当一个 SQL command（可以理解为抢购的用户）获得悲观锁後，其他的 SQL command 无法对这个 data 进行修改，直到悲观锁被释放後才能执行。

资料表中用 Synchronized 实现的锁均为悲观锁（ex：行锁，表锁，读锁，写锁）。

2.2 什麽是乐观锁（Optimistic Lock）？

相比悲观锁，它认为 Table 里面的 data 变动频率不会太频繁，因此他会允许多个 SQL command 来操作 Table；但乐观并不代表不负责，通常会在 Table 中增加一个 version 的栏位来做更新的确认。因此当 SQL command 想要变更栏位 data 时，会把之前取出的 version 跟现在的 version 做对比，如果相同就代表这段期间没人修改可以执行；如果不同这次的操作就不会生效。

2.3 在 MySQL DB 实作悲观锁

情境描述：
假设 A 跟 B 都想要买 iphone 手机，但商城的 iphone 库存只剩下一只，在不加锁的状况下同时下单会导致库存变成负号；请问如何用悲观锁解决这个问题呢？

只要安装好 MySQL，跟着步骤操作即可模拟 A、B 客户抢购。

• STEP 1：建立模拟资料
  • 先用 SQL command 建立一个简单的 Table 并新增 data。

    CREATE TABLE `your_DB`.`items` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NOT NULL,
    `num` INT NOT NULL,
    PRIMARY KEY (`id`));
    INSERT INTO `your_DB`.`items` (`name`,`num`)
    VALUES ("iphone",1);
    

  • 确认资料是否建立成功。

    SELECT * FROM `your_DB`.`items`;
    

    
• STEP 2：开一个视窗模拟 A 客户抢购行为
  • Transaction 的开始要用 begin;，不然 MySQL 会自动提交。
    
  • 在 A 购买前先将 id=1 的 data 加上悲观锁，此时这行 data 只有 A 可以操作。

    select num from `your_DB`.`items` where id = 1 for update;
    

    
• STEP 3：开另一个视窗模拟 B 客户抢购行为
  • 一样将 id=1 的 data 加上悲观锁，下完指令後便会进入等待的模式，要等 A 执行 commit 完成交易或是指令逾时才能继续。
    
• STEP 4：回到 A 客户的视窗完成交易
  • 让 A 执行购买的动作，将 data 的 num 减一。

    update `your_DB`.`items` set num = num -1 where id = 1;
    

  • 然後检视修改後的 data 是否符合预期。

    select num from `your_DB`.`items` where id = 1;
    

  • 最後下 commit; 结束这次交易。
    
• STEP 5：当 A 完成交易後回到 B 的视窗
  你会发现因为 A 释放了悲观锁，所以 B 结束等待并且获得锁；但 data 的 num 已经变成 0 了，只能放弃购买。
  

  7.89 秒是等待获得锁的时间。

2.4 在 MySQL DB 实作乐观锁

情境描述：
假设 A 跟 B 都想要买 iphone 手机，但商城的 iphone 库存只剩下一只，在不加锁的状况下同时下单会导致库存变成负号；请问如何用乐观锁解决这个问题呢？

观察两者实作上的逻辑差异。

• STEP 1：建立模拟资料

  • 先用 SQL command 建立一个简单的 Table 并插入 data，比起悲观锁的 Table，我们多了一个 version 的栏位。

    CREATE TABLE `your_DB`.`happy_items` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(45) NOT NULL,
    `num` INT NOT NULL,
    `version` INT NOT NULL,
    PRIMARY KEY (`id`));
    INSERT INTO `your_DB`.`happy_items` (`name`,`num`,`version`)
    VALUES ("iphone",1,0);
    

  • 确认资料是否建立成功。

    SELECT * FROM `your_DB`.`happy_items`;
    

    

• STEP 2：规划实作逻辑思路

  • A 跟 B 客户在购买前会先将 id=1 的 data 找出来。
  • A 先购买，此时会以 id=1 和 version=0 作为 where 条件来更新 data，更新後 num -1、version +1 变成 id=1、num=0、version=1。
  • B 购买时会因为同样是用 id=1 和 version=0 为 where 条件，所以会因为找不到 data 而无法更新。

• STEP 3：模拟抢购情境，A 跟 B 客户购买前先查询库存

  • A 及 B 执行查询的 SQL command 会取得相同的 iphone 库存资讯。

    SELECT num,version FROM `your_DB`.`happy_items` where id = 1;
    

    

• STEP 4：A 先执行购买的 SQL command

  • A 先购买 iphone 并更新 data。

    update `your_DB`.`happy_items` set num = num -1 , version = version + 1 where id = 1 and version = 0;
    

  • 检视修改後的 data 是否符合预期。

    SELECT num,version FROM `your_DB`.`happy_items` where id = 1;
    

    

• STEP 5：B 再执行购买的 SQL command

  • 此时 B 的购买就会失败，在执行购买的 SQL command 後你会发现没有 Row 被更新，因为库存资讯已更新，但 SQL command 还是旧的。
    

3. 衍伸问题

3.1 悲观锁、乐观锁优缺点

考点：对於关联式资料库「锁」的认知程度

• 悲观锁（Pessimistic Lock）
  • 优点
    使用资料库 Transaction 的机制来强迫执行的顺序。
  • 缺点
    一但加入 Transaction 的机制，会导致其他的 SQL command 针对这个 data 除了查询功能外全部卡死，如果这段 Transaction 执行时间较长，会给使用者不良的体验并造成系统吞吐量下降。
• 乐观锁（Optimistic Lock）
  • 优点
    因为没有在资料库加锁，所以 SQL command 都会对 data 进行操作，只有在更新 data 时才会做验证；这样做就能避免因使用悲观锁而导致吞吐量下降的问题。
  • 缺点
    因为乐观锁是我们人为实现的，所以如果换一个业务场景可能会不适用，甚至可能因为其他的 SQL command 导致错误。

感谢大家的阅读，如果喜欢我的文章可以订阅接收通知；如果有帮助到你，按Like可以让我更有写文的动力，我们明天见～

参考资源

1. 理解资料库『悲观锁』和『乐观锁』的观念(笔者部落格)

我在 Medium 平台 也分享了许多技术文章
❝ 主题涵盖「MIS & DEVOPS、资料库、前端、後端、MICROSFT 365、GOOGLE 云端应用、自我修炼」希望可以帮助遇到相同问题、想自我成长的人。❞