MySQLのMVCCとトランザクション内における集計について

この記事は何？

トランザクション分離レベルがRead CommittedなときのMVCCの挙動とトランザクション内における集計で学びがあったので忘備録として共有します。

そもそも、MVCCとは？

MVCC（Multi-Version Concurrency Control）は、各トランザクションに一意なバージョンを付与する技術です。データへの同時アクセスが行われた際、各トランザクションが独自のデータバージョンを持つことで、データへの競合を回避させ、並列処理を実現します。このアプローチにより、読み取りと書き込みの操作が相互にブロックされず、システム全体の効率とパフォーマンスが向上します。

Read Committedのトランザクション分離レベルにおけるMVCCの特性は、トランザクション内でデータを読み取る際、そのトランザクションが開始された時点でのコミット済みのデータのみを参照できることです。従って、トランザクションが進行中に他のトランザクションでデータが更新・コミットされても、その変更は読み取れません。

MVCCについて挙動を確認する

mysqlのInnoDBエンジンで実際にクエリを実行して、挙動を確認してみます。

まず、以下のデータを用意します。

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id int NOT NULL AUTO_INCREMENT,
  remaining_unit int,
  PRIMARY KEY (id)
);

insert into products (remaining_unit) values (2);

次に、ターミナルで、t1とt2の2つのセッションを別々に開いて、以下の手順でクエリを実行します。

実行順	t1	t2
1	BEGIN;	BEGIN;
2	SELECT remaining_unit FROM products WHERE id = 1;
3	UPDATE products SET remaining_unit = 0 WHERE id = 1;
4		SELECT remaining_unit FROM products WHERE id = 1;
5	COMMIT;
6		SELECT remaining_unit FROM products WHERE id = 1;

この手順に従ってクエリを実行すると、順序4と6でt2セッションの実行結果は2となります。

この結果が2となる理由は、Read Committedの分離レベルでは、各トランザクションが開始された時点のコミット済みデータ（スナップショットとも呼ばれます）から読み取りを行う仕様であるためです。この挙動がMVCCの特徴的な動作です。

また、「トランザクションの開始時点」とは、具体的には「BEGINを実行した後で最初にSELECTを実行する時点」を指します。

t2セッションにおいて、t1がコミットを行う前にSELECTを実行したため、remaining_unitの値が2の状態のスナップショットが確保されます。そのため、t1がコミットを行った後でも、順序6でのt2のSELECT結果はremaining_unitの値が2のままとなります。

トランザクションの開始時点が実際にBEGIN直後の最初のSELECTのタイミングであるかを検証してみます。

実行順	t1	t2
1	BEGIN;	BEGIN;
2	SELECT remaining_unit FROM products WHERE id = 1;
3	UPDATE products SET remaining_unit = 0 WHERE id = 1;
4	COMMIT;
5		SELECT remaining_unit FROM products WHERE id = 1;

実行結果から、順序5のSELECTの結果として、remaining_unitが0になることが確認できます。

では、t1のセッションが開始された後、t1がコミットする前にt2のセッションが開始され、t2がt1のコミット結果を待ちたい場合、どのような対応が考えられるでしょうか？ この問題への一つの解答として、排他ロックを使うことです。具体的には、SELECT ... FOR UPDATEを使用することで、t2はt1の終了をきちんと待つようになります。

FOR UPDATE句を使うと、対象となる行は他のトランザクションからの変更やロックを防ぐことができます。これによって、読み取りから更新までの間に他のトランザクションが介入することを防ぐため、データの整合性を保つことができます。

以下にその動作を実際に試す手順を示します。

実行順	t1	t2
1	BEGIN;	BEGIN;
2	SELECT remaining_unit FROM products WHERE id = 1 FOR UPDATE;
3	UPDATE products SET remaining_unit = 0 WHERE id = 1;
4		SELECT remaining_unit FROM products WHERE id = 1 FOR UPDATE;
5	COMMIT;

