MySQL Forums :: InnoDB :: Issue on UPSERT when concurrency is high

Advanced Search

Issue on UPSERT when concurrency is high
Posted by: Kailas Janardhanan ()
Date: December 06, 2016 03:46AM

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
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
'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.

Options: ReplyQuote

Subject Views Written By Posted
Issue on UPSERT when concurrency is high 354 Kailas Janardhanan 12/06/2016 03:46AM
Re: Issue on UPSERT when concurrency is high 174 Rick James 12/09/2016 05:13PM
Re: Issue on UPSERT when concurrency is high 263 Peter Brawley 12/09/2016 07:18PM
Re: Issue on UPSERT when concurrency is high 253 Rick James 12/10/2016 12:21AM
Re: Issue on UPSERT when concurrency is high 163 Peter Brawley 12/10/2016 01:45PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.