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

[15 Feb 2019 4:58] Masaki Oguro
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)
[18 Feb 2019 6:19] MySQL Verification Team
Hello Masaki,

Thank you for the report.

regards,
Umesh
[4 Jan 2021 5:05] Tsubasa Tanaka
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)
[25 Feb 2021 7:04] Sok Ann Yap
Sounds like the same issue as https://bugs.mysql.com/bug.php?id=98642, fixed in 5.7.32