t2のセッションでの順序4は、t1のコミットを待つ状態となります。t1が順序5でCOMMITすると、順序4のSELECTの結果としてremaining_unitの値が0として返されるはずです。これは、t2のSELECT ... FOR UPDATEがt1の更新を待っており、COMMIT後のデータが読み込まれるためです。

次に、本題のトランザクション内の集計とMVCCの影響について考えてみます。

トランザクション内の集計とMVCCの影響

トランザクション内での集計操作を行う際、MVCCの挙動、特にスナップショットの確保タイミングを適切に考慮しないと、期待とは異なる結果が返されるリスクがあります。

一般的なトランザクション内での集計のケースとして、ポイントの残高確認や売上金額の計算が挙げられます。例として、商品を購入する際に、使用するポイントや売上金額の確認が求められるシチュエーションを考えてみましょう。多くのシステムで、これらの値が0未満になることは許容されていません。

具体的なシナリオとして、ユーザーがポイントを使用して商品を購入し、その過程で取引データを生成する流れを考察します。このシナリオの中で、ポイント残高が0未満になる可能性について検討します。

事前のデータベース準備として、以下のSQLコマンドを実行します。

DROP TABLE IF EXISTS point_balances;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

CREATE TABLE products (
  id int NOT NULL AUTO_INCREMENT,
  remaining_unit int,
  price int,
  PRIMARY KEY (id)
);

