Execute stored procedure updating same table thrice with timeout 600

Question

Hello 
 I have created a stored procedure DELIMITER $$
CREATE OR REPLACE DEFINER=`dbadmin`@`%` PROCEDURE `FAR_STAGE_BP_IMPACT`()
BEGIN
 UPDATE FAR_BREAKS_STAGE cod
 INNER JOIN FAR_IBOR_BP_IMPACT_DATA tna ON (cod.FUND_SYMBOL = tna.ACCT_CD_FMRID)
 SET 
 cod.IBOR_TOTAL_NET_ASSETS_USD = tna.MKT_VAL_USD,
 cod.BREAK_BP_IMPACT =
 CASE
 WHEN 
 IFNULL(cod.IBOR_TOTAL_NET_ASSETS_USD,0) = 0 THEN
 0
 WHEN cod.DEBIT_CREDIT = 'Debit' THEN
 CAST(IFNULL(((cod.USD_EQ_Amount / cod.IBOR_TOTAL_NET_ASSETS_USD) * -10000), 0) AS DOUBLE(10, 4))
 ELSE
 CAST(IFNULL(((cod.USD_EQ_Amount / cod.IBOR_TOTAL_NET_ASSETS_USD) * 10000), 0) AS DOUBLE(10, 4))
 
 END
 WHERE
 tna.ACCT_CD_FMRID IS NOT NULL and cod.REC_TYPE = 'IBOR';
 
 
 UPDATE FAR_BREAKS_STAGE cod
 INNER JOIN FAR_ABOR_BP_IMPACT_DATA tna ON (cod.FUND_NUMBER = tna.ACCOUNT_NUMBER_IO)
 SET 
 cod.ABOR_TOTAL_NET_ASSETS_USD = tna.TOTAL_NET_ASSETS_USD,
 cod.BREAK_BP_IMPACT =
 CASE
 WHEN 
 IFNULL(cod.ABOR_TOTAL_NET_ASSETS_USD,0) = 0 THEN
 0
 WHEN cod.DEBIT_CREDIT = 'Debit' THEN
 CAST(IFNULL(((cod.USD_EQ_Amount / cod.ABOR_TOTAL_NET_ASSETS_USD) * -10000), 0) AS DOUBLE(10, 4))
 ELSE
 CAST(IFNULL(((cod.USD_EQ_Amount / cod.ABOR_TOTAL_NET_ASSETS_USD) * 10000), 0) AS DOUBLE(10, 4))
 END
 
 WHERE
 tna.ACCOUNT_NUMBER_IO IS NOT NULL and cod.REC_TYPE = 'ABOR';
 
 UPDATE FAR_BREAKS_STAGE bs SET 
 bs.FUND_BP_IMPACT =
 CASE WHEN 
 bs.REC_TYPE= 'IBOR' THEN
 fn_get_sum(bs.FUND_SYMBOL, 'IBOR')
 ELSE
 fn_get_sum(bs.FUND_SYMBOL, 'ABOR') 
 END;
 
 
END$$
DELIMITER ; 
 This stored procedure is working in unexpected manner. Sometimes only updating ABOR data and sometimes only IBOR and not updating column FUND_BP_IMPACT column at all. timeout is set as 600ms. Is there any way we can increase the timeout and can anyone suggest that do I need to split this one procedure into three to work correctly?

Soma · Answer

Swati Sharda 
 I suspect there could be multiple reasons for that 
 1. Row Locking - If multiple updates are trying to modify the same rows, there could be a row lock that prevents one of the updates from proceeding. 
 2. Each update statement has a Where clause, if one of the condition in the where clause does not match any rows, the update will be skipped for those rows. It could cascade down to further updates too 
 3. If there are triggers on the table that are executing as a result of the UPDATE statements, they might cause unexpected behavior such as skipping rows or modifying the table in a way that affects subsequent updates. 
 I would advise you to try having three similar test tables and test data for BREAKS, ABOR and IBOR. Modify the stored procedure to update these test tables. And then you can try testing and debugging to point out exactly what is happenin with the procedure.