SingleEdit: Prevent concurrent edits on records.

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1027
Joined: 2019-05-21 22:42
Location: Germany
Contact:

SingleEdit: Prevent concurrent edits on records.

Post by onoehring » 2021-01-15 17:32

Hi,

I created a new extension which should be easy to implement: SingleEdit
Purpose: Prevent concurrent edits on records.

As AppGini (AG) in the current version 5.92 does not support locking records for user B if user A currently edits them, I decided to make a start.
From the docs:

Introduction
A common problem working with online-databases is, that it’s almost impossible to handle concurrent edits on the same record.
If user UA has record RA open in the browser, nothing prevents user UB from opening that same record RA in his browser. So far there is no problem, as user UA and user UB are only viewing the record. A problem arises, once both users start editing the record.
This might seem not so common, but people might keep their browser window open, do something else and try to save an edited record 30 minutes after they opened it. We, as developers, just do not know if the user is viewing, editing, has his browser closed etc.

Let’s make an example and assume the records shows customer’s C1 data: Customer’s C1 lastname, firstname and street address. The data in the database reads as follows:

Code: Select all

Lastname: Noehring
Firstname: Olaf
Streetaddress: Somestreet 3
UA not starts editing the record and changes the lastname because the customer C1 has married, so the data in the browser of UA is now:

Code: Select all

Lastname: Merkel		<--- the value has been changed by UA
Firstname: Olaf
Streetaddress: Somestreet 3
At the same time UB changes the street address because the customer C1 has moved, so the data in the browser of UB is now:

Code: Select all

Lastname: Noehring
Firstname: Olaf
Streetaddress: Countryside 99		<--- the value has been changed by UB
We need do keep in mind every user sees the values on screen as they have been pulled from the database when the page was generated. This UA does not notice (yet), that UB changes the streetaddress and UB does notice the changes of the column lastname by UA.

UA now presses the save changes button in the browser window. The values that will be written to the database, overwriting existing values, are:

Code: Select all

Lastname: Merkel		<--- the value that has been changed by UA
Firstname: Olaf
Streetaddress: Somestreet 3
Once UB presses the save changes button in the browser window. These values will be written to the database, overwriting existing values:

Code: Select all

Lastname: Noehring
Firstname: Olaf
Streetaddress: Countryside 99		<--- the value that has been changed by UB
We can now see the problem: The data has been messed up and is wrong. The user has his old lastname but the new streetaddress. The new lastname which UA had changes was overwritten by the old lastname when UB saved the values from his browser window.
User UB should not have been able to save his edits, but should have been notified, that the records has been changed in the meantime.

This script solves this problem.

It can look like this:
singleedit_err1.png
singleedit_err1.png (18.43 KiB) Viewed 996 times
Download v0.9
(.ZIP, size 237kb, including files and documentation)
https://dl.olaf-noehring.de/?t=167959be ... 3be10084cb

Comments? Ideas? Suggestions?
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1027
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: SingleEdit: Prevent concurrent edits on records.

Post by onoehring » 2021-01-16 06:30

Hi,
be patient. I removed v0.9 and making it easier and better to implement. New version coming up in one or two hours.
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1027
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: SingleEdit: Prevent concurrent edits on records.

Post by onoehring » 2021-01-16 07:36

Hi,

it was a long day yesterday and I obviously way a bit too eager to release. So, release early and often, I present the 0.91 version (the previous version has been removed).
  • To use SingleEdit 0.91 you need to add only 7 lines of code now (including 2 lines of comments). This should make implementation even easier and your code cleaner.
  • You can adjust the custom error message more easy (moved variable).
  • Updates should be way easier, as the code is not simply included (to be exact: required) from two files.
Download v0.91
https://dl.olaf-noehring.de/?t=81bb7a67 ... db71170e80

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1027
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: SingleEdit: Prevent concurrent edits on records.

Post by onoehring » 2021-01-18 11:03

Hi,

I forgot to remove some debugging calls. They are gone now in this version (the previous version has been removed):

Download v0.92
https://dl.olaf-noehring.de/?t=562ee17e ... 08c5f358c9

Olaf

ltlow
Posts: 10
Joined: 2021-07-29 09:37

Re: SingleEdit: Prevent concurrent edits on records.

Post by ltlow » 2021-09-13 01:56

Did anyone getting this to work? Nothing show when multiple user save the record at same time. The data still corrupted.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1027
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: SingleEdit: Prevent concurrent edits on records.

Post by onoehring » 2021-09-13 13:21

