How to manage updates in a multi-user environment

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
jon@armasoft.co.uk
Posts: 20
Joined: Mon Apr 18, 2016 10:31 am

How to manage updates in a multi-user environment

Post by jon@armasoft.co.uk » Thu Apr 28, 2016 1:40 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6638
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: How to manage updates in a multi-user environment

Post by FourthWorld » Thu Apr 28, 2016 3:35 pm

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.
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

MaxV
Posts: 1574
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: How to manage updates in a multi-user environment

Post by MaxV » Wed May 04, 2016 1:15 pm

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.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Post Reply

Return to “Databases”