How to manage updates in a multi-user environment

[jon@armasoft.co.uk]

I'm currently putting together a design for a multi-user desktop application where I'm intending to populate livecode grids via SQL select statements.

It will be possible for a user to update a record within the grid which will then trigger an SQL update for that record. My problem is that it's possible another user may have updated the same record between the first user downloading a copy into the grid and performing their update.

My initial thought is to include a 'version' field in the SQL table that is incremented each time the record is updated and to check that it has not been changed between download and update - if a change is detected then I can run a comparison of the 2 versions and decide what action to take depending on what has changed where.

Does anyone use an alternative method to manage this issue or have any comments to make on this option?

Thanks for any feedback.

Jon.

[FourthWorld]

I've seen many multi-user apps that rely on optimistic conflict resolution, but I prefer explicit check out/check in to keep things clear and simple for both me and my users.

In workgroup solutions I've built everyone can view anything, but when a user wants to edit a record it first checks to see if the record has already been checked out by someone else. If so, it informs the user that the record is being edited by someone else and thus they're not able to check it out for editing themselves. If not, the server flags the record as checked out and the UI now allows editing. On check-in the flag is cleared after the record has been successfully written to the data store.

While this does impose an additional step for users, I haven't met a user yet who found it at all cumbersome. On the contrary, explicitly checking in and checking out records seems to work well conceptually for most users, perhaps even better than the guesswork of hoping records will not be edited simultaneously.

[MaxV]

If is just a database, not a cloud database with many replicas, the solution is simple.
Just use begin tansaction and commit.
Example of a query:

Code: Select all

BEGIN TRANSACTION;
...all my queries;
COMMIT;

During a transaction nothing else can happen on the database. Consider that you can use some table cells as variables and there is the CASE statement for decisions. So you can program all in the transaction.