3

while working on my new application customers came back to me with specific requirement. They would like to prevent multiple users editing same record at the same time. For example if User 1 login the application, search for the record and then select the record. There is few different forms that shows the data for selected record. For example that can be demographic form, family info, etc. Let's say we have User 2 who logged in and selects the same record. In this situation we would like to prevent both of them editing any of the forms at the same time. I'm wondering if my approach is a good fit or there is something that would work better. What I do is next:

  • User selects the record, the automatically save this record in Lock table
  • Lock table has RecID (autoincrement field), UserID(unique id for each user in the system), SelectedID (Record that user selected) and DateTime (Timestamp)
  • Then I disable all Edit buttons in each form on the front end.

  • On the back end I join Lock table to every update query. For example:

    <cfquery name="updateDemo" datasource="#dsn#">
    Update dbo.Demographic
    Set LastName = 'Kelly',
        FirstName = 'Mike',
        DOB = '07/06/1967'
    From Demographic AS D
        Left Outer Join dbo.Lock AS L
            On L.SelectedID = D.SelectedID
    Where RecID = '123132'
         And L.UserID = Session.UserID
</cfquery>

In query above I join table that will be update to the Lock table SelectedID. That way only selected record is update and we are making sure that UserID in Lock table is matching Session User ID. Users can unlock the Selected Record any time by clicking unlock button in the system. I'm not sure if this method is the best. So far I can't find any issues but this code is still in development phase. If anyone have any suggestions please let me know. I use SQL 2008, ColdFusion 2016 with JQuery and Bootstrap 3 on the front end.

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • 1
    There are 2 approaches to this problem... optimistic locking, and pessimistic locking. See https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking. Do some research and try not to reinvent the wheel. I generally use optimistic locking... it's easily implemented, and most of the time it's good enough. – Redtopia Sep 11 '18 at 14:32
  • If you actually want to lock the table from editing, I'd use an `INNER JOIN` there instead of an `OUTER JOIN`. With an `INNER JOIN`, if you aren't the User with the lock, then there isn't a record to update anyway. Since you've said you'll be working with large, high transaction tables, `INNER JOIN` may be a little quicker and more efficient. – Shawn Sep 11 '18 at 17:56

2 Answers2

1

Your solution should work but it sounds a bit complicated. I would:

  1. Add an updatedAt column to the Demographic table and update it with getdate() on save.
  2. Add the updatedAt field to your form and prefill it with the record data from the table.
  3. When updating make sure that the updatedAt field in the table equals to the updatedAt field in the form data. Throw an error if the updatedAt field in the table is newer than the updatedAt data in your form.
Yuri Vorontsov
  • 81
  • 1
  • 5
  • Yuri, your solution will populate updateAt column value from the table in the form hidden field, correct? Every time form gets populated with the data updateAt will be one that is the most recent. Then if someone else updated the record in the mean time, updateAt will change and we will be able to see the difference.This solution is less complicated but we are adding extra hit to the server. The request has to be sent in order to compare updateAt from the form with the updateAt in the table, correct? – espresso_coffee Sep 11 '18 at 13:32
  • 1
    Unless you actually care about when specifically the record was updated, you can just use a simple integer that is incremented every time an update occurs. You'll be dealing with a _much_ simpler and smaller datatype, so your increment and comparison functions are much more efficient. – Shawn Sep 11 '18 at 17:22
  • @espresso_coffee The version field can be pulled at the same time as the initial query. If multiple people pull information to update, it will be a race to see who submits first and updates the record. But this will keep Person B from overwriting Person A's changes with potentially the original information that was changed. – Shawn Sep 11 '18 at 17:26
  • @Shawn Ideally I would like to prevent user editing the form if someone else is already on that record. That way we save the users of entering the data and potentially not being able to save. The best case scenario is if user selects the record, lock that form. So if user b gets to the form they are able to view the data but not to edit. I hope this makes sense. I still do not know the best option to implement this feature. – espresso_coffee Sep 11 '18 at 17:29
  • If you want to notify User B that User A is already editing the form, then you could set a flag indicating that the record is already open for editing. But then make sure you set a timeout so that User A can't open a record and then walk away, locking the record for the next week. – Shawn Sep 11 '18 at 17:38
  • @Shawn Can you provide an example on how to achieve that? Should that be a script that runs every half an hour or some other way to do that? – espresso_coffee Sep 11 '18 at 17:43
  • If you used a timestamp to lock the record, you could just check the timestamps and remove them if they are older than x time. You would need to profile how your application is being used, and how often people are editing the same users. Then you can balance your solution. If it's not a common instance, you could just add the code to your editing page SQL calls to check and remove old locks and also run a nightly cleanup job alongside your other database jobs. – Shawn Sep 11 '18 at 18:41
1

Here is how I have solved this in the past.

For information that will need this kind of 'locking' add a numeric column named something like 'version'.

Every time a row in this table is saved, increment the value of 'version' by 1.

Include this value in any form used to edit the table.

If user A pulls up a record at the same time as user B, but user B saves the information first, the 'version' will be different when User A submits their form.

On form submit, do a check that the 'version' passed in matches the 'version' from the DB. If it does...update the table,. If it doesn't redirect the user to the form and let them know they are not updating the most recent 'version' of the data and give them an option to load the updated data.

Scott Stroz
  • 7,510
  • 2
  • 21
  • 25
  • I looked over your answer and here is one thing that I would like to point out. Your solution can cause some problems for the users. Let's say A and B user select the same record. User B updates the record, then User A tries to Submit the form. They will get the message Data/Version is different. So they have an option to A) load the current data or B) not to load the current data/not submit the form. This option would make them lose their previously entered data. I think that users would find that annoying. Correct me if this is not the case but I do not see good solution for this scenario. – espresso_coffee Sep 11 '18 at 17:23
  • If it's really important, you could set it up so that a record was merged instead of overwritten, kinda like Git. But that would be an awful lot of work for a normal user. You can create all sorts of options to check which version of the data should be edited or editable, but again, this may be a solution for a problem that doesn't really exist. That should be checked. – Shawn Sep 11 '18 at 17:34
  • 1
    What I neglected to include is that if they don't load the updated data and they resubmit....the version will still not match..so they will get the same message. For fields that may include a lot of data (like a textarea) I have even allowed them to view what they want to save compared to what is currently in the database. – Scott Stroz Sep 11 '18 at 22:02