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 which is updating same table thrice with joins with other table is not running properly. sometimes it is only updating ABOR data and sometimes it is updating only IBOR data. sometimes it update FUND_IMPACT column, other times don't execute it at all. Please suggest why it is behaving like that. Timeout is set as 600 ms.
Discussion posts and replies are publicly visible
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.
UPDATE
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 happening exactly with the procedure.
If it were something related with row locking, the behaviour would be different, because the SP would return an error, instead of update only certain rows.
Check your sentences, I think that will be related with the where/when clauses used.