MySQL Using ON DUPLICATE KEY UPDATE in multithreading

Question

I'm trying to figure out how to run a SQL query that does:

if(duplicate key):
    UPDATE with VALUES
else:
    INSERT with VALUES

in multiple threads.

Ordinarily I would do a INSERT...ON DUPLICATE KEY UPDATE to accomplish this task, and I would use a SELECT...FOR UPDATE to do the multithreading. But, how can I accomplish both?

Here's my SQL statement without the SELECT...FOR UPDATE

insert into PUSH_MESSAGES (stageOne, stageTwo, stageThree, stageFour, stageFive,
      highestStage, iOSTotal, androidTotal, iOSRunningCount, androidRunningCount, vendorId,
      problem, iOSComplete, totalStageFours, totalStageThrees,
      expectedTotals, startTime, androidComplete, date, topics, androidFailures, iOSFailures,
      latestUpdate)

      VALUES($first, $second, $third, $fourth, $fifth, $highestStage, $iOSTotal, $androidTotal,
    $iOSRunningCount, $androidRunningCount, $vendorId, $newProblem, $iOSCompleted, $totalStageFours,
    $totalStageThrees, $expectedTotals, $startTime,$newAndroidComplete, $date, $topics, $androidFailures, $iOSFailures)

      ON DUPLICATE KEY UPDATE stageOne=VALUES(stageOne), stageTwo=VALUES(stageTwo), stageThree=VALUES(stageThree),
      stageFour=VALUES(stageFour), stageFive=VALUES(stageFive), highestStage=VALUES(highestStage), iOSTotal=VALUES(iOSTotal),
      androidTotal=VALUES(androidTotal), iOSRunningCount=VALUES(iOSRunningCount), androidRunningCount=VALUES(androidRunningCount),
      vendorId=VALUES(vendorId), problem=VALUES(problem), iOSComplete=VALUES(iOSComplete), androidComplete=VALUES(androidComplete),
      totalStageThrees=VALUES(totalStageThrees), totalStageFours=VALUES(totalStageFours), expectedTotals=VALUES(expectedTotals),
      startTime=VALUES(startTime), date=VALUES(date), topics=VALUES(topics), androidFailures=VALUES(androidFailures),
      iOSFailures=VALUES(iOSFailures)

But, when I try and add in the SELECT...FOR UPDATE I end up getting stuck. I figure I must do something like:

select * from push_messages where vendorId=$vendorId for update

update push_messages set...

And then I don't know how to put an insert in there? What am I setting in the initial update? Thanks.

What does threading have to do with it? If helpful see my http://stackoverflow.com/a/38079598 ... keep that lock open for a split second. MT issues go away — Drew, Aug 12 '16 at 20:56
So let's say your thing is called `Carburetor` for the gateway. Between line 4 and 5 do your IODKU — Drew, Aug 12 '16 at 21:01
But, you can't do an `insert` where you've done a `select`, can you? — LivingRobot, Aug 12 '16 at 21:06
As long as you wrap your queries in transactions, it should work fine with multiple threads. — Barmar, Aug 12 '16 at 21:06
I think you need to [edit] and clear up what the actual problem is — Drew, Aug 12 '16 at 21:09
I updated the question with actual code. Thanks for all your help. — LivingRobot, Aug 15 '16 at 16:17
@Barmar can I wrap the `insert` in a transaction and the `on duplicate key update` in a separate transaction? How exactly would that work? — LivingRobot, Aug 15 '16 at 16:36
No, `on duplicate key update` is part of the `insert` statement, they can't be separated. — Barmar, Aug 15 '16 at 16:37
The transaction ensures that all the inserts and updates are done atomically, so other processes will never see an intermediate version of the table. — Barmar, Aug 15 '16 at 16:38

MySQL Using ON DUPLICATE KEY UPDATE in multithreading

0 Answers0