0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@toamoku-20220418(toamoku)

【初心者向け】データベース設計の基礎 #5 - トランザクションとACID特性をマスターしよう!🔒

0
Last updated at Posted at 2026-02-24

1. はじめに

前回は、インデックスについて学びました。
検索を劇的に高速化する方法が身につきましたね!🚀

でも、こんな不安はありませんか?🤔

-- 銀行の振込処理
-- 1. Aさんの口座から1万円引く
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;

-- ここでエラーが発生したら...?😱💥

-- 2. Bさんの口座に1万円入れる
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;

-- Aさんのお金が消えた!?💸
-- Bさんはお金がもらえない!?

「途中でエラーが出たらどうする?」
「複数人が同時にアクセスしたらデータが壊れない?」
「停電したら?」

そんな不安を解決するのが、今回学ぶトランザクション(Transaction)とACID特性です!✨

1.1 前回のおさらい📝

  • インデックス - 検索を高速化する仕組み
  • B-Tree構造 - インデックスの内部構造
  • 複合インデックス - 複数カラムのインデックス
  • EXPLAIN - クエリの実行計画を確認

今回は、データの整合性と信頼性を守る仕組みを学びます!

2. トランザクション(Transaction)とは?🔄

トランザクションとは、複数の処理をまとめて、1つの処理単位として扱う仕組みです。

2.1 日常生活で例えると💳

🏦 銀行のATMでの振込

あなたが友達に1万円振り込むとき:

1. あなたの口座から1万円を引く 💸
2. 友達の口座に1万円を入れる 💰

❌ トランザクションがない場合

1. あなたの口座から1万円を引く ✅
   (残高:50,000円 → 40,000円)

2. 【ここで停電!💥】

3. 友達の口座に1万円を入れる ❌(実行されない)

結果:
- あなた:1万円減った 😭
- 友達:お金もらえず 😭
- 銀行:1万円消失!? 😱💸

大問題!

✅ トランザクションがある場合

トランザクション開始 🔒

1. あなたの口座から1万円を引く
2. 【ここで停電!💥】

トランザクション失敗 ❌
  ↓
全部なかったことに!(ロールバック)🔄
  ↓
結果:
- あなた:残高変わらず(50,000円) ✅
- 友達:残高変わらず ✅
- 銀行:データ整合性OK! ✅

安全!🎉

「全部成功」か「全部失敗」の2択!
これがトランザクションです!

🛒 オンラインショッピング

商品を購入するとき:

1. 在庫を1つ減らす
2. 注文レコードを追加
3. 顧客のポイントを増やす
4. 売上を記録

これらは全部成功するか、全部失敗するべき!

在庫だけ減って、注文が記録されないとか...
売上が記録されなくて、在庫だけ減るとか...

あってはならない!❌

2.2 データベースでの例💾

トランザクションなし❌

  • 商品購入処理
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;
  • ここでエラー!💥
INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 1, 1);

在庫は減ったけど、注文は記録されない😱

トランザクションあり✅

BEGIN;  -- トランザクション開始🔒

UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;
INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 1, 1);

COMMIT;  -- 成功!両方とも確定✅

-- もしエラーが出たら...
ROLLBACK;  -- 全部なかったことに!🔄

3. トランザクションの基本操作📝

3.1 4つの基本コマンド

コマンド 意味 説明
BEGIN 開始 トランザクションを開始する🔒
COMMIT 確定 変更を確定する✅
ROLLBACK 取り消し 変更を全て取り消す🔄
SAVEPOINT 保存点 途中地点を保存📍

3.2 基本的な使い方

パターン1:正常終了✅

-- トランザクション開始
BEGIN;

-- 複数の処理
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;

-- 全部成功!確定する
COMMIT;

結果:

  • 両方のUPDATEが確定される✅
  • データベースに反映される💾

パターン2：エラー発生❌

BEGIN;

UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
-- 成功✅

