Categories: MySQLMySQL 5.7SQL数据库编程语言

在操作 MySQL 5.7 中的 json 字段时，并发执行（查询、修改、重新赋值）时数据丢失的问题处理（使用修改 JSON 值的函数 JSON_ARRAY_APPEND）

图1

1、json 字段中存储的是数组格式，值示例：[249, 247, 250, 244, 243, 255, 246, 245, 257, 248, 259, 252, 256]。如图1

图1

2、现有的在队列作业中运行的代码实现如下，生成的 SQL 如下 
$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、由于队列任务可以同时运行，在并发执行时，会导致需要添加至 json 字段中的值丢失。丢失了 [251, 253, 254, 258]。由于有大量的查询修改保存操作皆在同一时间点 2023-06-20 06:36:27 触发，进而导致了数据丢失。如图2

图2

4、参考：12.17.4 修改 JSON 值的函数 JSON_ARRAY_APPEND 代码实现如下，生成的 SQL 如下 。如图3

图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、除了往 json 数组中追加值以外，还存在往 json 数组中删除值的情况，现有的实现如下

$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、当在队列中并发运行时，字段 theme_task_ids 中总计有 17 个值，理论上来说，执行了 17 次以后，`id` = 22 应该会被删除掉。但是，实际上，字段 theme_task_ids 中还剩下 [380, 381, 382, 383, 385, 386, 389, 394]。执行的 SQL 如下： 如图4

图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、参考：Mysql json 数组删除数据  。发现 JSON_SEARCH 的参数 search_str 暂不支持数字。如图5

图5

SELECT
 JSON_SEARCH( theme_task_ids, 'all', '375' ) 
FROM
 theme_saas_task 
WHERE
 id = 21;

8、最终决定在插入到字段 theme_task_ids 中的值时，先转换为字符串。在队列作业中运行的代码调整实现如下

$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、但是在并发情况下，仍然存在一些值未被删除掉，总计 17 个，删除了 12 个，还剩下 5 个。如图6

图6

10、由于这一段代码运行的频率很低，最终决定添加锁实现

        $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、最终确认此方案存在问题，最后剩下 [567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581]。期望为 空数组。通过日志记录发现，在查询时，查询出的数据不符合预期，减少了许多，分别打印出第 1 次与 第 17 次的日志。

[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、最终找到根源，根源在于同一条记录，在另一个地方在不断地向字段 theme_task_ids 添加值，添加完毕后，就立即入队列，然后在队列中在不断地从字段 theme_task_ids 中删除值。决定向字段 theme_task_ids 添加值完毕后，再统一批量入队列。

调整前的实现：

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');
}

调整后的实现：

$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、确认调整后，第 12 步骤的方案可行，最后剩下 []。期望为 空数组，符合预期。分别打印出第 1 次与 第 16 次、 第 17 次的日志。

[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
    ]
]
永夜

Leave a Comment
Share
Published by
永夜
Tags: jsonJSON_ARRAY_APPENDJSON_SEARCHMySQLMySQL 5.7search_strupdate修改删除字符串并发执行数字数据丢失数组查询追加重新赋值锁实现队列
7 月 ago

Recent Posts

Laravel 9 中的 Redis 门面 hset 不允许传递多个字段/值

1、尝试基于 Redis 门面…

21 小时 ago

在 Google Chrome 中由于网站升级，打开空白，仅在无痕模式中可正常打开的解决

1、在 Google Chro…

5 天 ago

在 https://tinys.pl/cn/swap 上用 SOL 购买得到 NUTS，在 欧易 Web3 钱包 中显示数量为 0 的排查分析

1、在 https://tin…

7 天 ago

在 Laravel 9 中，基于 Spatie\QueryBuilder\QueryBuilder 来生成查询 SQL，同一字段可支持：partial、exact

1、参考：在 Laravel …

1 周 ago

基于 PHP-Xlswriter 导出 Excel 文件时，设置日期列的格式

1、现在导出的 Excel 文…

1 周 ago

在 OneKey 钱包中，发送 Optimism 网络中的 USDT 的流程

1、在 OneKey 钱包中，…

3 周 ago