一括系処理のデッドロックやDBのフリーズを回避する！

大量データ処理における注意点

マルチテナントSaaSや複数オペレーターが同時に利用するビジネスアプリケーションの開発において、CSV処理や大量データの一括ジョブ処理は一般的です。しかし、このような処理には注意点がいくつか存在します。

今回はアプリケーション開発においてありがちな罠と対策についてご紹介します。

ハマりやすい罠: デッドロックとDBのフリーズ

• デッドロック: 複数の処理が同時に異なるリソースにアクセスしようとすると、デッドロックが発生することがあります。例えば、A企業とB企業が同時にジョブ処理を実行する場合、デッドロックのリスクが増大します。特に、多人数のユーザーや複数の企業が同時に処理を実行する際には注意が必要です。
• DBのフリーズ: ループを使用して大量のINSERTやUPDATEを行うと、アプリケーションやデータベースのパフォーマンスが低下することがあります。これにより、アプリケーションが反応しなくなることがあります。

対策方法

1. デッドロック:
   • WHERE節のキーにINDEXを適用する。
     INDEXが貼られていない場合に、複数行や範囲でロックしてしまうことでデッドロックの原因になります。
   • 更新対象レコードが存在することを確認し、空振りを避ける。
     可能であれば1行、そうでなければ最小の範囲でロックを取得する必要があります。広ければ広いほどデッドロックの危険性が高まります。
   • 楽観ロックを使用してデータ競合を防ぐ。
2. DBのフリーズ:
   • N+1問題の解消。
     forを利用して対象データの個数以上の回数でSQLを発行する処理をしてしまっているバグ的な実装です。
     → 1万件であれば1,000件のバルクインサート * 10回に分割するなどアプリケーションとDBの往復を減らす
   • バルクインサートやバルクアップデートを利用して、データベースのリクエスト数を減らす。
3. 多重実行の防止:
   • Redisやジョブの履歴管理テーブルを利用して、同時実行を制御する。
     標準キューを利用した場合にも、規定時間を超えた場合は再度キューが投げられることでの多重実行を防止するためにも必ずロックが必要になります。
   • 楽観ロックを活用して、ジョブの多重実行を防止する。
     ジョブ履歴管理テーブルにRevisionカラムを追加して、ジョブを処理中に同じ内容のジョブの多重実行しないようにします。
   • 冪等性(結果の保証)
     大量データの処理で処理が途中で失敗してしまった場合に、それまでの成功処理分は残して、失敗分の途中から処理の進行をしたいバッチ処理の場合。複数回実行しても結果が同じになるように実装する必要があります。

重要技術 | 楽観ロックのテーブル設計

楽観ロックを実装するための一般的なアプローチとして、version または revision といったカラムをテーブルに追加します。このカラムは、レコードが更新されるたびにインクリメントされることを想定しています。

例えば、以下のような products テーブルがあるとします。

ここで revision カラムが楽観ロックのためのカラムです。

楽観ロックの実装

楽観ロックを利用したデータの更新処理の基本的な流れは以下の通りです：

1. レコードを読み取り、その時点での revision カラムの値を取得。
2. 更新内容とともに、revision の値を使ってデータを更新。この時、revision カラムの値を1増やす。
3. 更新が成功したら完了。更新が失敗した場合（他のユーザー等によって既に更新されていた場合）、エラーを返すか再試行する。

以下は、idが1の商品の名前と価格を更新する際のクエリの例です：

このクエリは、revision が1の時のみレコードを更新します。もしレコードが既に他のトランザクションによって更新され、revision が1でなくなっていた場合、このUPDATEクエリは0行を更新として空振ります。そして実行結果の行を取得すると0行となります。

0行の更新結果の時点で、楽観ロックによる競合が発生したことを検知でき、適切な対応（例：エラーメッセージの表示やロールバックによる終了処理、再施行など）を行うことができます。

一括での編集機能などで、複数のオペレータによってほぼ同時に更新が走ることがあるので必要になります。

今回はproductsテーブルにrevisionを入れた例ですが、ジョブの履歴管理テーブルjob_products_update_resultsテーブルにもrevisionを定義しておくのも良い方法です。

この方法を採用することで、データの整合性を保ちながら、複数のユーザーやプロセスが同時に同じデータにアクセスするシステムでのデータの競合を避けることができます。

具体的な対策の補足

1. INDEXの適用
   • 補足: トランザクションのロック順序が一貫していれば、デッドロックの可能性を低減できます。INDEXを正しく適用することで、データへのアクセス速度が向上し、ロック時間が短縮されるため、デッドロックのリスクが低減します。INDEXを適用していることで、DB側のロックの範囲とロック時間も最小化するので、トランザクションにて発行するSQLのWHEREで指定しているキーにはINDEXがあった方が良いです。
   • 具体的な対応: 例えば、usersテーブルのemailカラムにINDEXが必要だと判断した場合、以下のようなクエリでINDEXを追加できます。
     
     ALTER TABLE users ADD INDEX idx_email(email);

     1	ALTER TABLE users ADD INDEX idx_email(email);

