Lock user record and prevent editing?

Question

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.

Answer 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.

Answer 2

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.