あるゲームAPIの修正メモ（DB行ロック）

はじめに

ある稼働中のゲームのAPIでDBのロック処理を直しました。
その対応内容をまとめてみました。

PHPコードは書かずにSQL文＋文章というちょっと雑な説明になっていますがご容赦ください……。

概要

• いわゆるAPIと呼ばれるもの。
  クライアントから入力を受け取って処理を行い結果の文字列（JSON）を返す

• 更新系APIは基本的に同じクライアントから複数同時リクエストされることはない。
  仮にあったとしても同時処理できる必要はない
  （ただし一部例外はある）

環境

	Ver	備考
Apache	2.2	WEB専用のクラウドサーバ x 4台
PHP	5.4
Symfony	3.3
MySQL	5.6	DB専用のクラウドサーバ x 3台（3台ともに用途別）
クライアント	-	PC（Windows）、PS4

修正メモ

１．この行ロックは必要ないかも

複数のテーブルからデータを取得して計算を行い、複数のテーブルのデータを更新するAPIがありました。
ここではトランザクション内で複数のSELECT FOR UPDATEが実行されていました。
行ロックをかけたレコード全てUPDATEしているので一見必要そうに見えなくもないですが…。

修正前

BEGIN;
SELECT * FROM user WHERE id=1 FOR UPDATE;
SELECT * FROM user_info WHERE id=1 FOR UPDATE;
SELECT * FROM user_data WHERE id=1 FOR UPDATE;

-- ここでいろいろな計算（PHP）

UPDATE user SET column=xxxx WHERE id=1;
UPDATE user_info SET column=xxxx WHERE id=1;
UPDATE user_data SET column=xxxx WHERE id=1;
COMMIT;

元データから更新データを計算するために計算中は元データの参照をロックしています。
ただ同じクライアントから複数リクエストがこないので1箇所にロックをかけるだけで期待通りの動作になります。

修正後

BEGIN;
SELECT * FROM user WHERE id=1 FOR UPDATE;
SELECT * FROM user_info WHERE id=1;
SELECT * FROM user_data WHERE id=1;

-- ここでいろいろな計算（PHP）

UPDATE user SET column=xxxx WHERE id=1;
UPDATE user_info SET column=xxxx WHERE id=1;
UPDATE user_data SET column=xxxx WHERE id=1;
COMMIT;

同時に複数APIへリクエストされるような場合も可能であればロックをかけるレコードを揃えておいた方がいいのではないかと思いました。
例えばuser_infoやuser_dataだけを更新する別のAPIがあったとして、
それぞれ更新するテーブルに行ロックをかけるのと共通でuserにロックかけるのと大差はあるだろうか…？
無用なデッドロックを避けるためにロックをかけるデータは揃えておく方がよい気が。
システム次第ですがこの方が管理が楽になると思います。

２．同時に実行するとデータ不整合が起こる

いわゆるギルドのような仕組みがありました。
ギルドに加入するにはプレイヤーからの申請とギルドマスターからの招待の2パターンです。
このシステムでは申請と招待を保存するテーブルが分かれていて、かつ同ユーザーの申請と招待を共存できない仕様でした。
そのためどちらかの登録を受け付けたならば、どちらかはエラーを返すようにしないといけないのです。
ロックするタイミングやテーブルが適切でないため申請と招待を同時に行うとデータ不整合が起きてしまいました。

（なんでこんな設計になっているのかは知らん！）
仮に申請と招待を1レコードで管理していても同時実行で重複エラーになるのでロックを見直す必要があります。

招待側の処理　修正前

BEGIN;
-- 既に申請しているか招待されているかチェックする。
SELECT * FROM guild_invite WHERE user_id=1 FOR UPDATE;
SELECT * FROM guild_apply WHERE user_id=1 FOR UPDATE;

-- データがあればエラーコードを返して終了。

-- 招待データを登録
INSERT INTO guild_invite (user_id, column)VALUE(1, xxxx);
COMMIT;

申請側の処理　修正前

BEGIN;
-- 既に申請しているか招待されているかチェックする。
SELECT * FROM guild_invite WHERE user_id=1 FOR UPDATE;
SELECT * FROM guild_apply WHERE user_id=1 FOR UPDATE;

-- データがあればエラーコードを返して終了。

-- 申請データを登録
INSERT INTO guild_apply (user_id, column)VALUE(1, xxxx);
COMMIT;

