Lock a row to prevent simultaneous access or editing

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Lock a row to prevent simultaneous access or editing

Post by peebee » 2015-03-18 06:55

I'm looking for a (preferably simple) method to lock an existing record to prevent multiple users from accessing and possibly editing the same db row at the same time?

I'm imagining this could possibly be achieved with a hook but I'm not sure where to start? Ideally, the row would be locked until saved/cancelled (back button) and an (Ajax?) popup would advise other Users accordingly if the row is clicked on. The lock would release when the row is saved/cancelled and I imagine a timeout would also have to apply in case the record is left open?

As an alternative to an actual record lock if too complicated, some indication in the table view that the record is open by another User?

Anybody have any experience in this regard and have a solution? Thanks.

shkdxb
Veteran Member
Posts: 40
Joined: 2013-06-28 18:18

Re: Lock a row to prevent simultaneous access or editing

Post by shkdxb » 2016-03-18 05:35

I am also looking for such Record level locking.

i had a situation where two users tried to update the same record and corrupted fields with wrong details.

tried various combinations like:

1. Changed the DB Table to InnoDB and used "SELECT * FROM `TableName` HAVING ID= '$selectedID' LIMIT 1 FOR WRITE" or "SELECT * FROM `TableName` HAVING ID= '$selectedID' LIMIT 1 FOR UPDATE" in function TableName_dv hooks. this is not working.

2. Created separate RecordLock table and inserted a record with time stamp whenever function TableName_dv is called. Delete this record using function TableName_after_update after the update. But the problem with this method is, when users cancels the update and press the back button or browser back button, the locked record not getting deleted. hence no one is able to access this record.

Any idea how the locked record can be deleted?

shkdxb
Veteran Member
Posts: 40
Joined: 2013-06-28 18:18

Re: Lock a row to prevent simultaneous access or editing

Post by shkdxb » 2016-03-23 09:54

As an alternative to record lock, i made an edit button which will replace the Save as button when details view is called.

Added a button in Detail view template just above update button's <div.. (template folder TableName_templateDV.html file)

replaced Update buttons code with

Code: Select all

<!--addition for Edit button---------==================================---------->
<div class="btn-group-vertical btn-group-lg" style="width: 100%;">
</label><a href="#" id="editbutton" class="btn btn-primary" >Edit record</a>
</div>
<!--addition for Edit button---------==================================---------->
<div class="btn-group-vertical btn-group-lg" style="width: 100%;">
					<%%UPDATE_BUTTON%%>
</div><p></p>
created a file in hooks folder with TableName-dv.js and added below code.

Code: Select all

$j(document).ready(function(){
		//Make form readony and show/ hide edit button
		$j("#MainTable_dv_form *").prop("disabled", true);//disable form
		$j("#update").hide();//hide save as button
		$j(".select2-container").select2("enable",false);//disable all dropdown (select2) lists
		if($$('input[name=SelectedID]')[0].value==''){//if insert mode 
			$j("#MainTable_dv_form *").attr("disabled", false);
			$j("#editbutton").hide();
			$j("#insert").show();
			$j(".select2-container").select2("enable",true);
		}
		
		$j("#editbutton").click(function(){
			$j("#MainTable_dv_form *").attr("disabled", false);
			$j("#update").show();
			$j("#editbutton").hide();
			$j(".select2-container").select2("enable",true);
		}); 
		}

now user has to press edit button to change any record. this prevents accidental updates but still i'm looking for methods to lock the record.

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

Re: Lock a row to prevent simultaneous access or editing

Post by peebee » 2016-09-27 05:28

Anybody manage to achieve this - lock a record once selected to prevent multiple access at the same time?

I've just had an incident where two users were editing the same record at the same time and of course not a happy end result.

Should really be a fundamental element of any secure database UI in my opinion?

Anybody have any ideas?

Noha Eshra
Moderator
Posts: 82
Joined: 2013-11-11 19:21

Re: Lock a row to prevent simultaneous access or editing

Post by Noha Eshra » 2016-09-28 11:58

We plan to introduce record locking in a future version (no particular date yet)! Meanwhile, we might add a lesson to the online customization course to explain how to do it.

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

Re: Lock a row to prevent simultaneous access or editing

Post by peebee » 2016-09-28 23:51

That would be excellent and certainly much appreciated by many I'm sure.

I've already subscribed to your very informative course so I wait with great anticipation for the this new lesson. Thanks. :D

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

Re: Lock a row to prevent simultaneous access or editing

Post by peebee » 2016-10-27 23:07

We plan to introduce record locking in a future version (no particular date yet)! Meanwhile, we might add a lesson to the online customization course to explain how to do it.
Just wondering if there's been any movement on developing the record lock tutorial yet? I manage a very busy database with many Users and inconsistent data has presented as a problem due to simultaneous access to individual rows. I'm being pushed for a solution.

I've done a lot of research on how to implement row locking and none of it appears straightforward/simple without the addition on new lock tables, etc. I'm "hoping" Ahmad can weave his magic and provide some basic php/mysql commands to integrate into Appgini that will do the trick

As a happy subscriber to your Udemy course, I wait in great anticipation! :-)

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: Lock a row to prevent simultaneous access or editing

Post by grimblefritz » 2016-10-28 01:59

Multiuser database locking is not a trivial thing. From what I can see of the AG code, it would require a lot of modification to effectively implement, and even then, the design decisions required (that is, to select from among the variety of approaches) are not likely to satisfy everyone.

