support optimistic locking

[uladkasach]

upserts should support optimisitc locking for concurrency control

for example, the contract could look like

const upsertedSms = await smsDao.upsert({
    dbConnection: dbConnectionInTransaction,
     sms: new Sms({ ...sms, status: statusNow }),
     lockOn: sms.versionId, // abort the txn if the versionId has changed since the read (i.e., there was a parallel, conflicting write that came before it);
});

refs:

• https://stackoverflow.com/a/129397/3068233

[uladkasach]

this should also support saying lockOn: null

• i.e., "abort the txn if there is a row for the unique keys"
• which enables the user to ensure there is no record in the database for a domain-object w/ these unique keys, using optimistic locking

this will enable this one simple "upsert" command to be used in high concurrency operations to allow the user to atomically/safely fulfill the use case of:

• insert (i.e., throw an error if the row exists)
• findOrInsert (i.e., read, find that there was nothing, and then use upsert w/ lock-on-null to insert while expecting still nothing)

by letting the user specify to the command their expectation that the row does not exist:

• allowing the user to express that assumption with the simple upsert command they are already used to
• requiring the user to opt-into having errors thrown by this function under certain circumstances
  • making it much less likely that they'll not realize how this type of operation can shoot them in the foot (due to errors in high concurrency settings: i.e., two parallel invocations both read and then write at the same time, neither sees other in the read)
  • as opposed to an insert command, for example, where you would be less inclined to consider the effect of parallelism - with the lockOn syntax, you realize that you're asking for an error to be thrown in this specific situation