guild_inviteとguild_applyは申請・招待時にデータを新規登録するので最初はロックがかかりません。
そのため片方を少し遅れて実行すればエラーコードを返して終了できますが、申請と招待を同時に実行すると両方INSERTしてしまいます。

招待側の処理　修正後

BEGIN;
-- ギルドの存在チェック
SELECT * FROM guild WHERE guild_id=1 FOR UPDATE;

-- 既に申請しているか招待されているかチェックする。
SELECT * FROM guild_invite WHERE user_id=1;
SELECT * FROM guild_apply WHERE user_id=1;

-- データがあればエラーコードを返して終了。

-- 招待データを登録
INSERT INTO guild_invite (user_id, column)VALUE(1, xxxx);
COMMIT;

申請側の処理　修正後

BEGIN;
-- ギルドの存在チェック
SELECT * FROM guild WHERE guild_id=1 FOR UPDATE;

-- 既に申請しているか招待されているかチェックする。
SELECT * FROM guild_invite WHERE user_id=1;
SELECT * FROM guild_apply WHERE user_id=1;

-- データがあればエラーコードを返して終了。

-- 申請データを登録
INSERT INTO guild_apply (user_id, column)VALUE(1, xxxx);
COMMIT;

親情報（この場合はギルドのマスタ情報）をロックすることにしました。
これで同時申請・招待の不整合を防げるし、ギルドの解散と同時に実行されても大丈夫になりました。
ロック対象はデータ更新するテーブルとは限らない思います。

３．参照系APIに更新処理が入っている

一部のAPIは通信対戦を処理するゲームサーバからも通信があるので、クライアントとゲームサーバ同時に応答できるようにする必要がありました。
しかし参照系APIなのにSELECT FOR UPDATEが書かれていました。

修正前

BEGIN;
SELECT * FROM user WHERE user_id=1 FOR UPDATE;

-- データがあるかチェックする。
SELECT * FROM user_info WHERE user_id=1;
SELECT * FROM user_data WHERE user_id=1;

-- データがなければINSERT
INSERT INTO user_info (user_id, column)VALUE(1, xxxx);
INSERT INTO user_data (user_id, column)VALUE(1, xxxx);

-- 新規登録していたらここでデータを再取得
SELECT * FROM user_info WHERE user_id=1;
SELECT * FROM user_data WHERE user_id=1;
COMMIT;

-- 最後にデータをJSONで返す。

これでは同時通信された際に片方がCOMMITするまで待つことになります。
レコードが存在しないとダメというわけでもなかったので、今回はPHP側の追加だけで対応しました。

修正後

-- 更新しないのでトランザクションが不要になる

SELECT * FROM user WHERE user_id=1;

-- データがあるかチェックする。
SELECT * FROM user_info WHERE user_id=1;
SELECT * FROM user_data WHERE user_id=1;

-- データがなければSELECT結果と同じ配列を作って初期値を入れる。

-- 最後にデータをJSONで返す。

SELECT結果と同じ形式の配列を作ることで期待通りのJSONを出力できるようになりました。
これでクライアントとゲームサーバから同時に通信が来ても待ち時間なく応答できるようになりました。

まとめ

可能なら親情報にロックをかける。
その時点で必ず存在するレコードである。

• ユーザー情報更新なのでユーザーマスタをロック
• ギルド情報更新なのでギルドマスタをロック

…etc
その上でさらに細かくできるかを考えた方がよいのでは。

参照系APIに更新処理を入れない。
ロックが必要な更新処理を入れると大変な目にあうかもしれません…。
可能であれば通信フローを見直しましょう。
別APIを作って通信が1回増える方がマシかも。

おまけ

このシステムで使用しているトランザクション分離レベルは、REPEATABLE READでした。
MySQL5.6リファレンスマニュアル　14.2.11 デッドロックの対処方法

ロック読み取り (SELECT ... FOR UPDATE または SELECT ... LOCK IN SHARE MODE) を使用する場合は、READ COMMITTED などの低い分離レベルを使用してみてください。

ロックの使用を減らしてください。古いスナップショットからのデータを返すために、SELECT を許可する余裕がある場合は、FOR UPDATE または LOCK IN SHARE MODE 句を追加しないでください。同じトランザクション内の各一貫性読み取りでは、独自の新しいスナップショットから読み取られるため、READ COMMITTED 分離レベルを使用することが適切な方法です。

以前のチューニングメモでやってますけど、READ COMMITTEDでシステム組むのがよさそうですね。
Qiita - あるSPソーシャルゲームのチューニングメモ