title: MySQL 'select for update' behaviour
body: <p>As per the MySql documentation, MySql supports Multiple granularity locking(MGL).</p>
  
  <h1>case-1</h1>
  
  <p>Opened terminal-1:</p>
  
  <p>// connected to mysql</p>
  
  <pre><code>mysql&gt; start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select id, status from tracking_number limit 5 for update;
  +----+--------+
  | id | status |
  +----+--------+
  |  1 |      0 |
  |  2 |      0 |
  |  3 |      0 |
  |  4 |      0 |
  |  5 |      0 |
  +----+--------+
  5 rows in set (0.00 sec)
  mysql&gt; 
  </code></pre>
  
  <p>left it opened and opened terminal-2:</p>
  
  <p>// connected to mysql</p>
  
  <pre><code>mysql&gt; start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select id, status from tracking_number limit 5 for update;
  
  &lt;!-- Hangs here. and after some time it says--&gt;
  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  </code></pre>
  
  <p>Though there are plenty of rows to retrieve, T2 waits until t1 completes.</p>
  
  <h1>case-2</h1>
  
  <p>Left terminal-1 as is.Now in terminal-2:</p>
  
  <pre><code>mysql&gt; start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  &lt;!-- case 2.1 --&gt;
  mysql&gt; select id, status from tracking_number where id=1;
  +----+--------+
  | id | status |
  +----+--------+
  |  1 |      0 |
  +----+--------+
  1 row in set (0.00 sec)
  
  mysql&gt; select id, status from tracking_number where id=2;
  +----+--------+
  | id | status |
  +----+--------+
  |  2 |      0 |
  +----+--------+
  1 row in set (0.00 sec)
  
  &lt;!-- case 2.2 --&gt;
  mysql&gt; select * from tracking_number where id=2 for update;
  &lt;!-- Hangs here. and after some time --&gt;
  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  </code></pre>
  
  <ol>
  <li><p>But why in case 1, T2 waits for the same set of rows that T1 has locked?</p></li>
  <li><p>Does it mean the unbounded select query (even with limint parameter. I have tried with different range also) blocks the entire table?</p></li>
  <li>Is there any way to let transactions to lock independently without specifying the field of the record(i.e., without using <strong>where field=value</strong>)?</li>
  <li>Generally (or as per Java concurrent locking), write lock is exclusive and read is not. In case 2.1, though the records are in write lock mode, how T2 can read the same records? Since this is allowed what is the point in locking it?</li>
  <li>Case 2.2 is understood. </li>
  </ol>
  
  <p>Opened a terminal and a transaction:</p>
  
  <pre><code>mysql&gt; update tracking_number set status=4 where status=0 limit 5;
  Query OK, 5 rows affected (0.00 sec)
  Rows matched: 5  Changed: 5  Warnings: 0
  </code></pre>
  
  <p>Left it there and opened another terminal and transaction:</p>
  
  <pre><code>mysql&gt; update tracking_number set status=5 where status=0 limit 5; 
  </code></pre>
  
  <p>T2 did not succeed until i committed (or rollback) T1.</p>
  
  <ol>
  <li>Why is this behavior?</li>
  </ol>