UPDATE accounts SET balance = balance + 10000 WHERE account_id = 999;
-- エラー!account_id=999は存在しない💥

-- エラーが出たので、全部なかったことに
ROLLBACK;

結果:

  • 両方のUPDATEが取り消される🔄
  • 最初の状態に戻る✅

パターン3：途中でやっぱりやめる🤔

BEGIN;

UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 2;

-- あれ?在庫足りないかも...やっぱりやめよう
SELECT stock_quantity FROM products WHERE product_id = 1;
-- 残り0になってる!これはまずい!

ROLLBACK;  -- 取り消す!

結果:

  • 両方のUPDATEが取り消される🔄
  • 在庫は元の数に戻る✅

3.3 データベース別の構文

MySQL

START TRANSACTION;  -- または BEGIN;
-- 処理
COMMIT;  -- または ROLLBACK;

PostgreSQL

BEGIN;
-- 処理
COMMIT;  -- または ROLLBACK;

SQLite

BEGIN TRANSACTION;
-- 処理
COMMIT;  -- または ROLLBACK;

基本は同じ! ✨

4. ACID特性🔬

トランザクションには、ACID特性という4つの重要な性質があります。

ACIDは、以下の頭文字:

  • Atomicity(原子性)
  • Consistency(一貫性)
  • Isolation(独立性/分離性)
  • Durability(永続性)

4.1 A - Atomicity(原子性)⚛️

「全部成功」か「全部失敗」の2択!

説明

トランザクション内の処理は、分割できない1つの単位として扱われる。
途中で失敗したら、全部なかったことになる!

例：銀行の振込

BEGIN;

-- 1. Aさんから1万円引く
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;

-- 2. Bさんに1万円入れる
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;

COMMIT;

✅ 両方成功する場合

Aさん:50,000円 → 40,000円 ✅
Bさん:30,000円 → 40,000円 ✅

合計:80,000円 → 80,000円 ✅
(お金は消えない、増えない)

❌ 途中で失敗した場合

1. Aさんから引く → 成功
2. Bさんに入れる → エラー!💥

自動でROLLBACK🔄
  ↓
Aさん:50,000円(元通り) ✅
Bさん:30,000円(変わらず) ✅

合計:80,000円(変わらず) ✅

原子性により、中途半端な状態にならない! 🎯

日常の例：料理🍳

❌ 原子性がない:
  卵を割る → 成功
  フライパンに入れる → 失敗(フライパンがない!)
  → 卵が無駄になる😭

✅ 原子性がある:
  準備チェック
  → フライパンがない!
  → 卵を割らない!
  → 無駄にならない✅

4.2 C - Consistency(一貫性)🔒

データベースのルール(制約)を必ず守る!

説明

トランザクション前後で、データベースの整合性が保たれる。
制約違反があれば、トランザクションは失敗する。

制約の例

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10,2) NOT NULL,
    CHECK (balance >= 0)  -- 残高はマイナス禁止!
);

例1：残高がマイナスになる場合❌

BEGIN;

-- Aさんの残高:1,000円
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
-- 残高が -9,000円になる!

-- CHECK制約違反!💥
-- 自動でROLLBACK🔄

ROLLBACK;

結果:

  • 残高は1,000円のまま✅
  • マイナスにならない✅

例2：外部キー制約違反❌

BEGIN;

-- 存在しないcustomer_id=999を参照
INSERT INTO orders (customer_id, product_id) VALUES (999, 1);
-- 外部キー制約違反!💥

ROLLBACK;

結果:

  • 注文は追加されない✅
  • データの整合性が保たれる✅

例3：ユニーク制約違反❌

ユニーク制約とは他のデータと重複するのを許しません。
例：メールアドレスなど

BEGIN;

-- emailはUNIQUE制約がある
INSERT INTO users (username, email) VALUES ('佐藤', 'tanaka@example.com');
-- すでに存在するメールアドレス!💥

ROLLBACK;

一貫性により、ルール違反のデータは絶対に入らない! 🛡️

日常の例：パズル🧩

