Record locking - blocking simultaneous editing.

[Sasquatch]

Hi
I have an application to migrate from access(access+ TrueNas +multi user = data corruption).
One of the requirements is to block simultaneous editing of table data by 2 users, most edit cases are adding notes, or filling out empty fields.
two or more user should have read access to the same record, but only user who opened edit form first should have write access - other users will have all fields in read only mode, or save/delete buttons missing.
Is there build in mechanic for that?
If not I have a foggy idea how to approach it using PHP/JS.

I could add 2 columns to the tables in question : lock_user and lock_timestamp.
When opening the form BE(before Edit) PHP code would check if Lock_user is not null and lock_timestamp is current(say within 2 minutes from now) and proceeds accordingly.
sets lock_user and lock_timestamp fields
then somehow lock_timestamp should updated periodically, say every 60 seconds. I guess that would be JS? is it even possible in nubuilder?
or
sets all fields to read only
(optional)then periodically checks lock_timestamp and lock_user - again JS? again, is it even possible in nubuilder?
finally AS PHP sets lock_* fields to NULL.
lock_timestamp is there to prevent permanent lock in case of crash-exit.

[Janusz]

Hi, just to share my approach and to encourage others present their proposals or implementations.

In my case I am just blocking that someone is not overwriting data saved in the mean time by someone else.
I did not go for records blocking as it would not be difficult to implement - because my database is used from many different locations and it may happen that connection during edit will be lost and then my record would stay blocked - and not allowing any further modification - so that's why it was to risky for me to implement. Or even just unwanted browser closure would leave a record in blocked state.
So what I am doing for all my records - I have always 2 fields added updated_by & updated_on. And in the PHP BS I check if these values are the same as at the time when someone opened the record in edit form.
If these data are different then record is not saves and a message is displayed for the user that during his edit someone else modified the record - so he can copy some data he entered even to notepad. Next he can open the record again and modify if needed. As well someone can keep record in edit mode for the long time blocking other. As well auto cancelling what he entered and did not saved yet - in my case would not be acceptable. I think that blocking mechanism can be much different between applications.

[kev1n]

it may happen that connection during edit will be lost and then my record would stay blocked - and not allowing any further modification - so that's why it was to risky for me to implement. .

My solution:

To deal with this, a maximum lock time can be defined. When this time has expired, the record can be edited again by all.
However, the user who is blocking a record can still edit it even if the browser is restarted.
In addition, a Globeadmin or Superuser can unlock a locked record if necessary.


The locking mechanism I use, however, works a little differently.
There is a queue (records) that is to be processed based on the FIFO principle.
A button "Process Next" opens the next/oldest record which is locked. This ensures that no two people process a record at the same time. In my case, however, I do not need the possibility that the record can be opened readonly by someone else.

[Sasquatch]

Thanks for rapid replies.

So what I am doing for all my records - I have always 2 fields added updated_by & updated_on. And in the PHP BS I check if these values are the same as at the time when someone opened the record in edit form.
If these data are different then record is not saves and a message is displayed for the user that during his edit someone else modified the record - so he can copy some data he entered even to notepad.

That's actually very elegant solution, can you share the PHP BS code?
and message JS code too?
pretty please .
possibility to copy edited data and re submit is very good.

[Janusz]

Hi,
The working code is implemented and accessible in our test database:
https://test.nubuilder.cloud/

you can log with (globeadmin type account).
login: test
psw: nutest

(standard user type account)
log: guest
psw: nuguest

I prepared as well short youtube video how everything is connected:

https://youtu.be/lVukJEQ3Zwg

If you would have any questions please feel free to post them here.

More explanation for this database you can find as well here:
https://forums.nubuilder.cloud/viewtopic.php?f=19&t=10889
https://forums.nubuilder.cloud/viewtopic.php?f=19&t=10890

[Sasquatch]

This is AWESOME thank you.