title: What is the most elegant way to do Concurrency Control of modifying records in Mysql?
body: <p>Ok, look at this scenario, I got a table in which users may modify many rows at the same time. I want that when someone is modifying records then other people can't modify the same record. Ex, there's table1 with 3 columns (ID, text, flag):</p>
  
  <pre>
  ID-text-flag
  11-txt1-0
  12-txt2-0
  13-txt3-0
  14-txt4-0
  </pre>
  
  <p>I did some research about Concurrency Control in Mysql, the 1st solution that people suggest is to use <code>SELECT ... FOR UPDATE</code> or <code>SELECT ... LOCK IN SHARE MODE</code>in Mysql. However, there is a limitation for this solution, that is u have to update immediately right after you select a record. For example:</p>
  
  <pre><code>SELECT text FROM table1 where ID&lt;=20 FOR UPDATE;
  UPDATE table1 SET text = 'new text' where ID&lt;=20;
  </code></pre>
  
  <p>However, my application requires users to download the data to the Gui &amp; then the users may spend many hours to work on that data before they commit to update.</p>
  
  <p>There is a second solution, Mysql provide the Row Lock mechanism, but this solution require to use InnoDB &amp; its overhead is quite high.</p>
  
  <p>Another option is to use table lock in mysql, the overhead is quite low, but we can't lock the whole table for many hours right? For example, user A may modify the record 1 to 10, user B at the same time may need to modify record 11 to 20, so the user B shouldn't wait until the user A finishes modifying.</p>
  
  <p>In my opinion, i want to have a column "flag", when user A is modifying some records, the flag of these records will turn to 1, if user B wants to modify the same records, the system will popup message saying someone is modifying it. When user A finishes, it will bring the flag to 0, &amp; user A can modify these same record. </p>
  
  <p>But there is a complicated problem for this solution, what if user A forgot to save data? if that happens then the flag is 1 forever? Then the flag should have an expired time after a few hours? &amp; how to do that? </p>
  
  <p>Maybe we need time-stamp or some mechanism to let the flag turn back to 0 after a certain time? But this is more complicated than i thought. We can't let the DB to check the time-stamp of the flag every 1 hour?</p>
  
  <p>--> I am not sure if this is the most elegant solution? I have no commercial experience in DB design &amp; i want to know how DB people manage this issue in a commercial environment?</p>
  
  <p>Can you find a better &amp; more elegant solution than all the above solutions?</p>