mysql - How to recursively call a procedure when there is a deadlock? -

i have jobs "stored procedures" runs on night. every , run deadlock. want write code execute same procedure after 15 minutes 3 time if deadlock found.

assume time 10am. run @ 10am, if deadlock found, call job again @ 10:15am, @ 10:30am "if dead locked", 10:45am "if dead locked." if last run dead locked terminate job completely.

here have done.

declare totaltrys int default 0; declare exit handler sqlexception begin     diagnostics condition 1     @err_num = mysql_errno;      if(@err_num = 1213 , totaltrys < 4)         set totaltrys = totaltrys + 1;         select sleep(900);         call guardi.prod_update_guardi_accounts();     end if;  end; 

is valid approach? there better way other eliminating deadlocks together?

i keep in procedure: code related behaviour @ 1 location. also: deadlock not follow execution of call statement instead of proceed? if proceeds, solved, , waiting nothing. transactions following deadlock, executed after deadlock resolved.

my approach code be:

create procedure manageaccounts() begin declare totaltrys int default 0; declare exit handler sqlexception diagnostics condition 1 @err_num = mysql_errno; call guardi.prod_update_guardi_accounts(); while(@err_num = 1213 , totaltrys < 4)     set totaltrys = totaltrys + 1;     select sleep(900);     call guardi.prod_update_guardi_accounts(); end while; end; 

you have test this: untested, typos not impossible.