MySQL Bugs: #90592: unexpected innodb record lock

Bug #90592	unexpected innodb record lock
Submitted:	24 Apr 2018 6:42	Modified:	16 May 2018 8:25
Reporter:	yx jiang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	CentOS (6.4)
Assigned to:		CPU Architecture:	x86
Tags:	innodb, lock, record

Description:
create table t_lock(f1 int auto_increment, f2 int ,PRIMARY KEY (`f1`), key idx_f2(f2));
insert into t_lock(f2) values (5);
insert into t_lock(f2) values (17);
insert into t_lock(f2) values (23);
insert into t_lock(f2) values (29);

session1:
begin; select * from t_lock where f2 >= 5 and f2 <= 17 for update;

session2:
begin; select * from t_lock where f2 = 23 for update;

session2 will be blocked

Debuggin by GDB, I found in function rr_quick read more records which I do not expect.

Is this a special feature for effective opertion?

How to repeat:
create table t_lock(f1 int auto_increment, f2 int ,PRIMARY KEY (`f1`), key idx_f2(f2));
insert into t_lock(f2) values (5);
insert into t_lock(f2) values (17);
insert into t_lock(f2) values (23);
insert into t_lock(f2) values (29);

session1:
begin; select * from t_lock where f2 >= 5 and f2 <= 17 for update;

session2:
begin; select * from t_lock where f2 = 23 for update;

session 2 is blocked

Hi,

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

Simply, table has too few rows to warrant the use of index. It is much faster to scan the table then to jump through the index.

Not a bug.

@Sinisa Milivojevic

I debugged this case, for InnoDB, index_first is called to locate at record(5), then index_next is called 4 times to get record(17), (23), (29) and HA_ERR_END_OF_FILE. I thought rnd_init/rnd_next should be used for a full table scanning, but actually InnoDB used index_first/index_next to do full table scanning in this case. Could you give us more insight about the reason?

Another question about the primary key next-key lock.

--------------------------------------------------------------
create table t_lock2(a int primary key);
inset into t_lock2 values(5), (10), (15), (20), (25);

session 1;
begin; select * from inno15 where a>=10 and a<=20 for update;

session 2;
begin; insert into inno15 values(22);
(blocked)

session 3:
begin; insert into inno15 values(9);
(passed)
--------------------------------------------------------------

session 2 is blocked, by "show engine innodb status", the insert intention lock is waiting for the next-key lock at record(25). I don't understand why record(25) is locked but record(10) isn't here?

Then, I did another test, just change where condition a<=20 to a<20.

--------------------------------------------------------------
session 1;
begin; select * from inno15 where a>=10 and a<20 for update;

session 2;
begin; insert into inno15 values(22);
(passed)

session 3:
begin; insert into inno15 values(9);
(passed)
--------------------------------------------------------------

Why isn't record(25) locked here?

HI,

First of all, InnoDB has clustered primary key, so the reads could have gone by PK.

Second, there are so many different locks in InnoDB, including, but not limiting, to gap locks, next-key locks, insert intention locks, with locks on infimum and supremum.

This is all described in detail on many pages in our Reference Manual. Please, read it and things will be much more clear to you.

I traced more code in this case.

In function sub_select, it seems that the while loop keep reading records from index idx_f2.

  while (rc == NESTED_LOOP_OK && join->return_tab >= qep_tab_idx)
  {

Condition "join->return_tab >= qep_tab_idx" is always true during the process. So it lead to read all the records and lock them.

Trace code to function ha_innobase::unlock_row

MySQL tried to release row (23,3)'s lock. But code below in InnoDB skip this action.
================
    switch (m_prebuilt->row_read_type) {
    case ROW_READ_WITH_LOCKS:
        if (!srv_locks_unsafe_for_binlog
            && m_prebuilt->trx->isolation_level
            > TRX_ISO_READ_COMMITTED) {
            break;
        }
        /* fall through */
    case ROW_READ_TRY_SEMI_CONSISTENT:
        row_unlock_for_mysql(m_prebuilt, FALSE);
        break;
    case ROW_READ_DID_SEMI_CONSISTENT:
        m_prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT;
        break;
    }
================
So the row unsatified the query condition is locked.