title: Why does SELECT FOR UPDATE works only within a transaction?
body: <p>I think I am confused with the <a href="https://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html" rel="noreferrer">SELECT FOR UPDATE</a> construct.<br>
  Example:  </p>
  
  <pre><code>mysql&gt; select * from employees2;  
  +-------+----------+--------+-----------+  
  | EmpId | EmpName  | DeptId | EmpSalary |  
  +-------+----------+--------+-----------+  
  |     1 | John     |      1 |   5000.00 |  
  |     2 | Albert   |      1 |   4500.00 |  
  |     3 | Crain    |      2 |   6000.00 |  
  |     4 | Micheal  |      2 |   5000.00 |  
  |     5 | David    |   NULL |     34.00 |  
  |     6 | Kelly    |   NULL |    457.00 |  
  |     7 | Rudy     |      1 |    879.00 |  
  |     8 | Smith    |      2 |   7878.00 |  
  |     9 | Karsen   |      5 |    878.00 |  
  |    10 | Stringer |      5 |    345.00 |  
  |    11 | Cheryl   |   NULL |      NULL |  
  +-------+----------+--------+-----------+  
  11 rows in set (0.00 sec)  
  </code></pre>
  
  <p>I do the following in a script:   </p>
  
  <pre><code>#!/usr/bin/perl  
  use strict;  
  use warnings;  
  
  use DBI;  
  
  my $dbh = DBI-&gt;connect('dbi:mysql:testdb','root','1234', {'RaiseError' =&gt; 1, 'AutoCommit' =&gt; 0}) or die "Connection Error: $DBI::errstr\n";  
  my $sql = "select * from employees2 where EmpId IN (2,10) for update";   
  my  $sth = $dbh-&gt;prepare($sql);  
  $sth-&gt;execute or die "SQL Error: $DBI::errstr\n";  
  while (my @row = $sth-&gt;fetchrow_array) {  
     print "@row\n";  
  }   
  sleep(9000);  
  $dbh-&gt;commit;  
  </code></pre>
  
  <p>I also in parallel a console and connect to the database.<br>
  So I run the script <em>first</em> and then in another session I do:  </p>
  
  <pre><code>mysql&gt; select * from employees2 where EmpId IN (10) for update;   
  </code></pre>
  
  <p>The second select blocks as it refers to the same row.<br>
  This blocks either I do:  </p>
  
  <pre><code>mysql&gt; set autocommit = 0; 
  mysql&gt; begin;   
  mysql&gt; select * from employees2 where EmpId IN (10) for update;   
  mysql&gt; commit;     
  </code></pre>
  
  <p>or just  </p>
  
  <pre><code>mysql&gt; select * from employees2 where EmpId IN (10) for update;   
  </code></pre>
  
  <p>So it blocks irrelevant if it is in a transaction or not.<br>
  Now if I change the script as:  </p>
  
  <pre><code>my $dbh = DBI-&gt;connect('dbi:mysql:practice','root','') or die "Connection Error: $DBI::errstr\n";  
  </code></pre>
  
  <p>I.e the script does <strong>not</strong> run within a transaction the second session does not block!<br>
  Why does it block <strong>only</strong> if the script runs within a transaction?</p>