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

**URL:** <https://forums.percona.com/t/concurrent-update-the-same-table-where-id-between-x-and-xxx-not-truly-lock-all-rows-between-id-range/33001>\
**Category:** MySQL & MariaDB\
**Tags:** mysql\
**Created:** [September 9, 2024, 2:16am UTC](https://forums.percona.com/t/concurrent-update-the-same-table-where-id-between-x-and-xxx-not-truly-lock-all-rows-between-id-range/33001 "2024-09-09T02:16:35Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![OGhuang](https://avatars.discourse-cdn.com/v4/letter/o/97f17d/32.png) [@OGhuang](https://forums.percona.com/u/OGhuang)\
**Post date:** [September 9, 2024, 2:16am UTC](https://forums.percona.com/t/concurrent-update-the-same-table-where-id-between-x-and-xxx-not-truly-lock-all-rows-between-id-range/33001/1 "2024-09-09T02:16:36Z")

</div>

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

```auto
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