Bug #90675 Improper Record lock With Primary Key. where key >= or <= N

• Submitted: 28 Apr 2018 7:30
• Modified: 2 May 2018 13:34
• Reporter: ashe sun (OCA)
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: mysql-5.7
• OS: Any
• CPU Architecture: Any

[28 Apr 2018 7:30] ashe sun

Description:
mysql> show create table ashe\G
*************************** 1. row ***************************
       Table: ashe
Create Table: CREATE TABLE `ashe` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from ashe;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
| 10 | a    |
| 11 | a    |
+----+------+
6 rows in set (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> select @@innodb_locks_unsafe_for_binlog;
+----------------------------------+
| @@innodb_locks_unsafe_for_binlog |
+----------------------------------+
|                                0 |
+----------------------------------+

scene1

session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ashe where id<=4 for update;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set (0.00 sec)

show engine innodb status\G
.........
---TRANSACTION 1170261, ACTIVE 14 sec
2 lock struct(s), heap size 1160, 5 row lock(s)
.........

5 row locks,it is id=1,2,3,4,10; There should not be a record lock on id=10;

validation：
session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from ashe where id=10 for update;
(lock wait)

scene2
session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ashe where id <=4 order by id desc for update;
+----+------+
| id | name |
+----+------+
|  4 | d    |
|  3 | c    |
|  2 | b    |
|  1 | a    |
+----+------+
4 rows in set (0.00 sec)

mysql>

session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ashe where id=10 for update;
+----+------+
| id | name |
+----+------+
| 10 | a    |
+----+------+
1 row in set (0.00 sec)

How to repeat:
As Description

[2 May 2018 13:34] MySQL Verification Team

Hi,

Thank you for your bug report. 

However, we do not think that this is a bug. You are using locking that is safe for the binary logging.  Then means, that in the first case, the next row that had to be locked is the one where column `id` is 10.

In your second query, since you are using descending ordering, the row that had to be locked is supremum pseudo-record. 

This is all properly described in our 5.7 manual, chapter 14.5.1 and other related InnoDB chapters.