MySQL - トランザクション

概要

トランザクションとは、データベースに対する複数の処理を1つの論理的な作業単位としてまとめる仕組みである。
トランザクション内の全ての処理が成功した場合のみ変更が確定 (コミット) され、いずれかの処理が失敗した場合は全ての変更が取り消される。(ロールバック)

MySQLでは、InnoDBストレージエンジンがトランザクションを完全にサポートしている。
InnoDBは、ACID特性 (原子性、一貫性、分離性、永続性) を保証し、データの整合性と信頼性を確保する。

例えば、銀行の送金処理を考える。
口座Aから10,000円を引き出し、口座Bに10,000円を入金するという2つの処理がある場合、これらは1つのトランザクションとして扱われる。
引き出し処理が成功しても入金処理が失敗した場合、引き出し処理も自動的に取り消される。
これにより、データの不整合 (口座Aから引き出されたが口座Bに入金されない状態) を防止できる。

トランザクションは、以下に示すような場面で使用される。

• 複数のテーブルにまたがる更新処理
• 金融取引や在庫管理など、データの整合性が重要な処理
• バッチ処理による大量データの一括更新
• 複数ユーザが同時にアクセスする環境でのデータ保護

MySQLのデフォルト設定では、自動コミットモードが有効になっているため、各SQL文が個別にコミットされる。
複数の処理を1つのトランザクションとして扱うには、明示的に START TRANSACTION 文を使用してトランザクションを開始する必要がある。

トランザクション処理

• START TRANSACTION 文、または、BEGIN 文は、新しいトランザクションを開始する。
  
• COMMIT 文は、現在のトランザクションをコミットして、その変更を永続的なものにする。
  
• ROLLBACK 文は、現在のトランザクションをロールバックして、その変更を取り消す。
  
• SET autocommit 文は、現在のセッションのデフォルトの自動コミットモードを有効または無効にする。
  

デフォルトでは、MySQLは自動コミットモードが有効の状態で動作する。
したがって、トランザクション内ではない場合、各ステートメントは START TRANSACTION 文 および COMMIT 文で囲まれているかのようにアトミックである。

ROLLBACK 文を使用して処理を元に戻すことはできないが、ステートメントの実行中にエラーが発生した場合、ステートメントはロールバックされる。

一連のステートメントに対して自動コミットモードを暗黙的に無効にするには、START TRANSACTION 文を使用する。
START TRANSACTION 文を実行する時、そのトランザクションを COMMIT 文 または ROLLBACK 文で終了するまで、自動コミットは無効のままとなる。
その後、自動コミットモードはその以前の状態に戻る。

 START TRANSACTION;
 SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
 UPDATE table2 SET summary=@A WHERE type=1;
 COMMIT;

複数の実行する処理を START TRANSACTION 文 または BEGIN 文 と COMMIT 文 または ROLLBACK 文で囲む。

以下の例では、全ての処理がエラー無く完了する場合のみコミットされる。
もし、いずれかにおいてエラーが発生する場合は、コミットされずに自動的にロールバックされる。

 START TRANSACTION;
 -- <処理 1>;
 -- <処理 2>;
 COMMIT;

このように、一連の処理が全て完了する場合のみ実行されるもの がトランザクション処理である。

自動コミットモード

自動コミットモードを明示的に無効にするには、変数 autocommit を 0 に設定する。
ただし、自動コミットモードを無効にした場合、データベースやテーブルの変更において、トランザクションセーフテーブル (InnoDBまたはNDBのテーブル等) への変更が永続的になることはない。

COMMIT 文を使用して変更をディスクへ格納、または、ROLLBACK 文を使用して変更を無視する必要がある。

 SET autocommit=0;

変数 autocommit はセッション変数であるため、セッションごとに設定する必要がある。

ACID特性

MySQLのトランザクションは、ACID特性と呼ばれる4つの重要な特性を持つ。

原子性 (Atomicity)

トランザクションの全ての操作が完全に実行されるか、全く実行されないかのいずれかになる。
トランザクション内の一部の操作だけが実行されることはない。

COMMIT 文で全ての変更が確定され、ROLLBACK 文で全ての変更が取り消される。

一貫性 (Consistency)

トランザクション完了後、データベースが常に有効な状態に保たれる。
制約 (外部キー制約、CHECK制約等) の検証により、データの整合性が維持される。

トランザクション実行前と実行後で、データベースの整合性が保たれる。

分離性 (Isolation)

