title: Mysql for update
body: <p>MySQL supports "for update" keyword. Here is how I tested that it is working as expected. I opened 2 browser tabs and executed the following commands in one window.</p>
  
  <pre><code>mysql&gt; start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select * from myxml where id = 2 for update;
  ....
  mysql&gt; update myxml set id = 3 where id = 2 limit 1;
  Query OK, 1 row affected, 1 warning (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql&gt; commit;
  Query OK, 0 rows affected (0.08 sec)
  </code></pre>
  
  <hr>
  
  <p>In another window, I started the transaction and tried to take an update lock on the same record.</p>
  
  <pre><code>mysql&gt; start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select * from myxml where id = 2 for update;
  Empty set (43.81 sec)
  </code></pre>
  
  <p>As you can see from the above example, I could not select the record for 43 seconds as the transaction was being processed by another application in the Window No 1. Once the transaction was over, I got to select the record, but since the id 2 was changed to id 3 by the transaction that was executed first, no record was returned.</p>
  
  <p>My question is what are the disadvantages of using "for update" syntax? If I do not commit the transaction that is running in window 1 will the record be locked for-ever?</p>