❌ 一貫性がない:
  ピースを無理やり押し込む
  → 形が合わなくても入れちゃう😱

✅ 一貫性がある:
  形が合わないピースは入れられない
  → パズルのルールを守る✅

4.3 I - Isolation(独立性/分離性)🚪

複数のトランザクションが同時に実行されても、お互いに影響しない!

説明

あるトランザクションの途中経過は、他のトランザクションから見えない。

問題例：座席予約システム🎫

❌ 独立性がない場合

時刻:12:00:00
座席A:空席 ✅

--- 田中さん ---        --- 佐藤さん ---
12:00:01
座席Aを確認
→ 空席!✅

                        12:00:02
                        座席Aを確認
                        → 空席!✅

12:00:03
座席Aを予約
→ 成功!

                        12:00:04
                        座席Aを予約
                        → 成功!?

結果:
座席Aに2人の予約!?😱💥
ダブルブッキング!

✅ 独立性がある場合

--- 田中さん ---        --- 佐藤さん ---
BEGIN;
座席Aを確認
→ 空席!✅
座席Aをロック🔒

                        BEGIN;
                        座席Aを確認
                        → ロックされてる!⏳
                        → 待機...

座席Aを予約
→ 成功!
COMMIT;
ロック解除🔓

                        → ロック解除された!
                        座席Aを確認
                        → すでに予約済み❌
                        → 予約できない
                        ROLLBACK;

結果:
座席Aは田中さんだけの予約✅
ダブルブッキング回避!🎉

データベースでの例

-- 田中さんのトランザクション
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 残高:50,000円
-- FOR UPDATE でロック🔒

UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
-- 残高:40,000円(まだCOMMITしていない)

-- 【この時点で、佐藤さんがアクセス】
-- 佐藤さんには50,000円と見える!
-- (田中さんの変更はまだ見えない)

COMMIT;
-- ここで初めて、40,000円が確定✅

独立性により、同時アクセスでもデータが壊れない! 🛡️

日常の例：トイレ🚽

❌ 独立性がない:
  ドアに鍵がない
  → 誰かが使ってても入れちゃう😱

✅ 独立性がある:
  ドアに鍵がある🔒
  → 使用中は他の人は入れない
  → プライバシーが守られる✅

4.4 D - Durability(永続性)💾

一度確定したデータは、絶対に消えない!

説明

COMMITが成功したら、停電やシステムクラッシュが起きても、
データは必ず保存される。

例：注文の確定

BEGIN;

INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 1, 1);
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;

COMMIT;  -- ✅ 確定!

-- 【ここで停電!💥⚡】
-- 【サーバーが落ちる!】

-- サーバー再起動後...
SELECT * FROM orders WHERE customer_id = 1;
-- ちゃんと注文データが残ってる!✅

SELECT stock_quantity FROM products WHERE product_id = 1;
-- 在庫もちゃんと減ってる!✅

永続性により、確定したデータは絶対に消えない! 💪

仕組み：WAL(Write-Ahead Logging)📝

1. COMMITする前に、変更内容をログファイルに書き込む✍️
2. ログファイルへの書き込みが成功したら、COMMITを確定
3. 実際のデータファイルへの書き込みは、後でゆっくり行う

停電が起きても...
  ↓
ログファイルが残っている!
  ↓
再起動時にログから復元!🔄
  ↓
データが失われない!✅

日常の例：手紙✉️

❌ 永続性がない:
  口頭で伝える
  → 忘れられる可能性😅

✅ 永続性がある:
  紙に書いて郵送📨
  → 記録が残る
  → 後で確認できる✅

5. ACID特性のまとめ表📊

特性 意味 保証すること 例
Atomicity
原子性		 All or Nothing 全部成功か全部失敗 振込:引くのと入れるの両方
Consistency
一貫性		 ルールを守る 制約違反を許さない 残高マイナス禁止
Isolation
独立性		 他に影響しない 同時実行でも安全 座席のダブルブッキング防止
Durability
永続性		 消えない 確定したら永久保存 停電してもデータ残る

