What could make this function hang and throw error:
Lock wait timeout exceeded; try restarting transaction
My code as follows:
"CREATE DEFINER=`root`@`localhost` FUNCTION `shiftSchedulesDates`(`start_date` DATETIME, `branches` longtext, `centers` longtext, `officers` longtext ) RETURNS varchar(5000) CHARSET utf8mb4
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
declare next_date datetime default start_date;
declare paymentType varchar(255) default 'Daily';
declare accountId varchar(12);
declare schdNo varchar(12);
declare schdCounts int default 0;
declare selSchdDateCounts int default 0;
declare shiftedSchds varchar(100);
declare tenure int;
declare loop_on tinyint default 0;
declare loop_on2 tinyint default 0;
set @brch = ifnull(branches,'-1'), @centers = ifnull(centers,'-1'), @officers = ifnull(officers,'-1');
select count(sl._id) from schedules_log as sl join loan_accounts la on(sl.account_id = la._id)
join profile_details pd on(la.profile_id = pd._id and (@brch = '-1' or pd.branch_id = @brch) and (@centers = '-1' or pd.id_1 = @centers) and (@officers = '-1'or pd.id_2 = @officers))
where (date(sl.expected_repayment_date) = date(start_date) and sl._status in ('Partially Paid','Pending','Arrears') and sl.expected_repayment_amount > 0) limit 1 into loop_on;
outer_looper: while ( loop_on > 0 ) do
select sl.account_id, sl.schedule_number from schedules_log as sl
join loan_accounts la on(sl.account_id = la._id)
join profile_details pd on(la.profile_id = pd._id and (@brch = '-1' or pd.branch_id = @brch) and (@centers = '-1' or pd.id_1 = @centers) and (@officers = '-1'or pd.id_2 = @officers))
where (date(sl.expected_repayment_date) = date(start_date) and sl._status in ('Partially Paid','Pending','Arrears') and sl.expected_repayment_amount > 0) limit 1 into accountId, schdNo;
select la.payment_type, la.installments from loan_accounts as la where (la._id = accountId) into paymentType, tenure;
set next_date =
if(paymentType = 'Daily', date_add(start_date, interval 1 day),
if(paymentType = 'Weekly', date_add(start_date, interval 1 week),
if(paymentType = 'Monthly', date_add(start_date, interval 1 month),
date_add(start_date, interval 1 year))));
select count(sl._id) from schedules_log as sl where (sl.account_id = accountId and sl.schedule_number = schdNo and sl.expected_repayment_amount > 0 and sl._status in ('Partially Paid','Pending','Arrears')) limit 1 into loop_on2;
inner_looper: while ( schdNo <= tenure and loop_on2 > 0 ) do
set next_date = if(dayname(next_date) = 'Saturday' or dayname(next_date) = 'Sunday', getNextDate(next_date, 1, paymentType, 1), next_date);
update schedules_log as sl
set sl.expected_repayment_date = next_date
where (sl._id = concat(accountId,':',schdNo) and sl.account_id = accountId and sl.schedule_number = schdNo and sl.expected_repayment_amount > 0 and sl._status in ('Partially Paid','Pending','Arrears'));
set next_date = getNextDate(next_date, 1, paymentType, 1);
set schdNo = (schdNo + 1);
set schdCounts = (schdCounts + 1);
select count(sl._id) from schedules_log as sl where (sl.account_id = accountId and sl.schedule_number = schdNo and sl.expected_repayment_amount > 0 and sl._status in ('Partially Paid','Pending','Arrears')) limit 1 into loop_on2;
end while inner_looper;
set selSchdDateCounts = (selSchdDateCounts + 1);
-- check for more schedules
select count(sl._id) from schedules_log as sl join loan_accounts la on(sl.account_id = la._id)
join profile_details pd on(la.profile_id = pd._id and (@brch = '-1' or pd.branch_id = @brch) and (@centers = '-1' or pd.id_1 = @centers) and (@officers = '-1'or pd.id_2 = @officers))
where (date(sl.expected_repayment_date) = date(start_date) and sl._status in ('Partially Paid','Pending','Arrears') and sl.expected_repayment_amount > 0) limit 1 into loop_on;
end while outer_looper;
RETURN concat(selSchdDateCounts,':',schdCounts);
END"