複数の同時実行トランザクションが互いに干渉しない。
他のトランザクションの未コミット変更が、他のトランザクションから見えないように制御される。

トランザクション分離レベルとロック機構により実現される。

永続性 (Durability)

コミット後のデータはシステム障害によっても失われない。
変更がディスクに永続化され、クラッシュ後も復旧可能な状態になる。

永続性は、innodb_flush_log_at_trx_commit 設定で制御される。

• 0

  トランザクションログをバッファに留める。

  1秒ごとにディスクに書き込まれる。

  最も高速だが、障害時に最大1秒分のデータが失われる可能性がある。

• 1

  毎トランザクションコミット時にfsyncを実行する。

  最も安全だが、最も低速

  デフォルト設定

• 2

  トランザクションログをOSキャッシュに留める。

  MySQL障害では失われないが、OS障害では失われる可能性がある。

  中間的な性能と安全性。

トランザクション分離レベル

トランザクション分離レベルは、複数のトランザクションが同時実行される際の振る舞いを制御する。
MySQLは4つの分離レベルを提供する。

READ UNCOMMITTED

最も低い保護レベルである。

他のトランザクションの未コミット変更を読み取ることができる。
ダーティリードが発生する可能性がある。

統計情報の収集等、精度が重要でない読み取りに使用される。

READ COMMITTED

コミット済みデータのみ読み取ることができる。

各 SELECT 文が独立したスナップショットを使用する。
ダーティリードは防止されるが、ノンリピータブルリードとファントムリードが発生する可能性がある。

多くのOLTPアプリケーションで使用される。

REPEATABLE READ

MySQLのデフォルト分離レベル。
トランザクション開始時のスナップショットを使用する。
同じクエリを複数回実行しても、同じ結果が返される。
InnoDBでは、ネクストキーロックによりファントムリードも防止される。
多くのアプリケーションで適切なバランスを提供する。

SERIALIZABLE

最も高い保護レベルである。

全ての SELECT 文が暗黙的に SELECT ... FOR SHARE として実行される。
トランザクションが完全に直列化される。

金融取引等、完全な整合性が必要な場合に使用される。

読み取り異常の説明

分離レベルが低いと、以下の読み取り異常が発生する可能性がある。

• ダーティリード

  コミットされていない変更を読み取る。

  READ UNCOMMITTED で発生する。

  
  

• ノンリピータブルリード

  同じ行が複数回読み取られるたびに異なる値が返される。

  READ COMMITTED で発生する。

  
  

• ファントムリード

  同じクエリが異なる行セットを返す。

  READ COMMITTED で発生する。

  InnoDBのREPEATABLE READでは防止される。

分離レベルの設定方法

トランザクション分離レベルは、トランザクション単位、セッション単位、グローバル単位で設定できる。

 -- トランザクション単位で設定
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 START TRANSACTION;
 
 -- トランザクション処理
 COMMIT;
 
 -- セッション単位で設定
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
 -- グローバル単位で設定
 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
 -- 現在の分離レベルを確認
 SELECT @@transaction_isolation;

ロック機構

MySQLのInnoDBストレージエンジンは、複数のロック機構を提供してトランザクションの分離性を実現する。

共有ロック (S Lock)

複数のトランザクションが同時に保有できるロックである。
読み取り操作で使用される。

SELECT ... FOR SHARE 文で明示的に取得できる。

排他ロック (X Lock)

単一のトランザクションのみが保有できるロックである。
書き込み操作で使用される。

SELECT ... FOR UPDATE 文で明示的に取得できる。
他のトランザクションは共有ロックも排他ロックも取得できない。

インテンションロック

テーブルレベルで設定されるロックである。
行レベルのロックを取得する前に、テーブルレベルでロックの意図を示す。

• IS (Intention Shared)

  共有ロックを取得する意図を示す。

• IX (Intention Exclusive)

  排他ロックを取得する意図を示す。

ギャップロック

インデックスレコード間のギャップを保護するロックである。
新しいレコードの挿入を防止する。

REPEATABLE READ分離レベルでファントムリードを防止するために使用される。

ネクストキーロック

レコードロックとギャップロックを組み合わせたロックである。
インデックスレコードとその前のギャップをロックする。

ファントムリードを防止する。

ロック読み取り

