CODEWITHSUNDEEP
mysqlstored-proceduresfreeze
mihaidp
mihaidp

Reputation: 310

Stored procedure hanging

A stored procedure hangs from time to time. Any advices?

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

When it hangs it does it on: | 23970842 | username | sqlhost:54264 | database | Query | 65 | Sending data | SELECT IFNULL(id,0) INTO bookId FROM products WHERE isbn= NAME_CONST('p_isbn',_utf8'973-679-50 | 0.000 |

| 1133136 | username | sqlhost:52466 | database _emindb | Query | 18694 | Sending data | SELECT IFNULL(id,0) INTO bookId FROM products WHERE isbn= NAME_CONST('p_isbn',_utf8'606-92266- | 0.000 |

Upvotes: 2

Views: 2130

Answers (1)

udog
udog

Reputation: 1536

First, I'd like to mention the Percona toolkit, it's great for debugging deadlocks and hung transactions. Second, I would guess that at the time of the hang, there are multiple threads executing this same procedure. What we need to know is, which locks are being acquired at the time of the hang. MySQL command SHOW INNODB STATUS gives you this information in detail. At the next 'hang', run this command.

I almost forgot to mention the tool innotop, which is similar, but better: https://github.com/innotop/innotop

Next, I am assuming you are the InnoDB engine. The default transaction isolation level of REPEATABLE READ may be too high in this situation because of range locking, you may consider trying READ COMMITTED for the body of the procedure (SET to READ COMMITTED at the beginning and back to REPEATABLE READ at the end).

Finally, perhaps most importantly, notice that your procedure performs SELECTs and UPDATEs (in mixed order) on the same table using perhaps the same p_isbn value. Imagine if this procedure runs concurrently -- it is a perfect deadlock set up.

Upvotes: 2

Related Questions