Hi,

what you mean by "data corrupted"?
Did you follow the installation instructions?

Olaf

ltlow
Posts: 10
Joined: 2021-07-29 09:37

Re: SingleEdit: Prevent concurrent edits on records.

Post by ltlow » 2021-09-14 01:36

ya, I follow the installation guilde in the PDF file.
1. put the 3 php file in my hooks folder.
2. Put the code in _init function and _before_update function in my tablename.php in hooks folder
3. try the code with multiple user access to same record at same time. User A change field a and save changes. User B change field b and save changes. No any error message show and the data is mess up.

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 268
Joined: 2013-03-21 04:37

Re: SingleEdit: Prevent concurrent edits on records.

Post by peebee » 2021-09-14 04:22

Have to say Olaf, I tried your solution myself, followed your instructions to the letter - didn't see any result at all? That's using V5.97, not sure if that makes any difference? I didn't actually look at any of your code, just uploaded and added defaults to hook file as recommended. Nothing happened and simultaneous editing is still available without even a prompt.

Failure to prevent simultaneous editing is VERY problematic and DOES lead to data corruption.

I have been requesting some sort of a row level locking (or at absolute minimum an alert) in Appgini for around 5 years now. Check the forum. Sadly, it has never materialised.

Not promoting an alternative product here because Appgini is infinitely better....... but ideally I had hoped for a solution to be built into Appgini, much say like this similar software here:
https://xlinesoft.com/phprunner/docs/au ... rd_locking

I've attempted many different solutions myself, including adding a complete record_lock table (as is used in the software above) but I realise my limitations and I just can't get it to work?

I understand record locking is a complex/tricky problem to solve but in my own humble opion, it is absolutely necessary if you intend to maintain any form of data integrity.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1027
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: SingleEdit: Prevent concurrent edits on records.

Post by onoehring » 2021-09-15 08:18

Hi peebee & ltlow

thanks for your replies.
I will take a look at the code and try to fix it. I am happy for all feedback as we are all working to make AG even better.
I believe it was Jan who has already written about all problems with row locking in a browser based environment in this forum. I am not sure how (good) phprunner implements this. Effective row locking would be great and it should be so, that it can not circumvented by fiddling with JS. At least, this was and is my approach.

Olaf

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 268
Joined: 2013-03-21 04:37

Re: SingleEdit: Prevent concurrent edits on records.

Post by peebee » 2021-09-16 00:44

The other software's implementation of record locking is excellent - all recorded in a dedicated table so Admin can override at any time. Their logic is very sound and effective. It is however extremely complex and to implement something similar into current Appgini would require massive core edits. I gave it a go and gave up after a week or so of failures. It's something that's really necessary to be implemented from the outset in Appgini's generated files.

With regards to circumvention via javascript, record locking is more of an administrative/data integrity function rather than security so that isn't perhaps a major consideration?

As a minimum, I'd be very pleased to just see a simple alert that a record is currently open/being edited by another User. Might at least limit some data loss.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1027
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: SingleEdit: Prevent concurrent edits on records.

Post by onoehring » 2021-09-16 05:31

Hi peebee,

did you consider adding a extra column(s) to a record where you save a locking information?
Like person who locked and timestamp?
This way it should be quite easy to check in the _init / _before_update if this/these column(s) have a value, and if do some other checks (like:
_init: disable save option
_before_update: if timestamp is too long ago, overwrite anyways)
I did not go this way, as it requires such changes in the actual application tables.
An option for the admin to "unlock" records should be easy to implement then as well.

Maybe this is an option for you?

My approach however is totally different and (if it works .. and right now it does work for me ... I wil check, so it will work for all) does not require changes in the application tables. Circumventing my check is also not possible (by disabling javascript).

Olaf

ltlow
Posts: 10
Joined: 2021-07-29 09:37

Re: SingleEdit: Prevent concurrent edits on records.

Post by ltlow » 2021-09-22 00:55

Hi onoehring,

Did you figure out why it does not working in appgini 5.97? Attached with the app generated with Appgini 5.97 use to test your code in case you need it to debug. It only has a simple table with 2 fields.

https://drive.google.com/file/d/1lfoTBX ... sp=sharing

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1027
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: SingleEdit: Prevent concurrent edits on records.

Post by onoehring » 2021-09-25 08:10

HI itlow,

I had no time to check yet. I downloaded your files. When I find time, I will look into it. I might wait until AG 6.0 is released though.

Olaf

Post Reply