MariaDBで読み取り整合性と書き込み速度のバランスを実現！ REPEATABLE READのすべて

ロック
読み取り操作対象の行に対してロックを取得することで、他のトランザクションによる排他アクセスを防止します。
ファントムリードの防止
読み取り操作中に他のトランザクションによって挿入された行は、現在のトランザクションでは読み取ることができません。
読み取りの整合性
トランザクション開始時点のデータスナップショットに基づいて読み込みが行われ、トランザクション中に他のトランザクションによってコミットされた変更も反映されます。

REPEATABLE READは、読み取り操作の整合性を保証しつつ、書き込み操作による干渉をある程度許容する分離レベルと言えます。具体的には、以下の状況で有効です。

複数のユーザーが同時にデータを読み書きするような、同時アクセスが発生するアプリケーション
商品在庫照会のような、ある程度の遅延が許容される場合
銀行口座の残高照会のような、常に最新の情報が必要な場合

一方、以下の状況には適していません。

二重決済のような、データの競合が発生する可能性が高い場合
厳密なデータ整合性が求められる場合

以下の例は、REPEATABLE READ分離レベルでトランザクションを実行する簡単なプログラム例です。

-- トランザクション開始
START TRANSACTION;

-- 残高照会
SELECT balance FROM accounts WHERE account_id = 1;

-- 更新処理
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- コミット
COMMIT;

この例では、まずSTART TRANSACTIONを実行してトランザクションを開始します。その後、SELECTステートメントで口座の残高を照会します。このとき、REPEATABLE READ分離レベルの影響で、トランザクション開始時点の残高が取得されます。

続いて、UPDATEステートメントで口座の残高を100減額します。この処理は、他のトランザクションの影響を受けずに実行されます。

最後に、COMMITを実行してトランザクションをコミットします。

REPEATABLE READを使用する場合は、以下の点に注意する必要があります。

複雑なロジック
REPEATABLE READは、他の分離レベルに比べて複雑なロジックを必要とする場合があります。
ロックの影響
読み取り操作対象の行に対してロックを取得するため、書き込み操作のパフォーマンスに影響を与える可能性があります。
ファントムリードの可能性
読み取り操作中に他のトランザクションによって挿入された行は、現在のトランザクションでは読み取ることができません。しかし、コミット前にロールバックされた行は、ファントムリードとして現れる可能性があります。

REPEATABLE READは、読み取り操作の整合性と書き込み操作のパフォーマンスのバランスを考慮する必要がある場合に適した分離レベルです。

デッドロック
複数のトランザクションが互いにロックを保持しあうデッドロックが発生する可能性があります。
ロック
読み取り操作対象の行に対してロックを取得するため、書き込み操作のパフォーマンスに影響を与える可能性があります。
ファントムリード
他のトランザクションによってコミット前にロールバックされた行が、ファントムリードとして現れる可能性があります。

これらの注意点に加え、以下の点にも注意する必要があります。

トランザクションの範囲
トランザクションの範囲が広すぎると、パフォーマンスに影響を与える可能性があります。
トランザクションの開始タイミング
トランザクションの開始タイミングによっては、他のトランザクションの影響を受ける可能性があります。

ファントムリード

-- トランザクション 1 を開始
START TRANSACTION;

-- 行を挿入
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);

-- トランザクション 1 をコミット
COMMIT;

-- トランザクション 2 を開始
START TRANSACTION;

-- 残高照会
SELECT balance FROM accounts WHERE account_id = 1;

-- 行を削除
DELETE FROM accounts WHERE account_id = 1;

-- トランザクション 2 をロールバック
ROLLBACK;

この例では、トランザクション 1 で行を挿入し、トランザクション 2 で残高を照会してから行を削除します。しかし、トランザクション 2 がロールバックされるため、トランザクション 1 で挿入された行はファントムリードとして現れます。

デッドロック

-- トランザクション 1 を開始
START TRANSACTION;

-- 行 1 をロック
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- トランザクション 2 を開始
START TRANSACTION;

-- 行 2 をロック
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;

-- トランザクション 1 で行 2 を更新
UPDATE accounts SET balance = balance + 50 WHERE account_id = 2;

-- トランザクション 2 で行 1 を更新
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;

この例では、トランザクション 1 とトランザクション 2 がそれぞれ行 1 と行 2 をロックし、互いにロックを解放しようとします。そのため、デッドロックが発生します。

複雑なロジック
REPEATABLE READは、他の分離レベルに比べて複雑なロジックを必要とする。
デッドロック
複数のトランザクションが互いにロックを保持しあうデッドロックが発生する可能性がある。
ロック
読み取り操作対象の行に対してロックを取得するため、書き込み操作のパフォーマンスに影響を与える可能性がある。
ファントムリード
他のトランザクションによってコミット前にロールバックされた行が、ファントムリードとして現れる可能性がある。

REPEATABLE READ の代替方法としては、以下の分離レベルが挙げられます。

SERIALIZABLE
すべてのトランザクションがシリアル実行されるため、ファントムリードやデッドロックが発生しません。ただし、ロックの影響が大きいため、パフォーマンスが低下する可能性があります。
READ COMMITTED
コミットされた変更のみを読み取ることができます。ファントムリードは発生しませんが、読み込み操作と書き込み操作が同時に行われた場合、古いデータを読み込んでしまう可能性があります。
READ UNCOMMITTED
他のトランザクションの影響を受けずに、最新のデータを常に読み取ることができます。ただし、コミットされていない変更も読み取ってしまう可能性があるため、注意が必要です。

どの分離レベルを選択するかは、アプリケーションの要件によって異なります。

SERIALIZABLE
厳密なデータ整合性が求められる場合。
READ COMMITTED
読み取り操作と書き込み操作のバランスが必要な場合。
READ UNCOMMITTED
読み取り操作の頻度が高く、書き込み操作による干渉が許容される場合。

Optimistic Locking
ロックを使用せずに、競合を検出して解決することで、パフォーマンスを向上させます。
MVCC (Multi-Version Concurrency Control)
行の過去のバージョンを保持することで、ファントムリードやデッドロックを発生させずに、高い読み取り操作のパフォーマンスを実現します。