When operating a JSON field in MySQL 5.7, the problem of data loss is processed when concurrent execution (query, modification, reassignment) is processed (use a function JSON_ARRAY_APPEND that modifies the JSON value)
1. The json field is stored in the array format, the value example:[249, 247, 250, 244, 243, 255, 246, 245, 257, 248, 259, 252, 256]. as shown in Figure 1
![json 字段中存储的是数组格式，值示例：[249, 247, 250, 244, 243, 255, 246, 245, 257, 248, 259, 252, 256]](https://www.shuijingwanwq.com/wp-content/uploads/2023/10/1.png)
Figure 1
$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();
$fromThemeInstallationIds = json_decode($themeSaasTask->from_theme_installation_ids, true);
$fromThemeInstallationIds[] = $this->themeInstallation->originalThemeInstallation->id;
$themeSaasTask->from_theme_installation_ids = json_encode($fromThemeInstallationIds);
$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
$themeSaasTask->save();
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '393') limit 1
update `theme_saas_task` set `from_theme_installation_ids` = '[380]', `theme_saas_task`.`updated_at` = '2023-08-21 03:10:57' where `id` = 23
3. Since the queue tasks can be run at the same time, when concurrent execution, the value that needs to be added to the json field will be lost. lost[251, 253, 254, 258]. Since there are a large number of queries, modification and save operations, which are triggered at the same time point 2023-06-20 06:36:27, data is lost. as shown in Figure 2
![由于队列任务可以同时运行，在并发执行时，会导致需要添加至 json 字段中的值丢失。丢失了 [251, 253, 254, 258]。由于有大量的查询修改保存操作皆在同一时间点 2023-06-20 06:36:27 触发，进而导致了数据丢失](https://www.shuijingwanwq.com/wp-content/uploads/2023/10/2.png)
Figure 2
Figure 3
$themeSaasTask->from_theme_installation_ids = DB::raw('JSON_ARRAY_APPEND(from_theme_installation_ids, "$", ' . $this->themeInstallation->originalThemeInstallation->id . ')');
$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
$themeSaasTask->save();
update
`theme_saas_task`
set
`from_theme_installation_ids` = JSON_ARRAY_APPEND(from_theme_installation_ids, "$", 403),
`theme_saas_task`.`updated_at` = '2023-08-22 02:39:12'
where
`id` = 30
In addition to adding values to the json array, there are also cases of deleting values to the json array. The existing implementation is as follows
$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();
if ($this->themeInstallation->type == ThemeInstallation::TYPE_UPDATE && $themeSaasTask) {
$themeInstallationTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
$themeInstallationTaskIds = array_values(array_diff($themeInstallationTaskIds, [$this->themeInstallationTask->id]));
if (empty($themeInstallationTaskIds)) {
ThemeSaasTask::where('id', $themeSaasTask->id)->delete();
} else {
$themeSaasTask->theme_task_ids = json_encode($themeInstallationTaskIds);
$themeSaasTask->save();
}
}
6. When running concurrently in the queue, there are a total of 17 values in the field Theme_Task_ids. In theory, after 17 executions, `id` = 22 should be deleted. But, in fact, there is still left in the field Theme_Task_ids[380, 381, 382, 383, 385, 386, 389, 394]. The SQL executed is as follows: As shown in Figure 4
![当在队列中并发运行时，字段 theme_task_ids 中总计有 17 个值，理论上来说，执行了 17 次以后，`id` = 22 应该会被删除掉。但是，实际上，字段 theme_task_ids 中还剩下 [380, 381, 382, 383, 385, 386, 389, 394]](https://www.shuijingwanwq.com/wp-content/uploads/2023/10/4.png)
Figure 4
update `theme_saas_task` set `theme_task_ids` = '[379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395]', `theme_saas_task`.`updated_at` = '2023-08-23 01:54:15' where `id` = 22;
/* 删除掉 379 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '379') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[380,381,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22;
/* 只需要删除掉 380，但是 379 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '380') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[379,381,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22;
/* 只需要删除掉 381，但是 379,380 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '381') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[379,380,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22;
/* 只需要删除掉 384，但是 380,381 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '384') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[380,381,382,383,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:40' where `id` = 22;
7. Reference:mysql json array delete data . It is found that the parameter search_str of json_search is currently not supported by numbers. as shown in Figure 5
Figure 5
SELECT
JSON_SEARCH( theme_task_ids, 'all', '375' )
FROM
theme_saas_task
WHERE
id = 21;
8. It is finally decided to convert the value into the field Theme_Task_ids first, and convert it to a string first. The code adjustment running in the queue job is implemented as follows
$themeInstallationTaskId = '' . $this->themeInstallationTask->id . '';
$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $themeInstallationTaskId)->first();
$themeSaasTask->theme_task_ids = DB::raw('JSON_REMOVE(theme_task_ids, JSON_UNQUOTE(JSON_SEARCH(theme_task_ids, "one", ' . $themeInstallationTaskId . ')))');
$themeSaasTask->save();
update
`theme_saas_task`
set
`theme_task_ids` = '[\"516\",\"517\",\"518\",\"519\",\"520\",\"521\",\"522\",\"523\",\"524\",\"525\",\"526\",\"527\",\"528\",\"529\",\"530\",\"531\",\"532\"]',
`theme_saas_task`.`updated_at` = '2023-08-23 08:33:57'
where
`id` = 30;
"JSON_REMOVE(theme_task_ids, JSON_UNQUOTE(JSON_SEARCH(theme_task_ids, \"one\", 519)))";
9. However, in the case of concurrency, there are still some values that have not been deleted, a total of 17, 12 have been deleted, and there are still 5 left. as shown in Figure 6
Figure 6
10. Due to the low frequency of this code running, I finally decided to add a lock to achieve
$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();
if ($this->themeInstallation->type == ThemeInstallation::TYPE_UPDATE && $themeSaasTask) {
app(ThemeManager::class)->cleanTheme($this->themeInstallation, $this->themeInstallation->theme);
DB::beginTransaction();
$themeSaasTask = ThemeSaasTask::lockForUpdate()->find($themeSaasTask->id);
$themeInstallationTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
Log::info(
'$themeInstallationTaskIds3-'.$this->themeInstallationTask->id,
[$themeInstallationTaskIds]
);
$themeInstallationTaskIds = array_values(array_diff($themeInstallationTaskIds, [$this->themeInstallationTask->id]));
if (empty($themeInstallationTaskIds)) {
ThemeSaasTask::where('id', $themeSaasTask->id)->delete();
} else {
$themeSaasTask->theme_task_ids = json_encode($themeInstallationTaskIds);
Log::info(
'$themeSaasTask->theme_task_ids3-'.$this->themeInstallationTask->id,
[$themeSaasTask->theme_task_ids]
);
$themeSaasTask->save();
}
DB::commit();
}
11. Finally confirm that there is a problem with this plan, and finally leave it[567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581]. Expect an empty array. The logging records found that when the query, the queried data did not meet the expectations, the number was reduced, and the first and the 17th logs were printed out respectively.
[2023-08-24 10:10:06] local.INFO: $themeInstallationTaskIds3-567 [
[
567,
568,
569,
570,
571
]
]
[2023-08-24 10:10:06] local.INFO: $themeSaasTask->theme_task_ids3-567 [
"[568,569,570,571]"
]
[2023-08-24 10:10:09] local.INFO: $themeInstallationTaskIds3-583 [
[
567,
568,
569,
570,
571,
572,
573,
574,
575,
576,
577,
578,
579,
580,
581,
583
]
]
[2023-08-24 10:10:09] local.INFO: $themeSaasTask->theme_task_ids3-583 [
"[567,568,569,570,571,572,573,574,575,576,577,578,579,580,581]"
]
12. Finally, the root cause is found in the same record. In another place, the value is constantly added to the field Theme_Task_ids. Delete value. After deciding to add a value to the field Theme_Task_ids, then enter the queue in a unified batch.
Implementation before adjustment:
foreach ($list as $theme) {
$themeTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
$themeTaskIds[] = $themeInstallationTask->id;
$themeSaasTask->theme_task_ids = json_encode($themeTaskIds);
$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
$themeSaasTask->save();
// 推送任务到队列
InstallThemeToDb::dispatch($themeInstallationTask)->onQueue('longtime');
}
Adjusted implementation:
$themeInstallationTasks = [];
foreach ($list as $theme) {
$themeTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
$themeTaskIds[] = $themeInstallationTask->id;
$themeSaasTask->theme_task_ids = json_encode($themeTaskIds);
$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
$themeSaasTask->save();
$themeInstallationTasks[] = $themeInstallationTask;
}
foreach ($themeInstallationTasks as $themeInstallationTask) {
// 推送任务到队列
InstallThemeToDb::dispatch($themeInstallationTask)->onQueue('longtime');
}
13. After confirming the adjustment, the scheme of step 12 is feasible, and finally the remaining[]. Expected to be an empty array, in line with expectations. Prints the first and 16th and 17th logs respectively.
[2023-08-24 13:42:24] local.INFO: $themeInstallationTaskIds6-635 [
[
635,
636,
637,
638,
639,
640,
641,
642,
643,
644,
645,
646,
647,
648,
649,
650,
651
]
]
[2023-08-24 13:42:24] local.INFO: $themeSaasTask->theme_task_ids6-635 [
"[636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651]"
]
[2023-08-24 13:42:25] local.INFO: $themeInstallationTaskIds6-650 [
[
650,
651
]
]
[2023-08-24 13:42:25] local.INFO: $themeSaasTask->theme_task_ids6-650 [
"[651]"
]
[2023-08-24 13:42:26] local.INFO: $themeInstallationTaskIds6-651 [
[
651
]
]
