MySQLのトランザクションとACID特性に関する詳細な解説
トランザクションは、データ操作の原子性を保証します。つまり、トランザクションは完全に成功するか、完全に失敗するかのいずれかです。
このレッスンでは、トランザクションの原理と使用方法について解説します。
graph TB
A[START TRANSACTION] --> B[ExecuteSQLOperation]
B --> C{Was it successful?？}
C -->|All successful| D[COMMIT]
C -->|Partial failure| E[ROLLBACK]
B --> F[SAVEPOINT Save Point]
F --> G[ROLLBACK TO sp]
G --> B
D --> H[Data Persistence]
E --> I[Data Restored to Before Transaction]
1. 学習内容
- トランザクションのACID特性
- トランザクションの開始 / コミット / ロールバック
- SAVEPOINT セーブポイント
- 自動コミット (AUTOCOMMIT)
- トランザクションの隔離レベル
2. 銀行振込をめぐる実話
(1) 課題：転送が途中で失敗する
AがBに100元を送金する：
SQL
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Success
-- At this point, the system crashes
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Not executed
結果：Aは100不足し、Bは何も受け取れなかった――そのお金は跡形もなく消えてしまった。
(2) 取引の解決策
SQL
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Submit only if both are successful
3. ACIDの特性
| 機能 | 説明 | 実装 |
|---|---|---|
| Aトミック性 | トランザクションは分割不可能な作業単位である | アンドゥログ |
| C一貫性 | トランザクションの前後でデータの一貫性が保たれる | アプリケーション層 + 制約 |
| Isolation | 並行するトランザクションは互いに干渉しない | ロック + MVCC |
| D耐久性 | 送信後のデータは永続的に保存される | リドゥログ |
4. トランザクション操作
▶ 例：基本的なトランザクション
SQL
-- Start Transaction
START TRANSACTION;
-- Perform an operation
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Commit Transaction
COMMIT;
-- Or roll back the transaction
ROLLBACK;
▶ 例：SAVEPOINT
SQL
START TRANSACTION;
INSERT INTO orders (customer_id, amount) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 1);
SAVEPOINT sp2;
-- Roll back to sp1（Retain the first item INSERT）
ROLLBACK TO sp1;
-- Submit（Keep only the first one INSERT）
COMMIT;
5. 自動送信
▶ 例：AUTOCOMMIT
SQL
-- View Auto-Submit Status
SELECT @@autocommit;
-- Turn off Auto-Submit
SET autocommit = 0;
-- Enable Auto-Submit
SET autocommit = 1;
| オートコミット | 説明 |
|---|---|
| 1（デフォルト） | 各ステートメントを自動コミット |
| 0 | 手動でのCOMMITが必要です |
6. トランザクションの隔離レベル
| 隔離レベル | ダーティ・リード | 非再現性リード | ファントム・リード | パフォーマンス |
|---|---|---|---|---|
| 未コミットを読み取る | ✅ | ✅ | ✅ | 最高 |
| 読み取りコミット | ❌ | ✅ | ✅ | 高 |
| 繰り返し読み取り (デフォルト) | ❌ | ❌ | ✅ | 中 |
| SERIALIZABLE | ❌ | ❌ | ❌ | 最低 |
▶ 例：分離レベルの設定
SQL
-- View the current isolation level
SELECT @@transaction_isolation;
-- Set the isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
7. 並行処理の問題の実証
(1) ダーティ・リード
SQL
-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = 1000 WHERE id = 1;
-- Not committed
-- Transaction B (READ UNCOMMITTED)
SELECT balance FROM accounts WHERE id = 1; -- Read 1000 (Dirty Data)
-- Transaction A
ROLLBACK; -- Rollback
-- Transaction B: data read is now invalid
(2) 非反復リード
SQL
-- Transaction A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- Read 500
-- Transaction B
UPDATE accounts SET balance = 1000 WHERE id = 1;
COMMIT;
-- Transaction A
SELECT balance FROM accounts WHERE id = 1; -- Read 1000 (Inconsistent)
COMMIT;
❓ よくある質問
Q MySQL のデフォルトの隔離レベルは何ですか？
A REPEATABLE READ です。MVCC とギャップロックを用いて実装されています。
Q トランザクションがコミットされなかった場合はどうなりますか？
A ロックは維持されたままとなり、他のトランザクションは待機中にタイムアウトする可能性があります。
Q DDLはロールバックできますか？
A いいえ、DDLは自動的にコミットされるため、ロールバックすることはできません。
Q 「暗黙のコミット」とはどういう意味ですか？
A DDL文（CREATE/ALTER/DROP）が実行されると、現在のトランザクションは自動的にコミットされます。これを「暗黙のコミット」と呼びます。
Q 大規模なトランザクションにはどのようなリスクがありますか？
A ロック期間の長期化、アンドゥログの肥大化、およびマスター・スレーブ間のレイテンシの増加です。これらを小さなトランザクションに分割することをお勧めします。
📖 まとめ
- トランザクションは、操作の原子性を保証します。トランザクションは、完全に成功するか、完全に失敗するかのいずれかです。
- ACID：原子性、一貫性、隔離性、永続性
- COMMIT：コミット、ROLLBACK：ロールバック、SAVEPOINT：セーブポイント
- 隔離レベルは、同時実行に関連する問題（ダーティリード、非再現性リード、ファントムリード）を制御します。
- MySQLのデフォルトはREPEATABLE READです
📝 練習問題
-
基本問題（難易度：⭐）：口座Aから口座Bへ100を振り替えるトランザクションを作成してください。
-
上級問題（難易度 ⭐⭐）：SAVEPOINT を使用して、部分的なロールバックを実装してください。
-
チャレンジ問題（難易度：⭐⭐⭐）：非反復読み取りの問題を実証し、REPEATABLE READ 隔離レベルを用いて解決してください。