SELECT 文に特定の句を追加することにより、明示的にロックを取得できる。

 -- 排他ロックを取得
 SELECT * FROM table WHERE condition FOR UPDATE;
 
 -- 共有ロックを取得
 SELECT * FROM table WHERE condition FOR SHARE;
 
 -- ロックが取得できない場合、待機せずにエラーを返す
 SELECT * FROM table WHERE condition FOR UPDATE NOWAIT;
 
 -- ロックされている行をスキップして処理を続行
 SELECT * FROM table WHERE condition FOR UPDATE SKIP LOCKED;

FOR UPDATE は在庫管理や予約システム等、排他的な更新が必要な場合に使用される。

FOR SHARE は他のトランザクションによる更新を防ぎつつ、複数のトランザクションが読み取りを行う場合に使用される。

NOWAIT と SKIP LOCKED は、キュー処理等で有用である。

セーブポイント

セーブポイントは、トランザクション内で部分的なロールバックを可能にする機能である。
複雑なトランザクション処理で、特定の時点までロールバックする場合に使用される。

構文

 -- セーブポイントを設定
 SAVEPOINT savepoint_name;
 
 -- セーブポイントまでロールバック
 ROLLBACK TO SAVEPOINT savepoint_name;
 
 -- セーブポイントを削除
 RELEASE SAVEPOINT savepoint_name;

使用例

以下の例では、複数のINSERT操作の途中で問題が発生した場合に、特定の時点までロールバックしている。

 START TRANSACTION;
 
 -- 最初のINSERT
 INSERT INTO table1 (col1, col2) VALUES (1, 'data1');
 
 -- 最初のセーブポイント
 SAVEPOINT sp1;
 
 -- 2番目のINSERT
 INSERT INTO table1 (col1, col2) VALUES (2, 'data2');
 
 -- 2番目のセーブポイント
 SAVEPOINT sp2;
 
 -- 3番目のINSERT
 INSERT INTO table1 (col1, col2) VALUES (3, 'data3');
 
 -- sp2までロールバック (3番目のINSERTだけが取り消される)
 ROLLBACK TO SAVEPOINT sp2;
 
 -- 最初と2番目のINSERTだけがコミットされる
 COMMIT;

特徴

• トランザクション内で複数のセーブポイントを設定できる。
• セーブポイントまでロールバックしても、トランザクション自体は継続する。
• セーブポイントは、InnoDBストレージエンジンのみサポートする。
• セーブポイントは、複雑なバッチ処理やETL処理で有用。

デッドロック

デッドロックは、複数のトランザクションが互いにロックを待ち合い、処理が進行しなくなる状態である。

発生原因

デッドロックは、以下に示すような状況で発生する。

• ロック取得順序の不一致

  トランザクションAがテーブル1をロックしてからテーブル2をロックしようとし、トランザクションBがテーブル2をロックしてからテーブル1をロックしようとする。

• 循環的なロック待機

  トランザクションAがトランザクションBのロック解放を待ち、トランザクションBがトランザクションAのロック解放を待つ。

• 長時間実行トランザクション

  トランザクションが長時間ロックを保持することで、他のトランザクションとのロック競合が発生しやすくなる。

InnoDBのデッドロック検出

InnoDBは、デッドロックを自動的に検出して、解決する機能を持つ。

• デッドロック検出はデフォルトで有効

  innodb_deadlock_detect=ON

• デッドロックが検出されると、変更行数が最小のトランザクションがロールバック対象に選定される。
• ロールバックされたトランザクションはエラーコード 1213 を返す。

 -- デッドロック検出を有効化 (デフォルト)
 SET GLOBAL innodb_deadlock_detect = ON;
 
 -- デッドロック検出を無効化 (高同時実行性環境で使用)
 SET GLOBAL innodb_deadlock_detect = OFF;

デッドロックの診断

デッドロック発生時の詳細情報は、SHOW ENGINE INNODB STATUS 文で確認できる。

 SHOW ENGINE INNODB STATUS;

このコマンドは、最後に発生したデッドロックの情報を表示する。
関与したトランザクション、ロック待機状況、ロールバックされたトランザクション等が確認できる。

デッドロックの回避方法

デッドロックを回避するには、以下に示す方法が有効である。

• ロック取得順序の統一

  全てのトランザクションで、テーブルやレコードに対するロック取得順序を統一する。

  
  

• トランザクション時間の短縮

  トランザクションを可能な限り短く保ち、ロック保持時間を最小化する。

  
  

• 適切なインデックスの使用

  インデックスを適切に設定し、不要なロック範囲を最小化する。

  
  

