SELECT FOR UPDATE and UPSERT semantics

[Rob Shepherd <rg...@gmail.com>]

Hi,

In our current RDMS-based solution we rely on a couple of SQL routines
based on SELECT for UPDATE and UPSERT statements that allow us to find and
modify some rows to be updated in-place (based on the select criteria) and
to insert some rows if they don't exist.

I appreciate these aren't available in Ignite's SQL grammar, but can the
same thing be achieved in transactions?

Possibly we could start a transaction to find rows that need updating, then
loop through them to update each one, then commit the transaction.

One of the features of SELECT FOR UPDATE is that it will lock rows, and
when we have multiple such polling routines we can prevent them for
attempting multiple updates at the same time using NOWAIT to ignore a
poller that has already SELECT FOR UPDATE some of the same rows.

Before I start to evaluate deeper, can anybody think of a showstopper if we
try and replicate what we have in ignite?

Thanks

Rob