2. 空振りを避ける
   • 補足: 更新対象のレコードが存在しない場合、不要なロックやリトライを発生させる可能性があります。
   • 具体的な対応: SELECT FOR UPDATEなどを用いて明示的にロックを取得する前に、該当レコードが存在するかの確認を行います。疑似コード
     
     -- トランザクションを開始 START TRANSACTION; -- 特定のユーザーをロックする SELECT * FROM users WHERE user_id = 123 FOR UPDATE; -- 存在チェック IF (SELECT COUNT(*) FROM users WHERE user_id = 123) = 1 THEN -- 存在する場合、そのユーザーに対する更新処理を行う UPDATE users SET user_name = '新しい名前' WHERE user_id = 123; ELSE -- 存在しない場合、何らかの対応を行う（例: エラーメッセージの表示やログの記録など） END IF; -- トランザクションをコミット COMMIT;

     1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

     -- トランザクションを開始 START TRANSACTION;   -- 特定のユーザーをロックする SELECT * FROM users WHERE user_id = 123 FOR UPDATE;   -- 存在チェック IF (SELECT COUNT(*) FROM users WHERE user_id = 123) = 1 THEN     -- 存在する場合、そのユーザーに対する更新処理を行う     UPDATE users SET user_name = '新しい名前' WHERE user_id = 123; ELSE     -- 存在しない場合、何らかの対応を行う（例: エラーメッセージの表示やログの記録など） END IF;   -- トランザクションをコミット COMMIT;

3. 最小限の範囲でのロック
   • 補足: テーブル全体や範囲をロックすると、他のトランザクションがその部分をアクセスできなくなり、デッドロックの原因となる可能性が高まります。
   • 具体的な対応: 可能な限り行レベルのロック（例: SELECT FOR UPDATE）を利用し、必要最小限の範囲だけをロックします。
4. 楽観ロックの使用
   • 補足: 楽観ロックは、データを更新する際にバージョン番号などを使って、データが変更されていないことを確認する手法です。データが変更されていた場合は、更新を中止してエラーとします。
   • 具体的な対応: 更新処理を行う際、レコードのバージョン番号をチェックし、他のトランザクションによる変更を検出した場合は更新を中止します。
5. ロックの順序の一貫性
   • 複数のリソースに対してロックを取得する必要がある場合、すべてのトランザクションが同じ順序でロックを取得するようにすることで、デッドロックのリスクを減少させることができます。
6. タイムアウトの設定
   • ロックを取得できない場合、一定時間待機した後にトランザクションを中止することで、システム全体のハングアップを避けることができます。
7. 頻繁にコミットする
   • トランザクションを小さく保つことで、ロックを保持する時間を短縮し、他のトランザクションとの競合を減少させることができます。

テスト方法: デッドロックやDBのフリーズを避けるため

1. デッドロックのテスト:
   • シミュレーション: 複数のテナントやユーザーが同時にジョブを実行するシチュエーションを再現します。
     • 1つの企業で2人のユーザーが同時にジョブを実行。
     • A企業とB企業が同時にジョブを実行。
   • 大量データ処理: ジョブの実行時間を10分以上にするため、n万件の大量のサンプルデータでジョブを実行します。
2. DBのフリーズのテスト:
   • 負荷テスト: 大量のCSVデータを使用して、行数分のINSERTやUPDATEを行います。
   • 同時接続テスト: 複数のユーザーやアプリケーションインスタンスが同時にデータベースに接続し、クエリを発行するシチュエーションをシミュレートします。
3. 多重実行のテスト:
   • 再実行テスト: ジョブが完了する前に、同じジョブを再実行して、多重実行が発生するか確認します。
   • キューの再投入テスト: ジョブが一定の時間を超えて実行されない場合、キューに再度投げられることを確認するテストを行います。
4. テストコード:
   • デッドロックのテストコード
   • 多重実行のテストコード

テストのポイント

• モニタリングツールの使用: デッドロックやDBのフリーズを検出するために、DBのモニタリングツールやアプリケーションのロギング機能を使用することが推奨されます。
• 再現性: 問題が発生した場合、その問題を再現して、原因を特定することが重要です。
• 環境: 本番環境と同じ条件のテスト環境でテストを実行することで、より現実的な結果を得ることができます。

これらのテスト方法は、開発フェーズやQAフェーズで実施することにより、問題の早期発見や対応が可能となります。

システム開発ならシステムガーディアン

大量データ処理の専門家として、私たちシステムガーディアンはさまざまなビジネスツールやサービスの開発も行っています。

安全かつ効率的なデータ処理を求める企業は、ぜひ私たちにお問い合わせください！