V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
jonsmith
V2EX  ›  MySQL

库存扣减时,出现 MySQL 死锁是什么原因?

  •  
  •   jonsmith · 94 天前 · 3227 次点击
    这是一个创建于 94 天前的主题,其中的信息可能已经有所发展或是发生改变。

    在研究并发请求商品超售问题时,测试了几种 MySQL 的 sql 语句,其中一种情况产生死锁的问题很困惑,求大佬指点。

    SQL 语句

    UPDATE inventory SET stock = stock - Y WHERE product_id = X AND stock >= Y;
    
    • product_id 主键索引,此语句本意想避免超售,即 stock 库存不能为负。
    • 测试发现,当满足以下两种情况时,并发请求会死锁堵塞:
      • 此 sql 语句放在事务内执行。
      • stock 库存不足。
      • 以上两种情况只满足其一,不会造成死锁。

    对比 SQL

    // 事务中,先对库存加锁 for update ,避免其他事务修改库存
    SELECT stock FROM inventory WHERE product_id = X FOR UPDATE;
    // 再进行修改
    UPDATE inventory SET stock = stock - Y WHERE product_id = X;
    
    • 如果换成这个 sql 语句不会出现死锁的堵塞。

    请教下第一种 sql 是什么原因造成的死锁?

    第 1 条附言  ·  94 天前

    抱歉,我代码的错误,第1个SQL事务中忘记rollback。导致锁没释放,现在没问题了。

    那么这三种语句在事务中,实际性能有差异吗?

    // 第1
    UPDATE inventory SET stock = stock - Y WHERE product_id = X AND stock >= Y;
    
    // 第2
    SELECT stock FROM inventory WHERE product_id = X FOR UPDATE;
    // 一些业务逻辑...
    UPDATE inventory SET stock = stock - Y WHERE product_id = X;
    
    // 第3
    UPDATE inventory SET stock = (case when (stock >= Y) then (stock - Y) else stock end) WHERE product_id = X;
    
    
    18 条回复    2023-11-29 11:25:37 +08:00
    keymao
        1
    keymao  
       94 天前
    for update 会阻塞其他行级锁的请求,你上面那个 update 在库存不足的时候会执行失败,这样事务没执行完就锁着吧。

    你不开事务的话,for update 也是不生效的。 所以也没有问题。
    jonsmith
        2
    jonsmith  
    OP
       94 天前
    @keymao 两种 SQL 都在事务中执行的,当库存不足时,只有第 1 种会死锁。
    我怀疑是不是 where 条件里 `stock >= Y` 在事务中会扩大锁的范围,产生一些奇怪的死锁逻辑。
    jonsmith
        3
    jonsmith  
    OP
       94 天前
    又测试了第 3 种 SQL 语句:
    ```
    UPDATE inventory SET stock = (case when (stock >= Y) then (stock - Y) else stock end) WHERE product_id = X;
    ```
    这个在事务中也不会造成死锁。
    jonsmith
        4
    jonsmith  
    OP
       94 天前
    MySQL 是默认 RR 事务级别
    ezwd
        5
    ezwd  
       94 天前   ❤️ 1
    MySQL 的 InnoDB 存储引擎用行级锁来实现多版本并发控制( MVCC ),同时也支持 "SELECT ... FOR UPDATE" 这种显式锁定。在解决超售问题的场景中,死锁的出现可能是由于并发事务试图在同一时间内锁定同一行数据导致的。

    对于你的第一种 SQL 语句:

    ```
    UPDATE inventory SET stock = stock - Y WHERE product_id = X AND stock >= Y;
    ```
    这个语句在更新前会先检查 stock >= Y 条件,如果不满足这个条件,该行不会被锁定也不会被更新。当你有多个并发事务试图更新同一个 product_id ,并且 stock 库存不足时,这些事务可能会互相等待其他事务释放锁,这就可能导致死锁。

    对于你的第二种 SQL 语句:

    ```
    SELECT stock FROM inventory WHERE product_id = X FOR UPDATE;
    UPDATE inventory SET stock = stock - Y WHERE product_id = X;
    ```
    在这个情况下,你先显式地获取了一个行级锁,这会阻止其他事务在此期间修改这一行。然后,你再执行更新操作。由于你已经持有了行级锁,所以其他试图更新这一行的事务会被阻塞,直到你的事务完成,这样就避免了死锁。

    总的来说,第一种 SQL 语句在高并发的情况下可能会导致死锁,因为它试图在同一时间内更新同一行。而第二种 SQL 语句通过显式获取行级锁来避免这个问题。这就是为什么第二种 SQL 语句在你的测试中没有出现死锁。
    janwarlen
        6
    janwarlen  
       94 天前
    @ezwd #5 stock 库存不足就不满足 stock >= Y 的条件了啊,按照你说的不满足这个条件,该行不会被锁定也不会被更新,就不会死锁了...
    jonsmith
        7
    jonsmith  
    OP
       94 天前
    @janwarlen 对,代码 bug ,抱歉,已经 append 了
    asasjajsajsd
        8
    asasjajsajsd  
       94 天前
    用加行锁的,防止脏数据
    bololobo
        9
    bololobo  
       94 天前
    这个案例体现了 innoDB 锁体系的一个特点 就是两阶段锁:在执行语句时加的锁,要到提交事务或者回滚事务的时候才释放
    Pythoner666666
        10
    Pythoner666666  
       94 天前
    我们线上用的是第二种,就是为了避免死锁的情况
    Oilybear
        11
    Oilybear  
       94 天前
    还有其他索引吗比如 stock 之类的
    liprais
        12
    liprais  
       94 天前
    UPDATE inventory SET stock = stock - Y WHERE product_id = X AND stock >= Y
    这种除非你还有个索引在 stock 上而且是倒序的而且还有另外一个 sql 去更新这个索引才会出现死锁
    ZZ74
        13
    ZZ74  
       94 天前
    1 3 无差别...
    2 会略微低一些 主要是并发性上。
    另外 忘记 rollback.....话说还在手工控制事务么........
    xiang0818
        14
    xiang0818  
       93 天前
    用了分布式事务吧
    shenjinpeng
        15
    shenjinpeng  
       93 天前
    可以换别的实现 , 比如库存丢 redis , 然后用 redis 实现锁; 总感觉 MySQL 并发跟不上, 秒杀/营销 场景 容易超卖 ...
    wu00
        16
    wu00  
       93 天前
    单看这 3 个语句:
    1-乐观锁,stock >= Y 锁粒度最小化,性能最好,体验方面也比时间戳的 version 强得多;
    2-悲观锁,碰到其它线程对这条数据进行 for update 时会进行排队,性能最低,但是优点是当你处理"一些业务逻辑"时不用担心当前事务并行执行;
    3-没有存在的必要,似乎跟 1 没什么区别。
    leorealman
        17
    leorealman  
       93 天前
    for update 简单说就是一致性锁定读,因为由于 MVCC 的存在每个事务都可以获取一个数据版本且可以修改它,所以有可能修改到和其他事务正在处理相同的某一行数据,不知道我解释清楚了没?
    mmdsun
        18
    mmdsun  
       93 天前
    @Pythoner666666 第一种应该不会死锁,楼主笔误写错了。


    @shenjinpeng 性能差了点,但用 update + where 条件 不会超卖的。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5333 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 39ms · UTC 07:46 · PVG 15:46 · LAX 23:46 · JFK 02:46
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.