Unless you have an extremely busy system - in which case, AG is probably not the correct tool anyway - it might be simplest to implement some lock tables and go that route.

Just my two cents worth.

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

Re: Lock a row to prevent simultaneous access or editing

Post by peebee » 2016-10-31 23:00

I certainly get it - it's not trivial and not straight forward. That's exactly why I've asked the question of the developers.

I've looked into it at great length myself. Many options, many levels, many methods. Fortunately, Ahmad is infinitely more talented than I am and knows his application far better than I ever will. I'm sure there is a suitable method without too much hard work involved. I'm the eternal optimist. :-)

Meanwhile, I made a suggestion for a row locking tutorial on Appgini's Udemy Course. The encouraging reply from Ahmad: "Thanks for your suggestion. I'll work on a tutorial on locking records and publish it soon." For anybody not already onboard, they should invest the nominal fee and sign up for the Udemy course. Very insightful.

grimblefritz
AppGini Super Hero
AppGini Super Hero
Posts: 336
Joined: 2015-12-23 16:52

Re: Lock a row to prevent simultaneous access or editing

Post by grimblefritz » 2016-10-31 23:18

Yes, the best investment I made vis-a-vis AppGini was the Udemy course. The second best was AppGini itself. :)

AhmedBR
AppGini Super Hero
AppGini Super Hero
Posts: 327
Joined: 2013-09-19 10:23

Re: Lock a row to prevent simultaneous access or editing

Post by AhmedBR » 2022-07-07 13:35

Hi everyone, any news on this subject?
AppGini 22.14 - xampp 3.3.0 - PHP 7.4.30 - Summary reports - Calendar - Mass update - Messages - AppGiniHelper

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

Re: Lock a row to prevent simultaneous access or editing

Post by peebee » 2022-07-13 04:07

Sadly, I don't think there has been any progress in this regard since the original request and it still proves as a major potential problem.

Something similar to this link below would be ideal but after many failed attempts at trying to implement something similar myself, I've given up.

https://xlinesoft.com/phprunner/docs/au ... rd_locking

Hopefully it comes along soon. I am personally happy to contribute some $'s for somebody's time if they are able to assist?

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lock a row to prevent simultaneous access or editing

Post by jsetzer » 2022-07-13 09:40

Please stay tuned, a while ago I have written hooks-only record level locking. I have already shown my solution to Ahmed to get some feedback.
It is only I don't find the time for making it a bulletproof fully compatible out of the Box product. But, if you are interested, I can consider selling it as individual project, at first.

You can contact me by mail directly: [email protected]

I can upload screenshots when I'm back at the office next week.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

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

Re: Lock a row to prevent simultaneous access or editing

Post by peebee » 2022-07-13 23:51

Thanks Jan and yes, I'd be happy to purchase/test a solution as I believe this is the only real failure in AppGini (well, that and a strong password policy :-) ). I'll be in touch via email.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lock a row to prevent simultaneous access or editing

Post by jsetzer » 2022-07-18 07:31

If someone is interested in record-level locking to avoid simultaneus editing, please check out the following video on YouTube:

AppGini Helper - Record Lock Library*
https://youtu.be/EPK1TRPKUXM

Sample Screenshots (in German)

Record is readonly, but edit mode can be requested

mVMLZXSGNS.png
mVMLZXSGNS.png (171.38 KiB) Viewed 2225 times

Record can be edited by admin user, only

After admin has requested editing, he/she can edit it, but others cannot.

PsS5NMBKIE.png
PsS5NMBKIE.png (187.61 KiB) Viewed 2225 times

Record locked for others while admin is editing it

CSA509uCaC.png
CSA509uCaC.png (146.85 KiB) Viewed 2225 times
Other users can request editing. After admin has finished editing, record will be passed to other user.


Single-line integration

Code: Select all

function notes_dv($selectedID, $memberInfo, &$html, &$args)
{
    AppGiniHelperRecordLock::attach("notes", $selectedID, $html); 
}
Features
  • Single-line-of-PHP-code integration
  • Pure hooks-only solution, you don't have to change any generated (overwritable) files
  • Fully integrates with groups, members and permissions
  • Customizable button labels
  • Edit record (lock), if noone else is currently editing it
  • Request editing: Register for editing after current editor has finished editing
  • Cancel edit-request, if registered
  • Close (unlock) record after edit. Automatically pass record to requestor, if registered.
  • (almost) realtime updates in UI
  • (upcoming) Email notifications
Feel free to contact me by email, if you are interested.
[email protected]

Any feedback appreciated! I hope you like it!

Regards,
Jan


* Commercial note This will be a commercial product, as well as other products, that have been developed and are presented and advertised by other community members here on the forum
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lock a row to prevent simultaneous access or editing

Post by jsetzer » 2022-07-26 16:54

Hi everyone,

based on the new Record Lock Library, there is an extension for configuring collaboration workflows. If you are interested, please check out the 2nd video (links below):
  1. Record Lock Library - Introduction
    https://youtu.be/EPK1TRPKUXM
  2. Record Lock Library - Collaboration Extension
    https://youtu.be/A5vAEuEJ_IU
chrome_9SvTsRAOkK.png
chrome_9SvTsRAOkK.png (117.13 KiB) Viewed 2117 times

* Commercial note This will be a commercial product, as well as other products, that have been developed and are presented and advertised by other community members here on the forum
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply