Why does SELECT FOR UPDATE works only within a transaction?

Question

I think I am confused with the SELECT FOR UPDATE construct.
Example:

mysql> 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)  

I do the following in a script:

#!/usr/bin/perl  
use strict;  
use warnings;  

use DBI;  

my $dbh = DBI->connect('dbi:mysql:testdb','root','1234', {'RaiseError' => 1, 'AutoCommit' => 0}) or die "Connection Error: $DBI::errstr\n";  
my $sql = "select * from employees2 where EmpId IN (2,10) for update";   
my  $sth = $dbh->prepare($sql);  
$sth->execute or die "SQL Error: $DBI::errstr\n";  
while (my @row = $sth->fetchrow_array) {  
   print "@row\n";  
}   
sleep(9000);  
$dbh->commit;  

I also in parallel a console and connect to the database.
So I run the script first and then in another session I do:

mysql> select * from employees2 where EmpId IN (10) for update;   

The second select blocks as it refers to the same row.
This blocks either I do:

mysql> set autocommit = 0; 
mysql> begin;   
mysql> select * from employees2 where EmpId IN (10) for update;   
mysql> commit;     

or just

mysql> select * from employees2 where EmpId IN (10) for update;   

So it blocks irrelevant if it is in a transaction or not.
Now if I change the script as:

my $dbh = DBI->connect('dbi:mysql:practice','root','') or die "Connection Error: $DBI::errstr\n";  

I.e the script does not run within a transaction the second session does not block!
Why does it block only if the script runs within a transaction?

Solution

According to the documentation:

Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.

In other words, if you don't execute your first SELECT FOR UPDATE inside a transaction, no rows are locked.

Comments

[ThisSuitIsBlackNot]

To answer your new question: SELECT ... FOR UPDATE only locks rows until you commit the transaction in which it is executed. autocommit is enabled by default, so when you don't explicitly disable it, every SQL statement you execute forms a separate transaction (with InnoDB, that is). In other words, your first SELECT ... FOR UPDATE is executed and then immediately committed, releasing the locks.

[91DarioDev]

What about if instead it’s a single query but the for update is part of a subquery of an update query? In that case the query is only one and not in a transaction but I guess it should work because the commit is done only after the update statement. And it is the parent of the select subquery having the for update