CODEWITHSUNDEEP
singlestore
Al Yaros
Al Yaros

Reputation: 151

MEMSQL - UPSERT IF (Versioning of records)

I have a stream of events of user creation and user updates.

Each event contains the event type (Creation Or Update) and the entire user entity i.e. UserID, Username, Email Etc.. plus a Version number indicating the user version such that creation will be version 0, 1st update version 1 etc..

The events are not necessarily ordered so I might get Version 2 before Version 1 AND also they can be processed in parallel so that while i'm trying to persist version 1 the other events stream processor(s) is trying to persist in parallel version 2.

I want to perform UPSERT with atomic condition (IF) such that successfull update will be only if VERSION_IN_DB < NEW_VERSION_IM_TRYING_TO_UPDATE. If the DB doesn't contain any version just insert the user record. (atomic as well)

This all should make sure, that only the latest version of the user will be persisted in all cases - multi-threaded, unordered event stream etc..

Any idea how can I do it with MEMSQL?

Upvotes: 0

Views: 483

Answers (1)

Adam Prout
Adam Prout

Reputation: 739

Something like this should work: 

INSERT INTO <table> VALUES (...) ON DUPLICATE KEY UPDATE 
    col = IF(VALUES(version) > version, VALUES(col), col), 
    <repeat for other columns>, 
    version = IF(VALUES(version) > version, VALUES(version), version)

All the logic in the ON DUPLICATE KEY UPDATE clause runs atomically (and no one else can modify that row until the transaction with the INSERT commits).

Upvotes: 2

Related Questions