How to call a program recursively when deadlock occurs?

Question

My job "stored procedure" runs overnight. Every now and then I get stuck. I want to write a code that if a deadlock is found, it will execute the same process up to 3 times after 15 minutes.

Suppose it is 10 AM. Then run it at 10am and if a deadlock is found, call the job again at 10:15am, then at 10:30am "if deadlock", then at 10:45am "if deadlock". If the last run deadlocks, terminate the job completely.

This is what I did.

DECLARE totalTrys INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
    @err_num = MYSQL_ERRNO;

    if(@err_num = 1213 AND totalTrys < 4) THEN
        SET totalTrys = totalTrys 1;
        SELECT SLEEP(900);
        CALL guardi.prod_update_guardi_accounts();
    END IF;

END;

Is this an efficient approach? Is there a better way than eliminating the deadlock?

Answer 1

I would keep everything in the process: then all the code related to this behavior is in one place. Also: wouldn't the deadlock follow the execution of your CALL statement instead of continuing? If we proceed, then the problem may have been solved and we won't be waiting for anything. Additionally, transactions following a deadlock will only be executed after the deadlock is resolved.

My approach to your code is:

CREATE PROCEDURE manageAccounts()
BEGIN
DECLARE totalTrys INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS CONDITION 1
@err_num = MYSQL_ERRNO;
CALL guardi.prod_update_guardi_accounts();
WHILE(@err_num = 1213 AND totalTrys < 4) DO
    SET totalTrys = totalTrys + 1;
    SELECT SLEEP(900);
    CALL guardi.prod_update_guardi_accounts();
END WHILE;
END;

You'll have to test this: untested, so typos are not impossible.