ACIDがあるから、データベースは信頼できる! 🔒✨

6. 分離レベル(Isolation Level)🎚️

独立性(Isolation)には、実はレベルがあります!

6.1 なぜレベルがあるの?🤔

完全な独立性 = 遅い🐢
ゆるい独立性 = 速い🚀

というトレードオフがあるため!

6.2 4つの分離レベル

レベル 読み方 速度 安全性 使いどころ
READ UNCOMMITTED 未確定読み取り 最速🚀 最弱😰 ほぼ使わない
READ COMMITTED 確定読み取り 速い⚡ 普通😐 デフォルト
REPEATABLE READ 再読み取り可能 普通🏃 高い😊 MySQL推奨
SERIALIZABLE 直列化 遅い🐢 最強🔒 金融システム

レベル1:READ UNCOMMITTED(未確定読み取り)

他のトランザクションの未確定な変更も読める!

-- 田中さん
BEGIN;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
-- まだCOMMITしていない!

-- 【この時点で佐藤さんがアクセス】

-- 佐藤さん(READ UNCOMMITTED)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- 結果:40,000円(減った後の金額が見える!)

-- 【田中さんがROLLBACK】
ROLLBACK;  -- やっぱりやめた!

-- 佐藤さんが見た40,000円は「幻」だった!😱
-- これを「ダーティリード(汚れた読み取り)」という

ダーティリード(Dirty Read)が起こる!

❌ ほぼ使われない!危険!

レベル2：READ COMMITTED(確定読み取り)

確定した変更だけ読める!

-- 田中さん
BEGIN;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
-- まだCOMMITしていない!

-- 【佐藤さんがアクセス】

-- 佐藤さん(READ COMMITTED)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- 結果:50,000円(変更前の金額)✅

-- 【田中さんがCOMMIT】
COMMIT;

-- 【佐藤さんがもう一度読む】
SELECT balance FROM accounts WHERE account_id = 1;
-- 結果:40,000円(変更後の金額)

-- 同じトランザクション内で結果が変わった!
-- これを「ファジーリード(曖昧な読み取り)」という

ファジーリード(Fuzzy Read/Non-Repeatable Read)が起こる!

✅ PostgreSQLのデフォルト
⚠️ 同じトランザクション内で結果が変わる可能性がある

レベル3:REPEATABLE READ(再読み取り可能)

同じトランザクション内では、何度読んでも同じ結果!

-- 佐藤さん(REPEATABLE READ)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;
-- 結果:50,000円

-- 【田中さんがCOMMIT】
-- (残高を40,000円に変更)

-- 【佐藤さんがもう一度読む】
SELECT balance FROM accounts WHERE account_id = 1;
-- 結果:50,000円(変わらない!)✅

COMMIT;

-- トランザクション終了後にもう一度読む
SELECT balance FROM accounts WHERE account_id = 1;
-- 結果:40,000円(田中さんの変更が反映される)

ただし、ファントムリードは起こる可能性がある!

ファントムリード(Phantom Read)とは?👻

-- 佐藤さん
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 結果:10件

-- 【田中さんが新しい注文を追加】
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- 【佐藤さんがもう一度カウント】
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 結果:11件(増えてる!👻)

-- 「幻」のように新しいデータが現れた!

✅ MySQLのデフォルト
✅ ほとんどの用途で十分!

レベル4：SERIALIZABLE(直列化)

完全な独立性!最も安全!

-- 佐藤さん(SERIALIZABLE)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM accounts WHERE account_id = 1;

-- 【田中さんがアクセス】
BEGIN;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
-- ブロックされる!⏳
-- 佐藤さんのトランザクションが終わるまで待つ

-- 【佐藤さんがCOMMIT】
COMMIT;

-- 【田中さんのUPDATEがやっと実行される】
-- 成功!
COMMIT;

ファントムリードも起こらない!

