<p>I have designed database tables (normalised, on an MS SQL server) and created a standalone windows front end for an application that will be used by a handful of users to add and edit information. We will add a web interface to allow searching accross our production area at a later date.</p>

<p>I am concerned that if two users start editing the same record then the last to commit the update would be the 'winner' and important information may be lost. A number of solutions come to mind but I'm not sure if I am going to create a bigger headache.</p>

<ol>
<li>Do nothing and hope that two users are never going to be editing the same record at the same time. <em>- Might never happed but what if it does?</em></li>
<li>Editing routine could store a copy of the original data as well as the updates and then compare when the user has finished editing. If they differ show user and comfirm update <em>- Would require two copies of data to be stored.</em></li>
<li>Add last updated DATETIME column and check it matches when we update, if not then show differences. <em>- requires new column in each of the relevant tables.</em></li>
<li>Create an editing table that registers when users start editing a record that will be checked and prevent other users from editing same record. <em>- would require carful thought of program flow to prevent deadlocks and records becoming locked if a user crashes out of the program.</em></li>
</ol>

<p>Are there any better solutions or should I go for one of these?</p>

[{'answer_id': 838, 'author': 'Dave Ward', 'author_id': 60, 'author_profile': 'https://Stackoverflow.com/users/60', 'pm_score': 5, 'selected': True, 'text': '<p>If you expect infrequent collisions, <a href="http://msdn.microsoft.com/en-us/library/aa0416cz.aspx" rel="noreferrer">Optimistic Concurrency</a> is probably your best bet.</p>\n\n<p>Scott Mitchell wrote a comprehensive tutorial on implementing that pattern:<br>\n<a href="http://www.asp.net/Learn/data-access/tutorial-21-cs.aspx" rel="noreferrer">Implementing Optimistic Concurrency</a></p>\n'}, {'answer_id': 981, 'author': 'Mark Harrison', 'author_id': 116, 'author_profile': 'https://Stackoverflow.com/users/116', 'pm_score': 0, 'selected': False, 'text': '<p>The database will do this for you.  Look at "select ... for update", which is designed just for this kind of thing.  It will give you a write lock on the selected rows, which you can then commit or roll back.</p>\n'}, {'answer_id': 1620, 'author': 'karlgrz', 'author_id': 318, 'author_profile': 'https://Stackoverflow.com/users/318', 'pm_score': 2, 'selected': False, 'text': '<p>@ Mark Harrison : SQL Server does not support that syntax (<code>SELECT ... FOR UPDATE</code>).</p>\n\n<p>The SQL Server equivalent is the <code>SELECT</code> statement hint <code>UPDLOCK</code>.</p>\n\n<p>See <a href="http://msdn.microsoft.com/en-us/library/ms187373.aspx" rel="nofollow noreferrer">SQL Server Books Online</a> for more information.</p>\n'}, {'answer_id': 10437, 'author': 'Guy', 'author_id': 993, 'author_profile': 'https://Stackoverflow.com/users/993', 'pm_score': 1, 'selected': False, 'text': "<p>Another option is to test that the values in the record that you are changing are the still the same as they were when you started: </p>\n\n<pre><code>SELECT \n    customer_nm,\n    customer_nm AS customer_nm_orig\nFROM demo_customer\nWHERE customer_id = @p_customer_id\n</code></pre>\n\n<p>(display the customer_nm field and the user changes it)</p>\n\n<pre><code>UPDATE demo_customer\nSET customer_nm = @p_customer_name_new\nWHERE customer_id = @p_customer_id\nAND customer_name = @p_customer_nm_old\n\nIF @@ROWCOUNT = 0\n    RAISERROR( 'Update failed: Data changed' );\n</code></pre>\n\n<p>You don't have to add a new column to your table (and keep it up to date), but you do have to create more verbose SQL statements and pass <em>new</em> and <em>old</em> fields to the stored procedure.</p>\n\n<p>It also has the advantage that you are not locking the records - because we all know that records will end up staying locked when they should not be...</p>\n"}, {'answer_id': 156358, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': "<p>SELECT FOR UPDATE and equivalents are good providing you hold the lock for a microscopic amount of time, but for a macroscopic amount (e.g. the user has the data loaded and hasn't pressed 'save' you should use optimistic concurrency as above.  (Which I always think is misnamed - it's more pessimistic than 'last writer wins', which is usually the only other alternative considered.)</p>\n"}, {'answer_id': 156759, 'author': 'AJ.', 'author_id': 7211, 'author_profile': 'https://Stackoverflow.com/users/7211', 'pm_score': 2, 'selected': False, 'text': '<p>A classic approach is as follows: </p>\n\n<ul>\n<li>add a boolean field , "locked" to each table.  </li>\n<li>set this to false by default. </li>\n<li><p>when a user starts editing, you do this: </p>\n\n<ul>\n<li>lock the row (or the whole table if you can\'t lock the row) </li>\n<li>check the flag on the row you want to edit</li>\n<li>if the flag is true then \n\n<ul>\n<li>inform the user that they cannot edit that row at the moment</li>\n</ul></li>\n<li>else\n\n<ul>\n<li>set the flag to true</li>\n</ul></li>\n<li><p>release the lock</p></li>\n<li><p>when saving the record, set the flag back to false</p></li>\n</ul></li>\n</ul>\n'}, {'answer_id': 6464596, 'author': 'Linh danh thue', 'author_id': 573668, 'author_profile': 'https://Stackoverflow.com/users/573668', 'pm_score': 0, 'selected': False, 'text': '<p>With me, the best way i have a column lastupdate (timetamp datatype).\nwhen select and update just compare this value\nanother advance of this solution is that you can use this column to track down the time data has change.\nI think it is not good if you just create a colum like isLock for check update.</p>\n'}, {'answer_id': 38372873, 'author': 'mohamed elbagory', 'author_id': 6589135, 'author_profile': 'https://Stackoverflow.com/users/6589135', 'pm_score': 2, 'selected': False, 'text': '<p>-first create filed (update time) to store last update record\n-when any user select record save select time,\ncompare between select time and update time field if( update time) > (select time) that mean another user update this record after select record</p>\n'}]
['https://Stackoverflow.com/questions/833', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/186/']