Lock a record to prevent simultaneous User access

[peebee]

I hate to raise this old issue yet again but as far as I can see (from the changelog and my tests at least) there is still no record lock function implemented into the current Appgini release? This is an issue first raised back in 2015 and has been revisited many times since.

Whilst I haven't actually installed V5.81 yet, I did login to the Appgini Northwind demo (presumably V5.81) simultaneously as User "demo" on two different machines and tested on a new/existing entry. There was nothing preventing me accessing/editing/saving the same record simultaneously. If record locking has been implemented and I've missed it - please correct me and forgive me for what's coming below.

Sadly, the absence of record locking allows for inconsistencies and poor data integrity. Record locking is a fundamental of any multi user database. At a bare minimum, a popup alert or similar warning that the record is currently being accessed by User X might pass as acceptable?

I am a major fan of Appgini and use it on a daily basis, have done for many years. It's an incredible piece of software and kudos to Ahmad for developing such an incredibly useful tool. This absence of a record lock is however a bit of a let down.

Ahmad - any update on when record locking may/will be implemented? I really don't like quoting you but this was two years ago: This feature is on the top of our backlog for future releases of AppGini. Stay tuned! and this was one year ago: We're working on it but unfortunately won't be able to include it in the next release (AppGini 5.73) but planning it for AppGini 5.80 that we hope to launch in January.

In the interim, has anybody figured out a suitable working method to lock records from simultaneous access or at least prompt an alert that the record is currently open? Thanks.

[jsetzer]

In the interim, has anybody figured out a suitable working method to lock records from simultaneous access or at least prompt an alert that the record is currently open? Thanks.

For me the problem is, that - due to browser architecture - you cannot really tell if someone has opened a record for editing or not, because users may just close the browser after they have opened a record, and the server does not really know about it. Also, if more than one user has opened a record, you cannot tell if any or which user is currently editing that record.

Yes, you can use Ajax to periodically tell the server who has opened which record and started changing it, and, yes, you can use Javascript to detect if someone closes the browser, but honestly, this is not a good solution from my experience and not bullet-proof.

So what I did in one project is: Only one user can edit a record until he/she unlocks that record. Then everyone who is allowed to can lock that record for editing until he/she unlocks it again. As long as that one user has locked the record for editing, no other user can edit that record.

1. Give edit-permission to owner
2. If someone creates a record, he/she will be owner by default and can edit it
3. If the current user is owner of the record, show an additional "Close record" button
4. On click, change owner to "admin". Only admin will be able to edit the record at this stage, noone else
5. If someone opens the record and the owner is "admin", show an additional "Edit record" button
6. on click, if admin is still owner, change owner to current user.
7. At this stage only that user will be able to edit the record. Continue with step #3

This is how it looks like. Please note that fields are readonly at the beginning. Then, after click on "Edit", fields are editable, record can be saved. Then, after click on "Close", fields are readonly again:

ezgif.com-video-to-gif (2).gif (240.15 KiB) Viewed 4995 times

And this is the (very short) PHP code I'm using in hooks\TABLENAME.php file. All the "magic" has been encapsulated in one PHP class.

Code: Select all

function tasks_dv($selectedID, $memberInfo, &$html, &$args) {
    new AppGiniHelperLocking("tasks", $selectedID, $memberInfo, $html, $args);
}

Right now the ownership-system seems to be the only way to do row-level locking and I'm quite happy with it.

I'm using a similar technique of changing ownership in a workflow-solution for a customer in Norway: Depending on the status of a Record, only certain users are allowed to perform certain actions like: Creator of the record can edit it, creator can forward it to a moderator user for approval, then moderator-user can approve or reject the record. Only approved records will be visible in the list for anonymous users, and so on. These are just examples from that project.

Hope this gives you an idea of how you can do it.

With kind regards,
Jan

[peebee]

Thanks for the detailed reply Jan. I can see how your solution could solve the problem in many cases. Record ownership could prove a problem in my particular situation however.

Ideally, I had hoped from a more programmatic solution to be built into Appgini, much say like this similar software here: https://xlinesoft.com/phprunner/docs/au ... rd_locking

That lock process is based on this logic detailed here: https://webcheatsheet.com/php/record_lo ... ations.php (which references the alternative software and even offers some demo code in the footer).

I have already ventured down the path of creating a new record_lock table where table/ID records, memberID and timestamps could be stored for the purpose of locking rows. That would also allow Admins to unlock records manually in the event of an automated unlock failure. All I need to do now is figure out how to put it all together and get it to work!

It would be a nice (some may say necessary) addition to Appgini core.

[onoehring]

Hi,

maybe check my extension SingleEdit ( viewtopic.php?f=7&t=4122 )

Olaf