Description:
REPEATABLE READ isolation level violation on the second read from table.
(only with adaptive hash index = ON)
Example:
MySQL 5.6, 5.7, 8.0 with adaptive hash index = ON we can see this:
create table:
CREATE TABLE `t1` `id` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB");
INSERT INTO `t1` VALUES ('foo')
session 1:
set autocommit = 0; set transaction isolation level repeatable read;
select * from t1 where id = 'foo'; select * from t1 where id = 'foo';
session 2:
set autocommit = 0; set transaction isolation level repeatable read;
delete from t1 where id = 'foo'; rollback;
In this case session 1 should always see the ORIGINAL value as the second trx never commits.
Problem:
when running 2 (or more) of the above threads in parallel,
second select will show 0 rows. (first select is fine).
This only happens when adaptive hash index = ON (default).
When disabling AHI it fixes the issue.
How to repeat:
1. Run the below code.
2. Expect the issue to show up in seconds to minutes
3. Disable AHI: set global innodb_adaptive_hash_index = OFF
4. Rerun the code - no errors
How to repeat:
Java code to repeat:
RepeatableReadBug.java
=======================
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class RepeatableReadBug {
private static volatile boolean error = false;
public static void main(String[] args) throws Exception {
System.out.println("Starting test");
final DataSource dataSource = createDataSource();
setupDatabase(dataSource);
// Run delete transactions on a background thread
final Thread deleteThread = new Thread(() -> {
while (!error) {
delete(dataSource);
}
});
deleteThread.start();
// Run query transactions on the current thread until an error is observed
while (!error) {
query(dataSource);
}
deleteThread.join();
System.out.println("Done");
}
private static DataSource createDataSource() {
final MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setDatabaseName("simple");
dataSource.setUser("msandbox");
dataSource.setPassword("msandbox");
dataSource.setServerName("127.0.0.1");
dataSource.setPort(5726);
dataSource.setUseSSL(false);
return dataSource;
}
private static void setupDatabase(DataSource dataSource) throws SQLException {
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
connection.setAutoCommit(false);
statement.execute("DROP TABLE IF EXISTS `t1`");
statement.execute("CREATE TABLE `t1` (\n"
+ " `id` varchar(16) NOT NULL,\n"
+ " PRIMARY KEY (`id`)\n"
+ ") ENGINE=InnoDB");
statement.execute("INSERT INTO `t1` VALUES ('foo')");
connection.commit();
}
}
private static void delete(DataSource dataSource) {
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
statement.execute("delete from `t1` where `id` = 'foo'");
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void query(DataSource dataSource) {
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
final boolean foundOnce = t1Exists(connection);
final boolean foundSecond = t1Exists(connection);
if (!foundOnce) {
System.out.println("missed id on the first read");
error = true;
}
if (!foundSecond) {
System.out.println("missed id on the second read");
error = true;
}
connection.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
private static boolean t1Exists(Connection connection) throws SQLException {
final String sql = "SELECT * FROM `t1` WHERE `id` = 'foo'";
try (Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql)) {
return rs.next();
}
}
}
=======================
1. Download mysql-connector-java-5.1.48-bin.jar to the current dir
2. Run this:
export CLASSPATH=`pwd`/mysql-connector-java-5.1.48-bin.jar:`pwd`:$CLASSPATH
javac RepeatableReadBug.java && java RepeatableReadBug
Expected results in seconds to minutes:
$ time ./run.sh
Starting test
missed id on the second read
Done
real 0m30.383s
user 0m20.694s
sys 0m9.639s
Repeatable with bash / mysql cmd as well
Description: REPEATABLE READ isolation level violation on the second read from table. (only with adaptive hash index = ON) Example: MySQL 5.6, 5.7, 8.0 with adaptive hash index = ON we can see this: create table: CREATE TABLE `t1` `id` varchar(16) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB"); INSERT INTO `t1` VALUES ('foo') session 1: set autocommit = 0; set transaction isolation level repeatable read; select * from t1 where id = 'foo'; select * from t1 where id = 'foo'; session 2: set autocommit = 0; set transaction isolation level repeatable read; delete from t1 where id = 'foo'; rollback; In this case session 1 should always see the ORIGINAL value as the second trx never commits. Problem: when running 2 (or more) of the above threads in parallel, second select will show 0 rows. (first select is fine). This only happens when adaptive hash index = ON (default). When disabling AHI it fixes the issue. How to repeat: 1. Run the below code. 2. Expect the issue to show up in seconds to minutes 3. Disable AHI: set global innodb_adaptive_hash_index = OFF 4. Rerun the code - no errors How to repeat: Java code to repeat: RepeatableReadBug.java ======================= import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; public class RepeatableReadBug { private static volatile boolean error = false; public static void main(String[] args) throws Exception { System.out.println("Starting test"); final DataSource dataSource = createDataSource(); setupDatabase(dataSource); // Run delete transactions on a background thread final Thread deleteThread = new Thread(() -> { while (!error) { delete(dataSource); } }); deleteThread.start(); // Run query transactions on the current thread until an error is observed while (!error) { query(dataSource); } deleteThread.join(); System.out.println("Done"); } private static DataSource createDataSource() { final MysqlDataSource dataSource = new MysqlDataSource(); dataSource.setDatabaseName("simple"); dataSource.setUser("msandbox"); dataSource.setPassword("msandbox"); dataSource.setServerName("127.0.0.1"); dataSource.setPort(5726); dataSource.setUseSSL(false); return dataSource; } private static void setupDatabase(DataSource dataSource) throws SQLException { try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) { connection.setAutoCommit(false); statement.execute("DROP TABLE IF EXISTS `t1`"); statement.execute("CREATE TABLE `t1` (\n" + " `id` varchar(16) NOT NULL,\n" + " PRIMARY KEY (`id`)\n" + ") ENGINE=InnoDB"); statement.execute("INSERT INTO `t1` VALUES ('foo')"); connection.commit(); } } private static void delete(DataSource dataSource) { try (Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) { connection.setAutoCommit(false); connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); statement.execute("delete from `t1` where `id` = 'foo'"); connection.rollback(); } catch (SQLException e) { e.printStackTrace(); } } private static void query(DataSource dataSource) { try (Connection connection = dataSource.getConnection()) { connection.setAutoCommit(false); connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); final boolean foundOnce = t1Exists(connection); final boolean foundSecond = t1Exists(connection); if (!foundOnce) { System.out.println("missed id on the first read"); error = true; } if (!foundSecond) { System.out.println("missed id on the second read"); error = true; } connection.commit(); } catch (Exception e) { e.printStackTrace(); } } private static boolean t1Exists(Connection connection) throws SQLException { final String sql = "SELECT * FROM `t1` WHERE `id` = 'foo'"; try (Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery(sql)) { return rs.next(); } } } ======================= 1. Download mysql-connector-java-5.1.48-bin.jar to the current dir 2. Run this: export CLASSPATH=`pwd`/mysql-connector-java-5.1.48-bin.jar:`pwd`:$CLASSPATH javac RepeatableReadBug.java && java RepeatableReadBug Expected results in seconds to minutes: $ time ./run.sh Starting test missed id on the second read Done real 0m30.383s user 0m20.694s sys 0m9.639s Repeatable with bash / mysql cmd as well