update one row but lock more than one rows

CREATE TABLE `b` (

  `a` int(11) NOT NULL,

  `b` int(11) NOT NULL,

  `c` varchar(20) COLLATE utf8_bin DEFAULT NULL,

  `d` varchar(10) COLLATE utf8_bin DEFAULT NULL,

  PRIMARY KEY (`a`,`b`)

MariaDB [test]> select * from b;

+---+----+-------+------+

| a | b  | c     | d    |

+---+----+-------+------+

| 1 |  1 | 222   | 1    |

| 1 |  2 | 333   | 2    |

| 1 |  3 | 10406 | 3    |

| 1 |  4 | 60736 | 4    |

| 1 |  5 | 50170 | 5    |

| 1 |  6 | 55447 | 6    |

| 1 |  7 | 29705 | 7    |

| 1 |  8 | 13657 | 8    |

| 1 |  9 | 63638 | 9    |

| 1 | 10 | 36187 | 10   |

+---+----+-------+------+

10 rows in set (0.01 sec)

MariaDB [test]> explain update test.b set c ='111'  where b= 1;

+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

|    1 | SIMPLE      | b     | index | NULL          | PRIMARY | 8       | NULL |   10 | Using where |

+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

1 row in set (0.00 sec)

update test.b set c ='111' where b=1,2,3,4,5

---TRANSACTION 122145, ACTIVE 761 sec

3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1

MySQL thread id 31, OS thread handle 0x7f644b3ec700, query id 420 localhost root cleaning up

TABLE LOCK table `test`.`b` trx id 122145 lock mode IX

RECORD LOCKS space id 5 page no 3 n bits 192 index `PRIMARY` of table `test`.`b` trx id 122145 lock_mode X locks rec but not gap

Record lock, heap no 118 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 4; hex 80000003; asc     ;;

 2: len 6; hex 00000001dd21; asc      !;;

 3: len 7; hex 170000019518c3; asc        ;;

 4: len 3; hex 313131; asc 111;;

 5: len 1; hex 33; asc 3;;

RECORD LOCKS space id 5 page no 3 n bits 192 index `PRIMARY` of table `test`.`b` trx id 122145 lock_mode X locks rec but not gap

Record lock, heap no 99 PHYSICAL RECORD: n_fields 6; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 4; hex 80000005; asc     ;;

 2: len 6; hex 00000000383a; asc     8:;;

 3: len 7; hex 420000019a0281; asc B      ;;

 4: len 5; hex 3530313730; asc 50170;;

 5: len 1; hex 35; asc 5;;

//if had to wait ：

case DB_LOCK_WAIT:

			/* Never unlock rows that were part of a conflict. */

			prebuilt->new_rec_locks = 0;   //set new_rec_locks=0

//try semi consistent read ：

row_sel_build_committed_vers_for_mysql()

/*The lock was granted while we were

				searching for the last committed version.

				Do a normal locking read. */

//then:				

case DB_SUCCESS:

			if (clust_rec == NULL) {

				/* The record did not exist in the read view */

				ut_ad(prebuilt->select_lock_type == LOCK_NONE);

				goto next_rec;	

//only really did a semi consistent read, would set did_semi_consistent_read. but in my case,skipped this 

did_semi_consistent_read = TRUE;

//so the value of did_semi_consistent_read is FLASE,and set prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT

if (UNIV_UNLIKELY(did_semi_consistent_read)) {

	prebuilt->row_read_type = ROW_READ_DID_SEMI_CONSISTENT; 

} else {

	prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT;

}

ha_innobase::unlock_row()

	switch (prebuilt->row_read_type)

	case ROW_READ_TRY_SEMI_CONSISTENT:

		row_unlock_for_mysql(prebuilt, FALSE);

		break;

	case ROW_READ_DID_SEMI_CONSISTENT:

		prebuilt->row_read_type = ROW_READ_TRY_SEMI_CONSISTENT;

		break;

row_unlock_for_mysql()

//because prebuilt->new_rec_locks was set to 0,so the locked row can't be unlock!!

if (prebuilt->new_rec_locks >= 1) {

lock_rec_unlock()

}