mysql: Deadlock inside stored procedure

Question

I have stored procedure like this:

DECLARE err_code CHAR(5) DEFAULT '00000';
DECLARE msg TEXT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
    err_code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
END;

START TRANSACTION;
INSERT INTO tableA .....;
INSERT INTO tableB .....;

SELECT updateJournal(id) INTO x;

IF err_code != '00000' THEN
   ROLLBACK;
   INSERT INTO log (msg) VALUES (msg);
ELSE
   COMMIT;
END IF;

CREATE FUNCTION updateJournal(orderId INT) return int 
BEGIN INSERT INTO tableC ....; 
UPDATE tabled ....;

RETURN 1; END

sometimes I get msg error "Deadlock found when trying to get lock; try restarting transaction" the stored procedure is rollback, but function updateJournal still work (tableC inserted, tableD updated), can anyone explain why stored procedure is rollback with deadlock error, but function inside stored procedure still committed.

can anyone explain why my stored procedure get deadlock error?

Answer 1

It is rather hard to tell what is causing the deadlock based only on the information you provided. What you can try is, while the issue is happening, collect some information. There are several places in performance_schema that may contain useful info: The data_locks Table, The data_lock_waits Table, The metadata_locks Table

Make sure that the data is collected, it may not always been collected by default. In addition to that, as it has already been mentioned, you should capture an output of

SHOW ENGINE INNODB STATUS\G

that will also contain some useful information about locks that are happening.

You can get the data altogether using pt-stalk, a tool from Percona Toolkit that is intended to run an in-depth data collection presenting the state of the database. You can download it from:

wget https://percona.com/get/pt-stalk

You can trigger the execution manually:

./pt-stalk --no-stalk --iterations=1 --user=root --ask-pass

The data you want to review is in files:

*-ps-locks-transactions
*-transactions
*-lock-waits
*-innodbstatus1
*-innodbstatus2

Not always all the files will be created, it depends on the situation in the database. By default pt-stalk creates its data in /var/lib/pt-stalk.