Bug #101667 REPEATABLE READ isolation level violation on second read from table with AHI=ON
Submitted: 18 Nov 2020 18:24 Modified: 19 Nov 2020 10:27
Reporter: Alexander Rubin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6,5.7,8.0, 5.7.32, 8.0.22 OS:Any
Assigned to: CPU Architecture:Any

[18 Nov 2020 18:24] Alexander Rubin
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
[19 Nov 2020 10:27] MySQL Verification Team
Hello Alexander,

Thank you for the report and test case.
Verified as described with 5.7.32 build.

regards,
Umesh