✅ 最も安全!
❌ 最も遅い!
💰 金融システムなど、絶対にミスが許されない場合に使う

6.3 分離レベルの比較表📊

分離レベル ダーティリード ファジーリード ファントムリード 速度
READ UNCOMMITTED 起こる❌ 起こる❌ 起こる❌ 最速🚀
READ COMMITTED 防げる✅ 起こる❌ 起こる❌ 速い⚡
REPEATABLE READ 防げる✅ 防げる✅ 起こる❌ 普通🏃
SERIALIZABLE 防げる✅ 防げる✅ 防げる✅ 遅い🐢

6.4 分離レベルの設定方法

MySQL

-- セッション単位で設定
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 次のトランザクションだけ設定
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

PostgreSQL

-- セッション単位で設定
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 次のトランザクションだけ設定
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

6.5 どれを使うべき?🤔

💰 金融システム(銀行、決済)
  → SERIALIZABLE
  (絶対にミスできない!)

📊 一般的なWebアプリ
  → REPEATABLE READ または READ COMMITTED
  (デフォルトでOK!)

📈 リアルタイム分析、レポート
  → READ COMMITTED
  (最新データを見たい)

🚫 READ UNCOMMITTED
  → 使わない!
  (危険すぎる)

7. ロック(Lock)🔒

トランザクションの独立性を保つために、ロック(鍵) を使います!

7.1 ロックの種類

1. 共有ロック(Shared Lock)📖

「読み取り専用」のロック

SELECT * FROM accounts WHERE account_id = 1 FOR SHARE;
-- または
SELECT * FROM accounts WHERE account_id = 1 LOCK IN SHARE MODE;

特徴:

  • 他のトランザクションも読める✅
  • 他のトランザクションは更新できない❌
田中さん:SELECT ... FOR SHARE; ✅
佐藤さん:SELECT ... FOR SHARE; ✅(同時に読める!)

鈴木さん:UPDATE ... ⏳(待たされる)

2. 排他ロック(Exclusive Lock)🔐

「書き込み専用」のロック

SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

特徴:

  • 他のトランザクションは読めない❌
  • 他のトランザクションは更新できない❌
田中さん:SELECT ... FOR UPDATE; 🔒

佐藤さん:SELECT ... FOR SHARE; ⏳(待たされる)
鈴木さん:UPDATE ... ⏳(待たされる)

7.2 ロックの粒度🔍

粒度 説明 例
行ロック 特定の行だけロック 口座ID=1だけロック
テーブルロック テーブル全体をロック accountsテーブル全体をロック
データベースロック データベース全体をロック (ほぼ使わない)

行ロック(Row Lock)の例

BEGIN;

-- account_id=1 の行だけロック🔒
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;

COMMIT;

-- この間、account_id=2 は他の人が自由に使える!✅

細かいロック = 同時実行性が高い! 🚀

テーブルロック(Table Lock)の例

BEGIN;

-- accountsテーブル全体をロック🔒
LOCK TABLES accounts WRITE;

-- 誰も読めない、書けない!
UPDATE accounts SET balance = balance * 1.01;  -- 全員に1%の利息

UNLOCK TABLES;
COMMIT;

大きなロック = 同時実行性が低い! 🐢

8. デッドロック(Deadlock)💀

お互いにロックを待ち続けて、永久に進まない状態!

8.1 デッドロックの例

-- 田中さん
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
-- 🔒 口座1をロック

-- 【佐藤さんがアクセス】

-- 佐藤さん
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 2;
-- 🔒 口座2をロック

-- 【田中さんが続きを実行】
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
-- ⏳ 口座2のロック待ち(佐藤さんが持っている)

-- 【佐藤さんが続きを実行】
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 1;
-- ⏳ 口座1のロック待ち(田中さんが持っている)
  • お互いに相手のロックを待つ = デッドロック!💀発生

図解

