MySQL Bugs: #91209: SELECT FOR UPDATE is locking the very next row other than the candidate rows.

Bug #91209	SELECT FOR UPDATE is locking the very next row other than the candidate rows.
Submitted:	11 Jun 2018 10:36	Modified:	12 Jun 2018 11:41
Reporter:	Arunjith Aravindan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	Any (CentOS Linux release 7.2.1511)
Assigned to:		CPU Architecture:	Any
Tags:	select for update

Description:
SELECT FOR UPDATE is locking the very next row other than the candidate rows for the where clause. 

How to repeat:
MySQL 8.0.11 :

+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

mysql> select * from mydb.product;
+------+--------+---------+----------------+
| p_id | p_name | p_cost  | p_availability |
+------+--------+---------+----------------+
|    1 | Item1  | 10.0000 | YES            |
|    2 | Item2  | 20.0000 | YES            |
|    3 | Item3  | 30.0000 | YES            |
|    4 | Item4  | 40.0000 | YES            |
|    5 | Item5  | 50.0000 | YES            |
+------+--------+---------+----------------+
5 rows in set (0.00 sec)

Session 1: 
mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_id BETWEEN 3 and 4 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)

+------+--------+---------+----------------+
| p_id | p_name | p_cost  | p_availability |
+------+--------+---------+----------------+
|    3 | Item3  | 30.0000 | YES            |
|    4 | Item4  | 40.0000 | YES            |
+------+--------+---------+----------------+
2 rows in set (0.00 sec)

Session 2: 
mysql>  SELECT * FROM mydb.product WHERE p_id in(5)  FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

mysql> SELECT * FROM mydb.product WHERE p_id in(2,3,4,5)  FOR UPDATE SKIP LOCKED;
+------+--------+---------+----------------+
| p_id | p_name | p_cost  | p_availability |
+------+--------+---------+----------------+
|    2 | Item2  | 20.0000 | YES            |
+------+--------+---------+----------------+
1 row in set (0.00 sec)
---

Verified the same again with performance_schema.data_locks: 

mysql> SELECT object_name, index_name, lock_type, lock_mode, lock_data FROM performance_schema.data_locks WHERE object_name = 'product';
+-------------+------------+-----------+-----------+-----------+
| object_name | index_name | lock_type | lock_mode | lock_data |
+-------------+------------+-----------+-----------+-----------+
| product     | NULL       | TABLE     | IX        | NULL      |
| product     | PRIMARY    | RECORD    | X         | 3         |
| product     | PRIMARY    | RECORD    | X         | 4         |
| product     | PRIMARY    | RECORD    | X         | 5         |
+-------------+------------+-----------+-----------+-----------+
4 rows in set (0.00 sec)

In my understand it is related to https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_...

This InnoDB variable got introduced in 5.5 or 5.6, became deprecated in 5.7 and is removed in 8.0 (not listed here https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html)

I guess this means that MySQL 8.0 will now always do "gap locking" identical to previous versions with default setting for this variable.

-- Peter
-- not a MySQL/Oracle person

It something related to the next-key locking that combines index-row locking with gap locking. Gap locking locks gap forward until the next record. For example if we have 4 and 6, gap lock on 4 locks (usually) 4 itself, then the space for 5, and stops before 6. In the below test somehow the record 6 also seeing locked. 

mysql> select * from product1;
+------+--------+---------+----------------+
| p_id | p_name | p_cost  | p_availability |
+------+--------+---------+----------------+
|    1 | Item1  | 10.0000 | YES            |
|    2 | Item2  | 20.0000 | YES            |
|    3 | Item3  | 30.0000 | YES            |
|    4 | Item4  | 40.0000 | YES            |
|    6 | Item6  | 60.0000 | YES            |
|    7 | Item7  | 70.0000 | YES            |
+------+--------+---------+----------------+
6 rows in set (0.01 sec)

SESSION 1: 
mysql> START TRANSACTION;SELECT * FROM mydb.product1 WHERE p_id BETWEEN 3 and 4 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)

+------+--------+---------+----------------+
| p_id | p_name | p_cost  | p_availability |
+------+--------+---------+----------------+
|    3 | Item3  | 30.0000 | YES            |
|    4 | Item4  | 40.0000 | YES            |
+------+--------+---------+----------------+
2 rows in set (0.00 sec)

SESSION 2: 
mysql> SELECT * FROM mydb.product1 WHERE p_id in(2,3,4,5,6,7)  FOR UPDATE SKIP LOCKED;
+------+--------+---------+----------------+
| p_id | p_name | p_cost  | p_availability |
+------+--------+---------+----------------+
|    2 | Item2  | 20.0000 | YES            |
|    7 | Item7  | 70.0000 | YES            |
+------+--------+---------+----------------+
2 rows in set (0.00 sec)

---
mysql> SELECT object_name, index_name, lock_type, lock_mode, lock_data FROM performance_schema.data_locks WHERE object_name = 'product1';
+-------------+------------+-----------+-----------+-----------+
| object_name | index_name | lock_type | lock_mode | lock_data |
+-------------+------------+-----------+-----------+-----------+
| product1    | NULL       | TABLE     | IX        | NULL      |
| product1    | PRIMARY    | RECORD    | X         | 3         |
| product1    | PRIMARY    | RECORD    | X         | 4         |
| product1    | PRIMARY    | RECORD    | X         | 6         |
| product1    | PRIMARY    | RECORD    | X,GAP     | 5         |
+-------------+------------+-----------+-----------+-----------+
5 rows in set (0.00 sec)

Hi,

Thank you for your bug report. However, this is not a bug ....

This is expected and documented behaviour. All rows, or most rows, are locked in that table, because it is so small that index search is not used, while whole scanning of the table is utilised. It is simply a case where scanning is MUCH faster then index search.

Try adding many rows to the table, define a proper index and try again.