-1

Introduction:

1- I am not looking for a code solution.

2- I am only asking for a way of thinking (algorithm)

Problem Description:

1- I have a mysql database running on a server.

2- The database will be accessed by several clients at the same time using a webapp powered by php

3- If client A opens a record recordIdX within the webapp, it will be displayed in correspondent html input fields, and the client will have the time to modify those values before submitting the form.

4- The problem that might happen is when another client B opens the same recordIdX, modifies it, and update the database before client A finishes the update..

Question:

What is the best way to prevent this conflict ?

mlwn
  • 1,156
  • 1
  • 10
  • 25
  • 5
    Whatever you end up choosing, you need to consider carefully: what if client `A` opens the record, but then never edits/closes it? – Niet the Dark Absol Apr 11 '18 at 11:21
  • 1
    "I am not looking for a code solution." they why is the tag PHP included? – Raymond Nijland Apr 11 '18 at 11:21
  • 1
    I would consider something like hashing the record and putting that in a hidden field. Then when saving, check to see if the record still has the same hash. If not, then someone else edited it and you need to do some conflict resolution. – Niet the Dark Absol Apr 11 '18 at 11:21
  • 2
    "What is the best way to prevent this conflict ?" -> Use InnoDB as table engine and include FOR UPDATE into the select `SELECT * FROM [TABLE] WHERE .... FOR UPDATE` this way the record can only be updated by the first client who has openend it with `FOR UPDATE` – Raymond Nijland Apr 11 '18 at 11:27
  • 2
    This type of question is both too broad and possibly opinion-based. I would post this on DB exchange instead https://dba.stackexchange.com/ - You will get a better response there. – Funk Forty Niner Apr 11 '18 at 11:36
  • @RaymondNijland, why don't you convert your comment related to InnoDB to an answer ? – mlwn Apr 11 '18 at 12:54

1 Answers1

2

One webapp I developed had a similar requirement. This is how I did it:

Tables wich should be 'lockable' had a accessId column. accessId was a foreign key to the access table wich had some relevant columns for example: isLocked, isLockedByUserId, lastModificationDate, ...

This might be interesting too: Optimistic vs. Pessimistic locking

N. D.
  • 311
  • 2
  • 8
  • still prone to race conditions.. what if both clients request the same record at the same time and the database didn't had time to update the lockable record intime. – Raymond Nijland Apr 11 '18 at 11:28
  • @RaymondNijland pessimistic locking would prevent race conditions – N. D. Apr 11 '18 at 11:30
  • Best option the topicstarter has is to use InnoDB and `FOR UPDATE` and let the database handle the locking.. – Raymond Nijland Apr 11 '18 at 11:31
  • 2
    1 or 2 additional fields (lock guid,timestamp) can be used instead of another table, but otherwise this is the only solution. `FOR UPDATE` has to be used to guarantee atomic locking. – Vatev Apr 11 '18 at 11:50
  • 1
    @Vatev, I actually followed your opinion with just one field `isopen` .. I don't need more information... if it is set to 0, set it to 1 and open record... otherwise, echo message that field already in use... – mlwn Apr 11 '18 at 15:32
  • 2
    @mlwn how do you tell which client has the lock and who has to wait? Also you will need a timestamp and a lock timeout. Otherwise the record can (and will) become locked permanently after a network failure, crash etc. – Vatev Apr 12 '18 at 07:11
  • @Vatev, I already have another variable with the client id which is logged in. So, if the field `islocked` is `0`, nobody is using the field.. otherwise, I put the id of the client in this field `e.g. 4` for client id `4`... any user who wants to use the field will get a message that the field is locked by client id 4. If it was locked after network failure, client id 4 can still open the field and close it normally which will unlock it... that's basically more than what i need.. – mlwn Apr 12 '18 at 10:47