Details
-
Bug
-
Status: Done
-
High
-
Resolution: Fixed
-
5.7.24-26, 8.0.13-3, 5.6.46-86.2
Description
##how to reproduce:
test table definition:
```sql
CREATE TABLE `extra` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`a` varchar(36) NOT NULL ,
`b` varchar(128) NOT NULL ,
`c` varchar(10240) NOT NULL ,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_idx` (`a`,`b`),
) ENGINE=ROCKSDB
```
run several threads concurrently, each run the following logic loops for several times:
```sql
A: insert into extra(a, b, c) values (?,'b2','c2'),(?,'b3','c3'),(?,'b4','tc4'),(?,'b5','c5') on duplicate key update c=values(c);
B: insert into extra(a, b, c) values (?, 'b1', 'c1'), (?,'b2','c2'),(?,'b3','c3'),(?,'b4','tc4'),(?,'b5','c5') on duplicate key update c=values(c);
```
The value of field a is generated in random among different threads, but same in sql A and sql B in one loop.
Unexpected result:
in some rows, the value of field c is not c1 when b = b1
-
- Dive into the code
when sql B is running after sql A:(sql B has 5 rows to be inserted)
- Dive into the code
- row 1 will be success
- row 2,3,4,5 will be duplicated
since rocksdb reserve only one increment value each call(see ha_rocksdb::get_auto_increment), row 1 maybe get auto incr value 100, row 2 maybe get auto inc value 110(values between 100 and 110 fetched by other threads).
when row 2 is duplicated in sql_insert.cc:write_record,
table->file->restore_auto_increment(prev_insert_id); will be called, and prev_insert_id = 100;
then when row 3 fetch auto incr (handler::update_auto_increment), next_insert_id is 101, and auto_inc_interval_for_cur_row.maximum() is 110(since row 2 called), so the logic will be incorrenctly, 101 will be used by row 3.
since 101 is used by other thread, row 3 using this auto inc val will be duplicated on the auto inc key incorrectly, and row 3 will update the row whose auto inc val is 101.
this will lead to data updated incorrectly.
-
- innodb
this case will not happen with innodb, because innodb will reserve the auto incr values needed, Since sql B has 5 rows, 100 ~ 104 will be reserved by this thread.
- innodb