Concurrent Update the same table, "where id between x and xxx" not truly lock all rows between id range~?

MySQL & MariaDB
1

Hi team,

I’m testing Concurrent Update scenario by MySQL 8.0.31 (all default parameters with binlog enabled) in my lab, hoping to consult with team one concept.

Step0: I have prepared sample data for id 1 ~ 5000000 for following test.huge table

CREATE TABLE test.`huge` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0;

mysql> use test;
set global innodb_lock_wait_timeout=3600;

Step1, Trn1: this update takes around 1min
mysql> update huge set name=‘name_3’ where id between 1 and 5000000;

Step2, Trn2: start before Trn1 is done
mysql> update huge set name=‘name_new’ where id=5000000; → DONE, no lock?
mysql> update huge set name=‘name_new’ where id=1; → Lock, Waiting until Trn1 is done

Step3, Check the result
Select id,name from huge where id in (1,5000000);
±--------±---------+
| id | name |
±--------±---------+
| 1 | name_new |
| 5000000 | name_3 | → id=5000000, name = name_3 is still updated by Trn1
±--------±---------+

Question:

  • Hoping to consult with team the concept of “update huge set name=‘name_new’ where id=5000000;” could be done without being locked by Trn1~?
  • Looks like it’s based on row-level lock, Trn2 row id=5000000 is not locked yet when Trn1 just starts the update.
  • Is there a document or the type of lock to explain above MySQL lock behavior~?

Truly appreciate it.

Regards,
OG