Description:
In our tests, we found that in transaction TX2 under SERIALIZABLE isolation level, adding the statement "SELECT c1, c2 FROM mtest WHERE x = 0;" blocks TX1's UPDATE. We believe this statement should not block TX1's update because the rows it queries are different from the rows TX1 is updating.
How to repeat:
conn_0> CREATE TABLE mtest(x INT DEFAULT 0,c0 INT, c1 INT, c2 INT UNIQUE AUTO_INCREMENT);
conn_0> INSERT IGNORE INTO mtest(c0, c1, c2, x) VALUES (1, 1, 1, 1);
conn_0> INSERT INTO mtest(c0, c1, c2) VALUES (1, 1, NULL);
+---+-----+----+----+
| x | c0 | c1 | c2 |
+---+-----+----+----+
| 1 | 1 | 1 | 1 |
+---+-----+----+----+
| 0 | 1 | 1 | 2 |
+---+-----+----+----+
---------- Test case 0 ----------
conn_0> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.000 sec)
conn_1> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.000 sec)
conn_1> BEGIN;
Query OK, 0 rows affected (0.000 sec)
conn_0> BEGIN;
Query OK, 0 rows affected (0.000 sec)
conn_1> SELECT c1, c2 FROM mtest WHERE c2 = 2 FOR UPDATE;
+------+----+
| c1 | c2 |
+------+----+
| 1 | 2 |
+------+----+
1 row in set (0.001 sec)
conn_0> UPDATE mtest SET c0=2, c1=3 WHERE c2 = 1;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
conn_0> ROLLBACK;
Query OK, 0 rows affected (0.011 sec)
conn_1> ROLLBACK;
Query OK, 0 rows affected (0.000 sec)
---------- Test case 1 ----------
conn_0> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.000 sec)
conn_1> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.000 sec)
conn_1> BEGIN;
Query OK, 0 rows affected (0.000 sec)
conn_0> BEGIN;
Query OK, 0 rows affected (0.000 sec)
conn_0> SELECT c1, c2 FROM mtest WHERE c2 = 2 FOR UPDATE;
+------+----+
| c1 | c2 |
+------+----+
| 1 | 2 |
+------+----+
1 row in set (0.001 sec)
conn_0> SELECT c1, c2 FROM mtest WHERE x = 0;
+------+----+
| c1 | c2 |
+------+----+
| 1 | 2 |
+------+----+
1 row in set (0.000 sec)
MySQL [test]> UPDATE mtest SET c0=2, c1=3 WHERE c2 = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
**Unexpected blocked statement**
Suggested fix:
The statement "SELECT c1, c2 FROM mtest WHERE x = 0;" should only acquire a shared lock on the row(s) where x=0.
