Bug #87716 SELECT FOR UPDATE with BETWEEN AND gets row lock excessively.
Submitted: 9 Sep 2017 9:01 Modified: 13 Sep 2017 8:19
Reporter: Yoshiaki Yamasaki Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.18, 8.0.2, 5.7.19, 5.6.37 OS:Any
Assigned to: CPU Architecture:Any

[9 Sep 2017 9:01] Yoshiaki Yamasaki
Description:
SELECT FOR UPDATE with BETWEEN AND gets row lock excessively.

How to repeat:
CREATE TABLE `seats` (
  `seat_no` int(11) NOT NULL,
  `booked` enum('YES','KEEP','NO') DEFAULT 'NO',
  PRIMARY KEY (`seat_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO seats values(1,'NO');
INSERT INTO seats values(2,'NO');
INSERT INTO seats values(3,'NO');
INSERT INTO seats values(4,'NO');
INSERT INTO seats values(5,'NO');
INSERT INTO seats values(6,'NO');
INSERT INTO seats values(7,'NO');
INSERT INTO seats values(8,'NO');
INSERT INTO seats values(9,'NO');
INSERT INTO seats values(10,'NO');

[Transaction1]

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM seats WHERE seat_no BETWEEN 4 AND 6 FOR UPDATE;
+---------+--------+
| seat_no | booked |
+---------+--------+
|       4 | NO     |
|       5 | NO     |
|       6 | NO     |
+---------+--------+
3 rows in set (0.00 sec)

[Transaction2]

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE seats SET booked='NO' WHERE seat_no=7;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

* I expect Transaction1 doesn't has row lock for seat_no=7. However, Transaction1 has the lock.

Suggested fix:
"SELECT * FROM XXX WHERE XXX BETWEEN "A" AND "B" FOR UPDATE" doesn't get row lock for over "B".
[9 Sep 2017 9:04] Yoshiaki Yamasaki
[Note]
- tx_isolation='READ-COMMITTED' is no problem.
- "WHERE XXX IN" is no problem also(*).

* Example:

[Transaction1]

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM seats WHERE seat_no IN (4,5,6) FOR UPDATE;
+---------+--------+
| seat_no | booked |
+---------+--------+
|       4 | NO     |
|       5 | NO     |
|       6 | NO     |
+---------+--------+
3 rows in set (0.00 sec)

[Transaction2]

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE seats SET booked='NO' WHERE seat_no=7;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
[13 Sep 2017 8:19] MySQL Verification Team
Hello Yoshiaki,

Thank you for the report and test case.
Observed with 5.7.19.

Thanks,
Umesh