田中さん         佐藤さん
   |               |
   |--🔒口座1      |
   |               |--🔒口座2
   |               |
   |--⏳口座2待ち  |
   |               |--⏳口座1待ち
   |               |
  待つ←-----------→待つ
  
永久に進まない!💀

8.2 データベースの対処法

ほとんどのデータベースは、自動でデッドロックを検出して、片方をROLLBACKする!

-- 田中さん
ERROR: deadlock detected
-- 自動でROLLBACKされる🔄

-- 佐藤さん
-- ロックが解放される
-- 処理が続行できる✅
COMMIT;

8.3 デッドロックを防ぐ方法💡

1. ロックの順序を統一する

-- ❌ 悪い例:順序がバラバラ
トランザクション1: 口座1 → 口座2
トランザクション2: 口座2 → 口座1
→ デッドロックの可能性!💀

-- ✅ 良い例:順序を統一
トランザクション1: 口座1 → 口座2
トランザクション2: 口座1 → 口座2
→ デッドロックが起こらない!✅

-- 常にaccount_idの小さい順にロック
BEGIN;

-- account_id を並び替える
SET @account1 = LEAST(1, 2);  -- 小さい方
SET @account2 = GREATEST(1, 2);  -- 大きい方

-- 順番にロック
UPDATE accounts SET balance = balance - 1000 WHERE account_id = @account1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = @account2;

COMMIT;

2. トランザクションを短くする

-- ❌ 悪い例:長いトランザクション
BEGIN;

SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 🔒 ロック

-- 複雑な計算(10秒かかる)...
-- ロックを長時間保持!

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;

COMMIT;

-- ✅ 良い例:短いトランザクション
-- まず計算を先に行う(ロックなし)
-- 計算結果:1000円引く

BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 🔒 ロック

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
-- すぐに実行!

COMMIT;
-- すぐにロック解放🔓

3. タイムアウトを設定する

-- ロック待ちのタイムアウト(MySQL)
SET innodb_lock_wait_timeout = 5;  -- 5秒でタイムアウト

BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 5秒待ってもロックが取れなければエラー!

9. 実践例：ECサイトの購入処理🛒

9.1 シナリオ

商品を購入する処理を、トランザクションで実装します!

処理内容

  1. 在庫を確認
  2. 在庫を減らす
  3. 注文を記録
  4. 顧客のポイントを増やす

実装

BEGIN;

-- 1. 在庫を確認(排他ロック)
SELECT stock_quantity 
FROM products 
WHERE product_id = 1 FOR UPDATE;

-- 結果:10個

-- 在庫チェック(アプリケーション側)
-- if (stock_quantity < 購入数) { ROLLBACK; return; }

-- 2. 在庫を減らす
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 1;

-- 3. 注文を記録
INSERT INTO orders (customer_id, product_id, quantity, total_amount) 
VALUES (1, 1, 1, 5000);

-- 4. 顧客のポイントを増やす(購入額の1%)
UPDATE customers 
SET points = points + 50 
WHERE customer_id = 1;

-- すべて成功!
COMMIT;

エラーハンドリング付き

BEGIN;

-- エラーハンドリング(擬似コード)
TRY {
    -- 在庫確認
    SELECT stock_quantity FROM products WHERE product_id = 1 FOR UPDATE;
    
    IF (stock_quantity < 1) {
        THROW '在庫不足!';
    }
    
    -- 在庫を減らす
    UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;
    
    -- 注文を記録
    INSERT INTO orders (customer_id, product_id, quantity, total_amount) 
    VALUES (1, 1, 1, 5000);
    
    -- ポイント付与
    UPDATE customers SET points = points + 50 WHERE customer_id = 1;
    
    -- 成功!
    COMMIT;
    
} CATCH {
    -- エラーが起きたら全部なかったことに
    ROLLBACK;
    
    -- エラーをログに記録
    -- ユーザーにエラーメッセージを返す
}

同時購入の処理

田中さん:商品1を購入(在庫:10個)
佐藤さん:商品1を購入(在庫:10個)

