title: Lock user record and prevent editing?
body: <p>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 <code>User 1</code> 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 <code>User 2</code> 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:</p>
  
  <ul>
  <li>User selects the record, the automatically save this record in <code>Lock</code> table</li>
  <li>Lock table has RecID (autoincrement field), UserID(unique id for each user in the system), SelectedID (Record that user selected) and DateTime (Timestamp)</li>
  <li>Then I disable all <code>Edit</code> buttons in each form on the front end.</li>
  <li><p>On the back end I join Lock table to every update query. For example:</p>
  
  <pre><code>&lt;cfquery name="updateDemo" datasource="#dsn#"&gt;
      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
  &lt;/cfquery&gt;
  </code></pre></li>
  </ul>
  
  <p>In query above I join table that will be update to the <code>Lock</code> 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.</p>