Lock a row to prevent simultaneous access or editing
Lock a row to prevent simultaneous access or editing
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.
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.
Re: Lock a row to prevent simultaneous access or editing
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?
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?
Re: Lock a row to prevent simultaneous access or editing
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
created a file in hooks folder with TableName-dv.js and added below code.
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.
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>
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);
});
}
Re: Lock a row to prevent simultaneous access or editing
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?
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?
-
- Moderator
- Posts: 82
- Joined: 2013-11-11 19:21
Re: Lock a row to prevent simultaneous access or editing
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.
Re: Lock a row to prevent simultaneous access or editing
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.
I've already subscribed to your very informative course so I wait with great anticipation for the this new lesson. Thanks.
Re: Lock a row to prevent simultaneous access or editing
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.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.
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!
-
- AppGini Super Hero
- Posts: 336
- Joined: 2015-12-23 16:52
Re: Lock a row to prevent simultaneous access or editing
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.
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.
Re: Lock a row to prevent simultaneous access or editing
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.
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.
-
- AppGini Super Hero
- Posts: 336
- Joined: 2015-12-23 16:52
Re: Lock a row to prevent simultaneous access or editing
Yes, the best investment I made vis-a-vis AppGini was the Udemy course. The second best was AppGini itself.
Re: Lock a row to prevent simultaneous access or editing
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
Re: Lock a row to prevent simultaneous access or editing
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?
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?
Re: Lock a row to prevent simultaneous access or editing
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.
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lock a row to prevent simultaneous access or editing
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.
Re: Lock a row to prevent simultaneous access or editing
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
Record can be edited by admin user, only
After admin has requested editing, he/she can edit it, but others cannot.
Record locked for others while admin is editing it
Other users can request editing. After admin has finished editing, record will be passed to other user.
Single-line integration
Features
[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
AppGini Helper - Record Lock Library*
https://youtu.be/EPK1TRPKUXM
Sample Screenshots (in German)
Record is readonly, but edit mode can be requested
Record can be edited by admin user, only
After admin has requested editing, he/she can edit it, but others cannot.
Record locked for others while admin is editing it
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);
}
- 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
[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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lock a row to prevent simultaneous access or editing
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):
* 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
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):
- Record Lock Library - Introduction
https://youtu.be/EPK1TRPKUXM - Record Lock Library - Collaboration Extension
https://youtu.be/A5vAEuEJ_IU
* 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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools