MySQL Bugs: #94338: Dirty read-like behavior in READ COMMITTED transaction

Bug #94338	Dirty read-like behavior in READ COMMITTED transaction
Submitted:	15 Feb 2019 4:58	Modified:	18 Feb 2019 6:19
Reporter:	Masaki Oguro	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.25, 5.6.43	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
There is a SELECT query expected to return empty result.
But the query returns a row after another transaction executes INSERT statement.
After COMMIT or ROLLBACK of the transaction, the query returns empty again.
Both transaction isolation levels are READ COMMITTED.

I have tested on some official docker images and the problem reproduced in 5.7.25 but not in 8.0.0.

How to repeat:
-- Preparation
mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.7.25    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `t1` (
  `a_id` bigint(20) NOT NULL,
  `b_id` bigint(20) NOT NULL
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE `t2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a_id` bigint(20) NOT NULL,
  `b_id` bigint(20) NOT NULL,
  `c_code` char(1) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `c_id` bigint(20) NOT NULL,
  `state` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `secondary` (`a_id`,`b_id`,`c_code`,`c_id`,`state`)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 (a_id, b_id) VALUES (1,18), (1,19);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 (a_id, b_id, c_code, c_id, state) VALUES
  (1,18,'B',10,1),
  (1,19,'B',10,1),
  (1,20,'B',10,1),
  (1,21,'B',10,1),
  (1,18,'B',11,0),
  (1,19,'B',11,0),
  (1,20,'B',11,0),
  (1,21,'B',11,0),
  (1,18,'C',12,1),
  (1,19,'C',12,1),
  (1,21,'C',12,1),
  (1,18,'C',13,1);
Query OK, 12 rows affected (0.02 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE t1, t2;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
| test.t2 | analyze | status   | OK       |
+---------+---------+----------+----------+
2 rows in set (0.05 sec)

-- Transaction 1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

-- Transaction 2
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

-- Transaction 1
mysql> SELECT t1.b_id
  FROM t1
  WHERE (
    t1.a_id = 1
    AND IFNULL(1 = (
      SELECT state
      FROM t2 FORCE INDEX(secondary)
      WHERE
        a_id = 1
        AND b_id = t1.b_id
        AND ((c_code = 'A' AND c_id = 11) OR (c_code = 'B' AND c_id = 11))
      ORDER BY state LIMIT 1), 1))
  LIMIT 10;
Empty set (0.00 sec)

-- Transaction 2
mysql> INSERT INTO t2 (a_id, b_id, c_code, c_id, state) VALUES
  (1,22,'B',10,1),
  (1,23,'B',10,1),
  (1,24,'B',10,1),
  (1,25,'B',10,1),
  (1,26,'B',10,1),
  (1,27,'B',10,1),
  (1,28,'B',10,1),
  (1,29,'B',10,1),
  (1,30,'B',10,1),
  (1,31,'B',10,1),
  (1,32,'B',10,1),
  (1,33,'B',10,1),
  (1,34,'B',10,1),
  (1,35,'B',10,1),
  (1,36,'B',10,1),
  (1,37,'B',10,1),
  (1,38,'B',10,1),
  (1,39,'B',10,1),
  (1,40,'B',10,1),
  (1,41,'B',10,1),
  (1,42,'B',10,1),
  (1,43,'B',10,1),
  (1,44,'B',10,1),
  (1,45,'B',10,1),
  (1,46,'B',10,1),
  (1,22,'B',11,0),
  (1,23,'B',11,0),
  (1,24,'B',11,0),
  (1,25,'B',11,0),
  (1,26,'B',11,0),
  (1,27,'B',11,0),
  (1,28,'B',11,0),
  (1,29,'B',11,0),
  (1,30,'B',11,0),
  (1,31,'B',11,0),
  (1,32,'B',11,0),
  (1,33,'B',11,0),
  (1,34,'B',11,0),
  (1,35,'B',11,0),
  (1,36,'B',11,0),
  (1,37,'B',11,0),
  (1,38,'B',11,0),
  (1,39,'B',11,0),
  (1,40,'B',11,0),
  (1,41,'B',11,0),
  (1,42,'B',11,0),
  (1,43,'B',11,0),
  (1,44,'B',11,0),
  (1,45,'B',11,0),
  (1,46,'B',11,0),
  (1,23,'C',12,1),
  (1,25,'C',12,1),
  (1,26,'C',12,1),
  (1,29,'C',12,1),
  (1,32,'C',12,1),
  (1,34,'C',12,1),
  (1,35,'C',12,1),
  (1,36,'C',12,1),
  (1,37,'C',12,1),
  (1,38,'C',12,1),
  (1,39,'C',12,1),
  (1,42,'C',12,1),
  (1,43,'C',12,1),
  (1,44,'C',12,1),
  (1,46,'C',12,1),
  (1,28,'C',13,1),
  (1,29,'C',13,1),
  (1,31,'C',13,1),
  (1,33,'C',13,1),
  (1,35,'C',13,1),
  (1,36,'C',13,1),
  (1,38,'C',13,1),
  (1,39,'C',13,1),
  (1,43,'C',13,1),
  (1,46,'C',14,1),
  (1,22,'C',15,1),
  (1,24,'C',15,1),
  (1,27,'C',15,1),
  (1,28,'C',15,1),
  (1,30,'C',15,1),
  (1,31,'C',15,1),
  (1,33,'C',15,1),
  (1,40,'C',15,1),
  (1,41,'C',15,1),
  (1,45,'C',15,1),
  (1,24,'C',16,1),
  (1,26,'C',16,1),
  (1,27,'C',16,1),
  (1,34,'C',16,1),
  (1,40,'C',16,1),
  (1,42,'C',16,1);
Query OK, 91 rows affected (0.00 sec)
Records: 91  Duplicates: 0  Warnings: 0

-- Transaction 1
mysql> SELECT t1.b_id
  FROM t1
  WHERE (
    t1.a_id = 1
    AND IFNULL(1 = (
      SELECT state
      FROM t2 FORCE INDEX(secondary)
      WHERE
        a_id = 1
        AND b_id = t1.b_id
        AND ((c_code = 'A' AND c_id = 11) OR (c_code = 'B' AND c_id = 11))
      ORDER BY state LIMIT 1), 1))
  LIMIT 10;
+------+
| b_id |
+------+
|   19 |
+------+
1 row in set (0.00 sec)

-- Transaction 2
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

-- Transaction 1
mysql> SELECT t1.b_id
  FROM t1
  WHERE (
    t1.a_id = 1
    AND IFNULL(1 = (
      SELECT state
      FROM t2 FORCE INDEX(secondary)
      WHERE
        a_id = 1
        AND b_id = t1.b_id
        AND ((c_code = 'A' AND c_id = 11) OR (c_code = 'B' AND c_id = 11))
      ORDER BY state LIMIT 1), 1))
  LIMIT 10;
Empty set (0.00 sec)

Hello Masaki,

Thank you for the report.

regards,
Umesh

I seem 5.7.32 returns expected result.
Was there any fix? (I couldn't find in release-note)

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-32.html

mysql> SELECT t1.b_id   FROM t1   WHERE (     t1.a_id = 1     AND IFNULL(1 = (       SELECT state       FROM t2 FORCE INDEX(secondary)       WHERE         a_id = 1         AND b_id = t1.b_id         AND ((c_code = 'A' AND c_id = 11) OR (c_code = 'B' AND c_id = 11))
    ORDER BY state LIMIT 1), 1))   LIMIT 10;
+------+
| b_id |
+------+
|   19 |
+------+
1 row in set (0.00 sec)

mysql> SELECT @@version; -- Issue still appears
+-----------+
| @@version |
+-----------+
| 5.7.31    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT t1.b_id   FROM t1   WHERE (     t1.a_id = 1     AND IFNULL(1 = (       SELECT state       FROM t2 FORCE INDEX(secondary)       WHERE         a_id = 1         AND b_id = t1.b_id         AND ((c_code = 'A' AND c_id = 11) OR (c_code = 'B' AND c_id = 11))       ORDER BY state LIMIT 1), 1))   LIMIT 10;
Empty set (0.00 sec)

mysql> SELECT @@version; -- Fixed?
+-----------+
| @@version |
+-----------+
| 5.7.32    |
+-----------+
1 row in set (0.00 sec)

Sounds like the same issue as https://bugs.mysql.com/bug.php?id=98642, fixed in 5.7.32