mysql - 存储过程挂了

Question

存储过程有时会挂起。有什么建议吗？

BEGIN
DECLARE bookId int;

SELECT IFNULL(id,0) INTO bookId FROM products
    WHERE 
        isbn=p_isbn
    and stoc>0 
    and status='vizibil' 
    and pret_ron=(SELECT MAX(pret_ron) FROM products
    WHERE isbn=p_isbn
                  and stoc>0 
                  and status='vizibil')
    ORDER BY stoc DESC

LIMIT 0,1;

IF bookId>0 THEN
UPDATE products SET afisat='nu' WHERE isbn=p_isbn;
UPDATE products SET afisat='da' WHERE id=bookId;
SELECT bookId INTO obookId;
ELSE

SELECT id INTO bookId FROM products
    WHERE 
        isbn=p_isbn 
    and stoc=0 
    and status='vizibil' 
    and pret_ron=(SELECT MAX(pret_ron) FROM products
                    WHERE isbn=p_isbn 
                      and stoc=0 
                      and status='vizibil')
LIMIT 0,1;
UPDATE products SET afisat='nu' WHERE isbn=p_isbn;
UPDATE products SET afisat='da' WHERE id=bookId;
SELECT bookId INTO obookId;
END IF;

END

当它挂起时，它会在:| 23970842 |用户名 | sqlhost:54264 |数据库 |查询 | 65 |发送数据 |从产品中选择 IFNULL(id,0) INTO bookId 在哪里 isbn= NAME_CONST('p_isbn',_utf8'973-679-50 | 0.000 |

| 1133136 |用户名 | sqlhost:52466 |数据库_emindb |查询 | 18694 |发送数据 |从产品中选择 IFNULL(id,0) INTO bookId 在哪里 isbn= NAME_CONST('p_isbn',_utf8'606-92266- | 0.000 |

Answer 1

首先，我想提一下 Percona toolkit ，它非常适合调试死锁和挂起的事务。其次，我猜想在挂起时，有多个线程在执行同一个过程。我们需要知道的是，挂起时正在获取哪些锁。 MySQL 命令 SHOW INNODB STATUS 为您提供了详细信息。在下一个“挂起”时，运行此命令。

我差点忘了提到工具 innotop，它很相似，但更好:https://github.com/innotop/innotop

接下来，我假设您是 InnoDB 引擎。由于范围锁定，REPEATABLE READ 的默认事务隔离级别在这种情况下可能太高，您可以考虑对过程主体尝试 READ COMMITTED(在开始时设置为 READ COMMITTED，并在结束时返回到 REPEATABLE READ)。

最后，也许最重要的是，请注意您的过程可能使用相同的 p_isbn 值对同一个表执行 SELECT 和 UPDATE(以混合顺序)。想象一下，如果这个过程并发运行——这是一个完美的死锁设置。