1. The JSON field stores an array. Example value: [249, 247, 250, 244, 243, 255, 246, 245, 257, 248, 259, 252, 256]. See Figure 1.
![The JSON field stores an array. Example value: [249, 247, 250, 244, 243, 255, 246, 245, 257, 248, 259, 252, 256]](https://media.shuijingwanwq.com/2023/10/1.png)
2. The existing code running in the queue job is implemented as follows, and the generated SQL is as follows.
$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. Because queue tasks can run simultaneously, concurrent execution causes values that need to be added to the JSON field to be lost. The values [251, 253, 254, 258] were lost. A large number of query, modify, and save operations were triggered at the exact same time, 2023-06-20 06:36:27, which led to the data loss. See Figure 2.
![Because queue tasks can run simultaneously, concurrent execution causes values that need to be added to the JSON field to be lost. The values [251, 253, 254, 258] were lost. A large number of query, modify, and save operations were triggered at the exact same time, 2023-06-20 06:36:27, which led to the data loss](https://media.shuijingwanwq.com/2023/10/2.png)
4. Reference: 12.17.4 Functions That Modify JSON Values JSON_ARRAY_APPEND The code is implemented as follows, and the generated SQL is as follows. See 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
5. In addition to appending values to the JSON array, there are also cases where values need to be removed from 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, the `theme_task_ids` field contains a total of 17 values. Theoretically, after 17 executions, `id` = 22 should have been removed. However, in reality, the `theme_task_ids` field still contains [380, 381, 382, 383, 385, 386, 389, 394]. The executed SQL is as follows: See Figure 4.
![When running concurrently in the queue, the `theme_task_ids` field contains a total of 17 values. Theoretically, after 17 executions, `id` = 22 should have been removed. However, in reality, the `theme_task_ids` field still contains [380, 381, 382, 383, 385, 386, 389, 394]](https://media.shuijingwanwq.com/2023/10/4.png)
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: Removing data from a MySQL JSON array . I found that the `search_str` parameter of JSON_SEARCH does not currently support numbers. See Figure 5.
SELECT
JSON_SEARCH( theme_task_ids, 'all', '375' )
FROM
theme_saas_task
WHERE
id = 21;
8. I ultimately decided to convert values to strings before inserting them into the `theme_task_ids` field. The adjusted code 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, under concurrent conditions, some values still remained undeleted. Out of a total of 17, 12 were deleted, leaving 5. See Figure 6.
10. Because this piece of code runs very infrequently, I ultimately decided to implement it with a lock.
$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. I ultimately confirmed that this approach was problematic. The remaining values were [567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581]. The expected result was an empty array. Through log records, I found that the queried data did not meet expectations and had decreased significantly. I printed the logs for the 1st and 17th executions 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. I finally found the root cause. The root cause was that for the same record, values were being continuously added to the `theme_task_ids` field elsewhere. Once added, they were immediately pushed to the queue, and then the queue continuously removed values from the `theme_task_ids` field. I decided to batch enqueue the values only after all additions to the `theme_task_ids` field were complete.
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');
}
Implementation after adjustment:
$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 approach in step 12 proved feasible. The final remaining value was []. The expected result was an empty array, which matched expectations. I printed the logs for the 1st, 16th, and 17th executions 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
]
]