--- 田中さん ---          --- 佐藤さん ---
BEGIN;
在庫確認(10個)
🔒 ロック
                           BEGIN;
                           在庫確認
                           ⏳ 待つ(田中さんのロック)

在庫を減らす(9個)
注文記録
COMMIT;
🔓 ロック解放
                           🔓 ロック取得!
                           在庫確認(9個)✅
                           在庫を減らす(8個)
                           注文記録
                           COMMIT;

結果:
在庫:8個✅
田中さんの注文:✅
佐藤さんの注文:✅

正しく処理できた!🎉

10. SAVEPOINT(保存点)📍

トランザクションの途中地点を保存できます!

10.1 使い方

BEGIN;

-- 処理1
INSERT INTO orders (customer_id, total_amount) VALUES (1, 5000);

-- ここで保存点を作成
SAVEPOINT order_created;

-- 処理2
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 1);

-- 処理3
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;

-- 処理3でエラー!(在庫不足)
-- 処理2と3だけ取り消したい!

-- 保存点まで戻る🔄
ROLLBACK TO SAVEPOINT order_created;

-- 処理1は残る!✅
-- 処理2と3は取り消される!🔄

COMMIT;

10.2 複数の保存点

BEGIN;

INSERT INTO orders (customer_id) VALUES (1);
SAVEPOINT sp1;  -- 保存点1📍

INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
SAVEPOINT sp2;  -- 保存点2📍

UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;
SAVEPOINT sp3;  -- 保存点3📍

-- sp2まで戻る
ROLLBACK TO SAVEPOINT sp2;
-- 保存点2以降が取り消される

-- sp1まで戻る
ROLLBACK TO SAVEPOINT sp1;
-- 保存点1以降が取り消される

COMMIT;

10.3 実用例：複数商品の購入

BEGIN;

-- 注文を作成
INSERT INTO orders (customer_id, total_amount) VALUES (1, 10000);
SET @order_id = LAST_INSERT_ID();

-- 商品1を処理
SAVEPOINT product_1;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (@order_id, 1, 1);
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 1;

-- 商品2を処理
SAVEPOINT product_2;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (@order_id, 2, 1);
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 2;
-- エラー!在庫不足!💥

-- 商品2だけ取り消す
ROLLBACK TO SAVEPOINT product_2;

-- 商品1は成功、商品2は失敗として処理
COMMIT;

11. 実践演習:銀行の振込処理💰

11.1 問題

銀行の振込処理を、トランザクションを使って実装してください!

要件

  1. Aさんの口座から1万円を引く
  2. Bさんの口座に1万円を入れる
  3. 振込履歴を記録する
  4. エラーが起きたら、全部なかったことにする

テーブル定義

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(50),
    balance DECIMAL(10,2) NOT NULL,
    CHECK (balance >= 0)  -- 残高はマイナス禁止!
);

