MySQL Bugs: #87716: SELECT FOR UPDATE with BETWEEN AND gets row lock excessively.

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

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".

[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

Hello Yoshiaki,

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

Thanks,
Umesh