title: How can I make a row being read wait until a lock on it is released using JDBC and MySQL?
body: <p>So I have this small example..</p>
  
  <pre><code>import java.sql.Connection;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  import static java.sql.DriverManager.getConnection;
  
  public class Bar {
      public static void main(String[] args) throws SQLException, InterruptedException {
          final long start = System.currentTimeMillis();
          final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
          final Statement statement = connection.createStatement();
          statement.executeUpdate("UPDATE actor SET first_name = 'bar' WHERE last_name = 'tugay'");
          statement.close();
          connection.close();
          final long end = System.currentTimeMillis();
          System.out.println("Took: " + (end - start)); // Will print around 350ms
      }
  }
  </code></pre>
  
  <p>and when I execute this small piece of code, it will print out values around 350 ~ 400 milliseconds, which is fine.</p>
  
  <p>Now when I first start the following code..</p>
  
  <pre><code>import java.sql.*;
  
  import static java.sql.DriverManager.*;
  
  public class Foo {
  
      public static void main(String[] args) {
          final Connection connection;
          try {
              connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
              connection.setAutoCommit(false);
  
              final Statement statement = connection.createStatement();
              statement.executeUpdate("UPDATE actor SET first_name = 'foo' WHERE last_name = 'tugay'");
  
              System.out.println("Sleeping!");
              Thread.sleep(15000); // Sleep for 15 seconds..
  
              connection.commit();
              connection.close();
          } catch (SQLException | InterruptedException e) {
              e.printStackTrace();
          }
      }
  }
  </code></pre>
  
  <p>and then run Bar.java, I will get values around 12 - 13 seconds, which means while Foo.java "locks" the rows where lastname = 'tugay', Bar.java just waits, and then sets the first_name to 'bar'.</p>
  
  <p>I would like to get same behavior if Bar.java tries to read rows where last_name = 'tugay'. So this is the code I have:</p>
  
  <pre><code>import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  import static java.sql.DriverManager.getConnection;
  
  public class Bar {
      public static void main(String[] args) throws SQLException, InterruptedException {
          final long start = System.currentTimeMillis();
          final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
          final Statement statement = connection.createStatement();
          final ResultSet resultSet = statement.executeQuery("SELECT first_name FROM actor WHERE last_name = 'tugay'");
          resultSet.next();
          System.out.println(resultSet.getString("first_name"));
          statement.close();
          connection.close();
          final long end = System.currentTimeMillis();
          System.out.println("Took: " + (end - start));
      }
  }
  </code></pre>
  
  <p>Given, initally the value first_name is "koray" in the database, when I start Foo.java and while it "sleeps", when I run Bar.java I will get:</p>
  
  <pre><code>koray
  Took: 390
  </code></pre>
  
  <p>Is there a way to make Bar.java wait while reading as well, just like it waits when updating?</p>