CREATE TABLE transfers (
    transfer_id INT AUTO_INCREMENT PRIMARY KEY,
    from_account_id INT,
    to_account_id INT,
    amount DECIMAL(10,2),
    transfer_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO accounts VALUES
(1, '田中太郎', 50000),
(2, '佐藤花子', 30000);

🤔 考えてみよう!

  1. どの順序で処理すべき?
  2. どこでロックを取るべき?
  3. エラーハンドリングはどうする?
解答例を見る👇

✅ 解答例

BEGIN;

-- 1. Aさんの残高を確認(排他ロック)
SELECT balance 
FROM accounts 
WHERE account_id = 1 FOR UPDATE;

-- 結果:50,000円

-- 残高チェック
-- if (balance < 10000) { ROLLBACK; return '残高不足!'; }

-- 2. Bさんの口座もロック(デッドロック防止のため、順序を統一)
SELECT balance 
FROM accounts 
WHERE account_id = 2 FOR UPDATE;

-- 3. Aさんの口座から引く
UPDATE accounts 
SET balance = balance - 10000 
WHERE account_id = 1;

-- CHECK制約により、マイナスになるとエラー!
-- (balance >= 0 の制約)

-- 4. Bさんの口座に入れる
UPDATE accounts 
SET balance = balance + 10000 
WHERE account_id = 2;

-- 5. 振込履歴を記録
INSERT INTO transfers (from_account_id, to_account_id, amount) 
VALUES (1, 2, 10000);

-- すべて成功!
COMMIT;

結果を確認

-- 残高確認
SELECT * FROM accounts;
account_id account_holder balance
1 田中太郎 40000 ✅
2 佐藤花子 40000 ✅ 
-- 履歴確認
SELECT * FROM transfers;
transfer_id from_account_id to_account_id amount transfer_date
1 1 2 10000 2026-02-20 15:30:00

完璧! 🎉

エラーケース1：残高不足

BEGIN;

-- Aさんの残高：40,000円
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;

-- 5万円引こうとする(残高不足!)
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 1;
-- エラー!CHECK制約違反!💥

-- 自動でROLLBACK🔄
ROLLBACK;

-- 結果:Aさんの残高は40,000円のまま✅

エラーケース2:存在しない口座

BEGIN;

UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
-- 成功

UPDATE accounts SET balance = balance + 10000 WHERE account_id = 999;
-- エラー!account_id=999は存在しない!💥

ROLLBACK;

-- 結果:両方とも取り消される✅

12. まとめ🎓

お疲れ様でした!🎉
トランザクションとACID特性について、しっかり学べましたね!

12.1 今回学んだこと📚

トランザクションとは

  • 複数の処理をまとめて、1つの単位として扱う仕組み🔄
  • 「全部成功」か「全部失敗」の2択!
  • BEGIN、COMMIT、ROLLBACKで制御

ACID特性🔬

  1. Atomicity(原子性) - All or Nothing⚛️
  2. Consistency(一貫性) - ルールを守る🔒
  3. Isolation(独立性) - 他に影響しない🚪
  4. Durability(永続性) - 消えない💾

分離レベル🎚️

  1. READ UNCOMMITTED - 使わない!❌
  2. READ COMMITTED - PostgreSQLのデフォルト
  3. REPEATABLE READ - MySQLのデフォルト✅
  4. SERIALIZABLE - 金融システム向け💰

ロック🔒

  • 共有ロック - 読み取り専用📖
  • 排他ロック - 書き込み専用🔐
  • デッドロック - お互いに待つ💀

覚えておくこと💡

  • ✅ データの整合性が重要な処理には必ずトランザクション
  • ✅ エラーハンドリングを忘れずに
  • ✅ ロックの順序を統一してデッドロック防止
  • ✅ トランザクションは短く保つ

12.2 実務での使い方🏢

1. 設計時:どの処理をトランザクションにまとめるか考える
     ↓
2. 実装時:BEGIN、COMMIT、ROLLBACKを適切に使う
     ↓
3. テスト時:エラーケースも必ずテスト
     ↓
4. 運用時:デッドロックを監視
     ↓
5. 改善:遅いトランザクションを最適化

12.3 次回予告🚀

次回は実践的なテーブル設計パターン集について学びます!

「多対多のリレーションはどう実装する?」
「履歴データはどう管理する?」
「論理削除と物理削除、どっちを使う?」
「階層構造はどう表現する?」

実務でよく使うテーブル設計のパターンを、
具体例を交えながらわかりやすく解説します!

お楽しみに!👋

データベース設計基礎シリーズ

  1. データベース設計の基本概念(ER図、エンティティ)
  2. SQLの基本をマスターしよう
  3. 正規化入門(第1〜第3正規形)
  4. インデックスの仕組みと使い方
  5. トランザクションとACID特性 ← 今回✅
  6. 実践的なテーブル設計パターン集 ← 次回
  7. よくあるアンチパターンと対策

💬 質問や感想があれば、コメント欄でお気軽にどうぞ!
👍 役に立ったら、いいね&ストックをお願いします!

それでは、また次回!🙌

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?