• 分離レベルの変更

  必要に応じて、分離レベルをREAD COMMITTEDに変更し、ロック競合を減らす。

  
  

• デッドロック発生時のリトライ処理

  アプリケーション側でデッドロックエラーを検出し、トランザクションを再実行する。

XAトランザクション (分散トランザクション)

XAトランザクションは、複数のデータベースやリソースマネージャにまたがる分散トランザクションを実現する。
X/Open XA仕様に基づき、2フェーズコミット (Two-Phase Commit) プロトコルを使用する。

概要

XAトランザクションは、複数のデータベースインスタンスやストレージエンジンにまたがるトランザクションを管理する。
全ての参加リソースが正常に準備できた場合のみ、全体がコミットされる。

いずれかのリソースが失敗した場合、全体がロールバックされる。

構文

XAトランザクションは、2つのフェーズで実行される。

 -- フェーズ 1: 準備フェーズ
 XA START 'xid';
 
 -- SQL文を実行
 INSERT INTO table1 VALUES (...);
 UPDATE table2 SET ...;
 XA END 'xid';
 XA PREPARE 'xid';
 
 -- フェーズ 2: コミットフェーズ
 XA COMMIT 'xid';
 
 -- またはロールバック
 XA ROLLBACK 'xid';
 
 -- プリペア状態のトランザクションを確認
 XA RECOVER;

xid は、XAトランザクションを識別する一意の識別子である。

特徴

• XAトランザクションは、InnoDBストレージエンジンのみサポートする。
• クラッシュリカバリー時に、XA RECOVER で未完了のトランザクションを確認できる。
• 分散データベースシステムやマイクロサービスアーキテクチャで使用される。
• 2フェーズコミットにより、全てのリソースの一貫性が保証される。

パフォーマンスと推奨事項

トランザクションのパフォーマンスを最適化し、問題を回避するための推奨事項を以下に示す。

トランザクション設計

• トランザクションを短く保つ。

  トランザクションは可能な限り短く (数秒以内を推奨) 保つ。

  長時間のトランザクションはロック競合を増やし、デッドロックのリスクを高める。

  
  

• 大量の更新は小分けにする。

  数万行以上の更新は、複数のトランザクションに分割して実行する。

  1度に大量の更新を行うと、ロック競合が発生しやすい。

  
  

• トランザクション外で時間の掛かる処理を実行する。

  ファイルI/O、外部API呼び出しなど、時間のかかる処理はトランザクション外で実行する。

  トランザクション内では、データベース操作のみに集中する。

分離レベルの選択

アプリケーションの要件に応じて、適切な分離レベルを選択する。

• READ UNCOMMITTED

  統計情報の収集等、精度が重要でない読み取りに使用する。

  
  

• READ COMMITTED

  多くのOLTPアプリケーションで適切。

  ノンリピータブルリードが許容される場合に使用する。

  
  

• REPEATABLE READ

  MySQLのデフォルトで、多くのアプリケーションに対応。

  一貫した読み取りが必要な場合に使用する。

  
  

• SERIALIZABLE

  金融取引など、完全な直列化が必要な場合に使用する。

  最も安全だが、最も低速。

ロック競合の最小化

ロック競合を最小化することにより、デッドロックを回避してパフォーマンスを向上できる。

• 適切なインデックスの作成

  WHERE句で使用されるカラムにインデックスを作成する。

  インデックスがないと、テーブル全体がロックされる可能性がある。

  
  

• クエリの最適化

  スキャン対象のレコード数を最小化する。

  不要な行をロックしないように、WHERE句を正確に記述する。

  
  

• 必要最小限の行のみロック

  SELECT ... FOR UPDATE は、必要な行のみに使用する。

  範囲ロックは避け、主キーによる行ロックを使用する。

バッチ処理の最適化

大量のINSERT文 や UPDATE文は、自動コミットを無効化してバッチ処理することで高速化できる。

 SET AUTOCOMMIT=0;
 
 -- 大量のINSERT処理
 INSERT INTO table1 VALUES (...);
 INSERT INTO table1 VALUES (...);
 INSERT INTO table1 VALUES (...);
 -- ... (数千行)
 
 -- 1括でコミット
 COMMIT;
 
 -- 自動コミットを元に戻す
 SET AUTOCOMMIT=1;

自動コミットを無効化することで、各INSERT文ごとのコミットオーバーヘッドを削減できる。
ただし、トランザクションサイズが大きすぎると、ロールバックログが肥大化するため、適度なサイズで分割することが推奨される。