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.
Comments
Post a Comment