CREATE TABLE transactions (
    id INT NOT NULL AUTO_INCREMENT,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    total_price INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE point_balances (
  id int NOT NULL AUTO_INCREMENT,
  user_id int, 
  amount int,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

以下のSQLにて、ユーザーに最初に1500ポイントが付与され、その後500ポイントが使用されたという履歴を作成します。

INSERT INTO users (id) VALUES (1);
INSERT INTO point_balances (user_id, amount) VALUES (1, 1500); 
INSERT INTO point_balances (user_id, amount) VALUES (1, -500);
INSERT INTO products (remaining_unit, price) VALUES (2, 1000);

続いて、商品の購入処理に関する集計とバリデーションを含むクエリを示します。エラーハンドリングに関しては通常アプリケーションコードで行いますが、ここではシンプルにSQLとして示します。

BEGIN;

-- 商品の在庫を確保するための排他ロックを取得
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- 在庫の確認
SELECT * FROM products WHERE id = 1 AND remaining_unit >= 1;

-- 在庫が確認できなければ、トランザクションをロールバックしエラーを返却

update products set remaining_unit = remaining_unit - 1 where id = 1;

-- ユーザーのポイント残高を取得
SELECT sum(amount) FROM point_balances WHERE user_id = 1 GROUP BY user_id;

-- ポイントの残高が不足している場合は、トランザクションをロールバックしエラーを返却

-- ポイント残高を更新（1000ポイントを使用）
insert into point_balances (amount) values (-1000);

-- 取引履歴を追加
INSERT INTO transactions (product_id, quantity, total_price) VALUES (1, 3, 1500);

COMMIT;

上記の実装には、ポイント残高が0未満になるリスクが存在します。それは、ポイント残高の取得と登録前のバリデーションです。

前述の通り、最初の在庫チェック時にスナップショットが確保され、その後の処理で他のトランザクションやセッションでデータがcommitされると、その変更は読み取れません。例えば、ポイントに有効期限が存在する場合、別のトランザクションでpoint_balancesにマイナスの値が記録されるシナリオは十分に考えられます。

さらに、商品の在庫を更新するトランザクションが先に実行され、購入トランザクションが長時間待機状態になった場合、ユーザーがページから離れるなどして、商品の重複購入などの問題が発生する可能性も考慮されます。

実際に、別セッションでのcommitによりポイント残高が0未満になるシナリオを試してみます。
t1がポイント残高での商品購入、t2がポイントの有効期限切れを想定しています。

実行順	t1	t2
1	BEGIN;	BEGIN;
2	SELECT * FROM products WHERE id = 1 FOR UPDATE;
3	SELECT * FROM products WHERE id = 1 AND remaining_unit >= 1;
4	UPDATE products SET remaining_unit = remaining_unit - 1 where id = 1;
5		INSERT INTO point_balances (user_id, amount) VALUES (1, -1000);
6	SELECT sum(amount) FROM point_balances WHERE user_id = 1 GROUP BY user_id;
7		SELECT sum(amount) FROM point_balances WHERE user_id = 1 GROUP BY user_id;
8	INSERT INTO point_balances (user_id, amount) VALUES (1, -1000)
9	INSERT INTO transactions (product_id, quantity, total_price) VALUES (1, 3, 1500);
10	COMMIT;	COMMIT;

COMMIT後、t1,t2のどちらかのターミナルで、

SELECT sum(amount) FROM point_balances WHERE user_id = 1 GROUP BY user_id;

を実行してみると、残高が-1000になることが確認できます。

t1の順序6では、残高が1000となりますが、t2の順序7で確認すると、残高は0となります。
これは、t1の順序2で取得されたスナップショットのため、commitされていないデータを読み取れない状態になっているからです。

t1の順序6での確認により、残高は1000とされているため、バリデーションを通過してしまい、順序8でのinsertが実行され、結果的に残高がマイナスになる結果となります。

これを受けて、トランザクション内での集計やバリデーションを行う際には、MVCCの特性を十分に考慮して設計・実装する必要があります。

対応策

今回のケースの場合、どうにかして残高の集計をシリアルに実行する必要がありそうです。

一番最初に思いつくのは、

SELECT * FROM point_balances WHERE user_id = 1 FOR UPDATE;

だと思います。

user_idに絞り込んで排他ロックすることで、他トランザクションや別セッションで重複実行されたとしても処理がシリアルになるので集計結果がマイナスになるようなデータ不整合は防げます。

ただし、この方法だと、リリース時は特に問題ないかもしれませんが、年月経過でレコードが増えてきたときに、lockする行数が増加するため、長い目で見たときにいくつか課題が出てきます。

• 正常に処理が進んだ場合でも、ロールバック時にも、多くのシステムリソースの消費が行われる可能性が高くなる
• デッドロックが発生する確率が高くなる
• パフォーマンスの悪化によりタイムアウトが発生する確率が高くなる

そこで、先に記載したテーブルとは別に排他ロック用のテーブルを作成するのが良いのではないかと思います。

CREATE TABLE user_point_mutex (
  user_id int, 
  PRIMARY KEY (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

このuser_point_mutexテーブルをBEGIN直後にFOR UPDATE句を実装することで、先に記載した複数行lockによる問題は防ぐことができます。

以下は、修正後のSQLクエリです。

BEGIN;

// ポイント残高取得をシリアルに実行するため排他ロック
SELECT * FROM user_locks WHERE user_id = 1 FOR UPDATE;

// 在庫確保のために排他ロック
SELECT * FROM products WHERE id = 1 FOR UPDATE;

// 在庫チェック
SELECT * FROM products WHERE id = 1 AND remaining_unit >= 1;

// 在庫が取得できなければ、rollbackしてエラーを返す。

update products set remaining_unit = remaining_unit - 1 where id = 1;

// ポイント残高取得
SELECT sum(amount) FROM point_balances WHERE user_id = 1 GROUP BY user_id;

// ポイント残高確認。残高が不足していた場合、rollbackしてエラーを返す。

// ポイント残高登録
insert into point_balances (amount) values (-1000);

INSERT INTO transactions (product_id, quantity, total_price) VALUES (1, 3, 1500);

commit;

ただし、このアプローチにもデメリットもあります。

• 新しいユーザーが追加される度に、user_point_mutexテーブルにエントリを追加する必要がある。
• ロックをかけるテーブルが増加することで、他のトランザクションとの実装順序を揃えないとデッドロックのリスクが増加する

最終的に、一意にロックするためのテーブルを使用する方法を示しましたが、最適な解決策はシステムの要件や状況により異なります。このため、利点と欠点をしっかりと比較し、要件に応じた適切なアプローチを選択することが重要です。