Issue on UPSERT when concurrency is high
Hello All,
We are using apache storm application for message processing. The messages are parsed and inserted/updated on MySQL 5.7.16 which is installed on a Linux machine. We are trying to replicate the operations happening on an Oracle DB and hence we might receive INSERT,UPDATE or DELETE statements. For each of these operations we receive a separate message. Since we are reading this messages from a queue and multiple nodes are doing this at the same time, we can’t assume any sequence in the processing of messages. In other words we might receive an UPDATE statement first and INSERT later. To make sure the latest update is reflected in Maria DB we are doing the following
1) Using an UPSERT statement, so that INSERT and UPDATE can be handled in the same way.
2) Using timestamp (SRC_SEQ_TMSTP column to be specific) to identify the latest update and ignore if the request has an older timestamp than the value currently present in DB.
3) USING SELECT for UPADTE for locking the row to support high concurrency.
4) We are doing SOFT DELETE only
@@GLOBAL.tx_isolation is set as READ-COMMITTED and @@tx_isolation is set as REPEATABLE-READ
A sample query is given below
INSERT INTO DEV_MRCH_OPR.ORDSKU_DST (ITEM_DST,REF_ITEM_DST,LATEST_SHIP_DATE_DST,NON_SCALE_IND_DST,ORIGIN_COUNTRY_ID_DST,PICKUP_LOC_DST,
EARLIEST_SHIP_DATE_DST,SUPP_PACK_SIZE_DST,ORDER_NO_DST,PICKUP_NO_DST,RCD_LOAD_TMSTP,RCD_UPDT_TMSTP,RCD_LOAD_USER,RCD_UPDT_USER,RCD_SRC_SYS_ID,
DELETE_IND,SRC_SEQ_TMSTP,ACTION_FLG) SELECT temp.ITEM_DST,temp.REF_ITEM_DST,temp.LATEST_SHIP_DATE_DST,temp.NON_SCALE_IND_DST,
temp.ORIGIN_COUNTRY_ID_DST,temp.PICKUP_LOC_DST,temp.EARLIEST_SHIP_DATE_DST,temp.SUPP_PACK_SIZE_DST,temp.ORDER_NO_DST,temp.PICKUP_NO_DST,
temp.RCD_LOAD_TMSTP,temp.RCD_UPDT_TMSTP,temp.RCD_LOAD_USER,temp.RCD_UPDT_USER,temp.RCD_SRC_SYS_ID,temp.DELETE_IND,temp.SRC_SEQ_TMSTP,
temp.ACTION_FLG from ( select '101' as ITEM_DST,'88257906' as REF_ITEM_DST,'2016-02-08:16:38:09' as LATEST_SHIP_DATE_DST,'Y' as NON_SCALE_IND_DST,
'CA' as ORIGIN_COUNTRY_ID_DST,'808' as PICKUP_LOC_DST,'2016-02-08:16:38:09' as EARLIEST_SHIP_DATE_DST,'20.0000' as SUPP_PACK_SIZE_DST,
'26798740' as ORDER_NO_DST,'545' as PICKUP_NO_DST,'2016-10-13 10:56:11.121' as RCD_LOAD_TMSTP,'2016-10-13 10:56:11.121' as RCD_UPDT_TMSTP,
'MDP_JUNIT' as RCD_LOAD_USER,'MDP_JUNIT' as RCD_UPDT_USER,0 as RCD_SRC_SYS_ID,'N' as DELETE_IND,'000000000400149233652016-02-09 11:10:10.000000' as SRC_SEQ_TMSTP,
'UPDATE' as ACTION_FLG from DUAL) temp WHERE '000000000400149233652016-02-09 11:10:10.000000' > IFNULL
((SELECT SRC_SEQ_TMSTP FROM DEV_MRCH_OPR.ORDSKU_DST WHERE ITEM_DST = '101' AND ORDER_NO_DST = '26798740' FOR UPDATE) , 0)
ON DUPLICATE KEY UPDATE REF_ITEM_DST = '88257906',LATEST_SHIP_DATE_DST = '2016-02-08:16:38:09',NON_SCALE_IND_DST =
'Y',ORIGIN_COUNTRY_ID_DST = 'CA',PICKUP_LOC_DST = '808',EARLIEST_SHIP_DATE_DST = '2016-02-08:16:38:09',SUPP_PACK_SIZE_DST = '20.0000',
PICKUP_NO_DST = '545',RCD_UPDT_TMSTP = '2016-10-13 10:56:11.121',RCD_UPDT_USER = 'MDP_JUNIT',RCD_SRC_SYS_ID = 0,DELETE_IND = 'N',
SRC_SEQ_TMSTP = '000000000400149233652016-02-09 11:10:10.000000',ACTION_FLG = 'UPDATE';
We are facing some issues when the concurrency level is very high. We had a scenario where an INSERT and UPDATE happened on a row on Oracle DB. There was no entry corresponding to this in Maria DB. The time stamp of UPDATE was higher than that of INSERT . But in DB only the INSERT was present and UPDATE was ignored. There is no Exception present for the transactions. The possibility we believe is that both INSERT and UPDATE happened at the same time from multiple threads, possibility from MULTIPLE machines. Both operations when verified in the DB could not find any record(since it is a new entry). Both tried to update the DB and while doing that it did in the reverse order.
Is there any way to address this issue. There is a possibility that making transaction isolation SERIALIZABLE, we might address this however we might face a major performance issue. Any other option is welcome.