UPDATE WHERE from a Record type (optimistic concurrency control)

[bok-]

Hey GRDB community,

I'm looking at implementing a form of optimistic concurrency control / optimistic locking on top of some of our tables as part of a synchronisation process.

Ultimately what I'm trying to do is create a statement akin to UPDATE table SET <colums> WHERE primary_key = xxx AND checksum = yyy — so cancel the update if the checksum/etag/modification date has changed since we fetched the value as it probably means it has been changed since then.

I can do that using the query interface directly, but I can't see a way to customise the WHERE clause when working with a Record, or is this perhaps how updateChanges() is meant to be used and I'm just holding it wrong? Otherwise if there is a way to create a [ColumnAssignment] from a Record I can just use Record.update() directly but I can't seem to figure that out either without going through EncodableRecord + PersistanceContainer.

Any suggestions would be much appreciated!

[groue]

Hello @bok-,

I can't see a way to customise the WHERE clause when working with a Record,

Indeed GRDB provides no way to customize the WHERE clause of the UPDATE statements generated by the update family of record instance methods. It is made of the primary key and only the primary key.

I can do that using the query interface directly.

:-) Indeed it is always possible to talk to the database.

But I have difficulties figuring out what your technique can be. The only updating method that allows configuration of the WHERE clause is the request.updateAll(db, assignments) method. Maybe you write something as below?

// UPDATE player
// SET name = ..., score = ...
// WHERE id = xxx and checksum = yyy
let updatedPlayersCount = try Player
    .filter(id: xxx)
    .filter(Column("checksum") == yyy)
    .updateAll(db, [
        Column("name").set(to: player.name),
        Column("score").set(to: player.score),
    ])

is this perhaps how updateChanges() is meant to be used?

It is not. The role of updateChanges() is not to alter the WHERE clause, but to limit the UPDATE clause itself to only columns that are modified:

var player = Player(id: 1, name: "Bob", score: 500)

// UPDATE player SET score = 1000 WHERE id = 1 -- name not updated
try player.updateChanges(db) { $0.score = 1000 }

// Does not hit the database
try player.updateChanges(db) { $0.score = 1000 }

I can't seem to figure that out either without going through EncodableRecord + PersistanceContainer.

I don't think there exist any public api that allows you do to that. For example you can't create a PersistenceContainer in the first place!

---

Well done, @bok-, you just wrote a very interesting feature request :-)

[groue]

The suggested solution preserves the current behavior of update and delete methods (they always update or delete), and requires an explicit opt-in for optimistic locking with optimisticUpdate and optimisticDelete.

This might sound concerning, because one could "forget" to call the optimistic-locking-enabled methods. Why not bake optimistic locking into the record type itself, so that we don't need extra methods, and just alter the behavior of update and delete methods?

This is a valid concern, but based on Record Timestamps and Transaction Date I can also foresee some needs to opt out of optimistic locking, such as migrations, and the need to keep some columns out of the lock. I can even imagine some apps that store several locks in a single database row (each one of them protecting its own subset of columns).

That's why I tend to prefer when apps define their own explicit optimisticUpdate and optimisticDelete methods. They leave the plain update and delete methods available.

[groue]

Hi @bok-, I'm currently working on a branch that adds conditions to all update and delete instance methods. I hope this could help you implement your own checksum-based optimistic locking, but I'd be happy if you could confirm. That's the topic of the two previous messages right above.

[bok-]

Thanks @groue! I've been out of things for the last week with covid but I'm back now. Will take a look at the suggestions today.

[bok-]

Yep I tend to agree — I think the minimal approach is the best one, and we can extend that to explicit methods or record support if we want to. The only drawback I can see is making it clear in the documentation that the condition: parameter is additive to the primary key check, and doesn't replace it.

For a bit of context, our writes are always asynchronous and prioritised in a simple queue, so that user-initiated writes would always be prioritised ahead of data synchronisation writes, and all of that ahead of writes that could trigger FTS updates, etc. My plan is to simply fail the synchronisation attempt if the records are stale as this suggests the user has changed it while a sync is in progress, ad that can be retried normally in the future. The proposal would work perfectly for us then — we can tell if the records were updated and if not why not. So that would be useful for logging.

Would the condition: parameter also be possible for upsert methods or would that be overcomplicating things?

[groue]

OK, I will keep on looking after update/delete conditions. Thanks for your concern about the documentation, it is quite relevant.

Would the condition: parameter also be possible for upsert methods or would that be overcomplicating things?

I assume that what the "upsert with condition" operation you want is [insert if missing, otherwise update all columns if and only if the checksum has a specific value].

GRDB reserves the "upsert" word for the INSERT ... ON CONFLICT DO UPDATE ... SQL statement.

The other related operation provided by GRDB is "save", which is not an atomic database update (it is better wrapped in a transaction). It means "try to update, and it nothing was updated, insert".

I'll see which one is the best fit for the "upsert with condition".