I use mysql to deal some data use multi-thread, first I search the data use id range, like:
select id
from xxx
where id between 1 and 1000
and accountant_time = '2021-05-31 00:00:00'
and enter_accounts_state = 1
and enter_ce_state = 2
and ebs_summary_state = 2
and is_del = 0
result id like '1, 2, 3, 4, 5, ... 1001'.
and second I will delete these match data with addtional condition confirm_state from table like sql below:
DELETE
FROM xxx
WHERE confirm_state = 3
AND id IN ( 1, 2, 3, 4, ..., 1001);
All of the id range no intersection。
I found that some thread need return 1001 rows, but only returned the first row, I tried several times use same code and same data, but the left data also not same, the common feature is only return first row of that batch count which need return all。
When I add for update for the select sql, it works normal,
How can I understand what happens?
