How to batch update with conditions?

edwinsn · 2018-10-15 07:00:43

The concept's illustrated with this example:

https://gist.github.com/edwinyzh/656250 … 7994811d7f

In short, to implement the 'UPDATE...WHERE...' mechanism in mORMot's batch-update.

Do you like this suggestion, ab?

ab · 2018-10-15 07:04:47

The Batch update is only by ID.
So first retrieve the IDs then apply the Batch.

edwinsn · 2018-10-15 09:02:10

ab wrote:

So first retrieve the IDs then apply the Batch.

What if the record has been changed in the database by **other clients**, in between 'first retrieve the ID' and 'apply the Batch'?

ab · 2018-10-15 10:56:18

Then you should use a regular transaction on the server side, using a persistence service.

I hope you do encapsulate all your data access in a persistence service, and never call directly the ORM from the client, right?

edwinsn · 2018-10-15 12:35:00

OK, maybe I have a misunderstanding about SQLite transactions - can the following situation (step 3) happen on the server side?

1 - start transaction.
2 - read a record from db.
3 - the record read in step 2 is modified by another client.
4 - update the record.
5 - commit the transaction.

Ehab · 2018-10-15 12:52:31

Hi edwinsn,
I'm new to SQLite, but I know that you can't update any record inside the transaction.

But still that another user can save his modified version of record after the current user end his transaction.

ab · 2018-10-15 13:21:15

You CAN update any record inside a transaction.
This is what transactions are for.

ab · 2018-10-15 13:23:06

You CAN update any record inside a transaction.
This is what transactions are for, and base of https://en.wikipedia.org/wiki/ACID_(computer_science)

If another user save his modified version in another transaction, the last one will be taken in account.

If you expect something else, a higher level mechanism (e.g. as locking, or versioning) may be used, but mainly in the persistence or even the business logic layer.

mpv · 2018-10-15 21:22:59

The feature @edwinsn request is known as "Optimistic locking", and as @ab note it usually should be implemented in the business logic layer.
I have it in my apps (every row store a modification date). Before run update on server side I compare modification date passed from client side with date stored in DB (yes, one more select, but it is quick) and in case it's not equal return error to client "Record is modified by another user".

edwinsn · 2018-10-16 03:33:56

mpv wrote:

The feature @edwinsn request is known as "Optimistic locking", and as @ab note it usually should be implemented in the business logic layer.
I have it in my apps (every row store a modification date). Before run update on server side I compare modification date passed from client side with date stored in DB (yes, one more select, but it is quick) and in case it's not equal return error to client "Record is modified by another user".

I'm doing the similar thing - the logic is the same, except that I do it on the client-side before submitting the batch-update, so it's not wrapped by a DB transaction.

edwinsn · 2018-10-16 12:19:01

BTW, I use mORMot's TModTime field type for the so-called 'modification date', and it's a perfect fit

mORMot Open Source

#1 2018-10-15 07:00:43

How to batch update with conditions?

#2 2018-10-15 07:04:47

Re: How to batch update with conditions?

#3 2018-10-15 09:02:10

Re: How to batch update with conditions?

#4 2018-10-15 10:56:18

Re: How to batch update with conditions?

#5 2018-10-15 12:35:00

Re: How to batch update with conditions?

#6 2018-10-15 12:52:31

Re: How to batch update with conditions?

#7 2018-10-15 13:21:15

Re: How to batch update with conditions?

#8 2018-10-15 13:23:06

Re: How to batch update with conditions?

#9 2018-10-15 21:22:59

Re: How to batch update with conditions?

#10 2018-10-16 03:33:56

Re: How to batch update with conditions?

#11 2018-10-16 12:19:01

Re: How to batch update with conditions?

Board footer