Database Locks
一部のコンテンツは LLM によって生成されており、まだ手動で検証されていません。
データベースのロック機構 は、並行 アクセスを制御 し、データの一貫性 と整合性 を確保 するために使用 される。
Pessimistic / Optimistic Lock
悲観的ロック（Pessimistic Lock）
データ競合 が頻繁 に発生 する環境 に適 している。データの安全性 を確保 できるが、システムの並行性 が低下 する。
楽観的ロック（Optimistic Lock）
読 み取 りが多 く書 き込 みが少 ないシナリオに適 している。競合 が少 ない場合 、システムパフォーマンスを効果的 に向上 させる。ただし、競争 が激 しい場合 は過度 なリトライが発生 する可能性 がある。
sequenceDiagram
participant A as ClientA
participant B as ClientB
participant DB as Database
B ->> DB: read product
DB ->> B: return product(Quantity=10, Version=1)
A ->> DB: read product
DB ->> A: return product(Quantity=10, Version=1)
B ->> DB: update product(Quantity=8)
DB ->> DB: update quantity, incr version(Version=2) success
A ->> DB: update product(Quantity=9, Version=1)
DB ->> DB: version checked failed(Version != 1)
note over A, DB: conflict!
DB ->> A: OptimisticLockErr
DB ロックの種類
ロック範囲による分類
| ロックタイプ | 説明 |
|---|---|
| Row-Level Lock | テーブル内 の特定 の行 をロックし、他 の行 への同時 アクセスを許可 |
| Page-Level Lock | データベース内 の特定 のページ（固定 サイズのデータブロック）をロック |
| Table-Level Lock | テーブル全体 をロック。実装 は簡単 だが並行性 が大幅 に低下 |
ロックモードによる分類
| ロックタイプ | 説明 |
|---|---|
| Shared Lock (S Lock) | 共有 ロック。複数 のトランザクションが同時 にリソースを読 み取 れるが変更 はできない |
| Exclusive Lock (X Lock) | 排他 ロック。トランザクションがリソースを読 み書 き可能 。保持中 は他 のトランザクションはロックを取得 できない |
| Update Lock (U Lock) | 更新 ロック。トランザクションがリソースを更新 しようとする際 のデッドロックを防止 |
その他のロックタイプ
| ロックタイプ | 説明 |
|---|---|
| Schema Lock | スキーマロック。データベースオブジェクトの構造 を保護 |
| Bulk Update Lock (BU Lock) | 一括 更新 ロック。一括挿入 操作 時 に使用 し、必要 なロック数 を削減 してパフォーマンスを向上 |
| Key-Range Lock | キー範囲 ロック。インデックスデータでファントムリードを防止 |
MySQL / PostgreSQL Lock サポート
| Lock 種類 | MySQL (InnoDB) | PostgreSQL | 備考 |
|---|---|---|---|
| Row-Level Lock | ✅ 自動 | ✅ 自動 | InnoDB はデフォルトで行 レベルロック。エンジンがクエリに基 づいて自動選択 |
| Page-Level Lock | ❌ | ❌ | 両方 ともサポートなし。SQL Server 固有 |
| Table-Level Lock | ✅ LOCK TABLES | ✅ LOCK TABLE | 手動 指定 可能 だが、通常 エンジンが自動処理 |
| Shared Lock (S) | ✅ SELECT ... FOR SHARE | ✅ SELECT ... FOR SHARE | MySQL 8.0+ で FOR SHARE 構文 をサポート |
| Exclusive Lock (X) | ✅ SELECT ... FOR UPDATE | ✅ SELECT ... FOR UPDATE | 両方 とも同 じ構文 |
| Update Lock (U) | ❌ | ❌ | SQL Server 固有 。MySQL/PostgreSQL にはこの概念 がない |
| Schema Lock | ✅ Metadata Lock | ✅ AccessExclusiveLock | DDL 操作時 に自動取得 |
| Bulk Update Lock | ❌ | ❌ | SQL Server 固有 |
| Key-Range Lock | ✅ Gap Lock / Next-Key Lock | ✅ Predicate Lock | ファントムリード防止 に使用 |
MySQL InnoDB のロック機構
は大半
がストレージエンジンによって SQL 文
と分離
レベルに基
づいて自動選択
される。ユーザーは Row Lock や Gap Lock を直接指定
できない。
Row Lock 競合の軽減
| 戦略 | 説明 |
|---|---|
| トランザクション範囲 の縮小 | 必要 な行 のみロックし、早 めに COMMIT |
| 長時間 ロック保持 の回避 | トランザクション内 で時間 のかかる操作 （API 呼 び出 しなど）を避 ける |
| 適切 なインデックスの使用 | インデックスなしの UPDATE/DELETE は Table Lock に昇格 する可能性 |
| 分割処理 | 大量 更新時 は分割 実行 し、一度 にロックする行数 を減 らす |
一括更新のベストプラクティス
分割 UPDATE + LIMIT（アプリケーション層ループ）
アプリケーション層 で繰 り返 し実行 し、影響行数 が 0 になるまで継続 ：
-- 単発実行、アプリケーション層で affected_rows を判定して継続を決定
UPDATE orders
SET status = 'archived'
WHERE created_at < '2024-01-01' AND status = 'completed'
LIMIT 1000;アプリケーション層 ロジック（擬似 コード）：
while True:
affected = db.execute(UPDATE ... LIMIT 1000)
if affected == 0:
break
sleep(0.1) # オプション：ロック競合軽減サブクエリを使用 した一括更新 （推奨 ）
アプリケーション層 で SQL を直接組 み立 て可能 。一時 テーブル不要 ：
UPDATE user_accounts ua
INNER JOIN (
-- ID を必ずソートしてデッドロックを防止
SELECT 1 as user_id, 50.00 as amount UNION ALL
SELECT 2, 100.00 UNION ALL
SELECT 3, 75.50
ORDER BY user_id ASC
) as temp ON ua.user_id = temp.user_id
SET ua.balance = ua.balance + temp.amount
WHERE ua.balance + temp.amount >= 0; -- 楽観的チェックを維持**ORDER BY ソートの重要性
**：複数行
更新時
、異
なるトランザクションが異
なる順序
で同
じデータをロックするとデッドロックが発生
。PK で統一
ソートすることで、すべてのトランザクションが同
じ順序
でロックを取得
。
一時テーブルを使用した一括更新
データ量 が多 い場合 や複数回 クエリが必要 な場合 に適用 ：
-- 1. 更新対象の ID を一時テーブルに書き込む
CREATE TEMPORARY TABLE tmp_update_ids AS
SELECT id FROM orders WHERE created_at < '2024-01-01' LIMIT 10000;
-- 2. JOIN で一括更新
UPDATE orders o
INNER JOIN tmp_update_ids t ON o.id = t.id
SET o.status = 'archived';
-- 3. 一時テーブルをクリーンアップ
DROP TEMPORARY TABLE tmp_update_ids;autocommit=0 一括
コミットについて：一括
INSERT では効果
が顕著
。UPDATE でも有効
だが、トランザクションサイズに注意
。大
きすぎるトランザクションは undo log の肥大化
やロック保持時間
の長期化
を招
くため、1000〜5000 件
ごとにコミットすることを推奨
。
Gap Lock
ギャップロックは MySQL InnoDB の特殊 なロック機構 である。
SELECT ... FOR UPDATEなどのステートメントによってトリガーされる- インデックスキー（PK）間 の隙間 をロックし、他 のトランザクションがその隙間 に新 しい行 を挿入 するのを防止
Demo Table
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2) NOT NULL
);
INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);防止幻讀
| Time | Transaction A | Transaction B |
|---|---|---|
| T0 | BEGIN; | BEGIN; |
| T1 | SELECT * FROM employees WHERE id BETWEEN 1 AND 3 FOR UPDATE; | |
| T2 | INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00); | |
| T3 | COMMIT; — トランザクション A の Commit を待機 | |
| T4 | SELECT * FROM employees; — ID(2) のデータはテーブルに存在
しない | |
| T5 | COMMIT; | |
| T6 | SELECT * FROM employees; — ID(2) のデータがテーブルに表示
される |
データの誤削除/変更防止
| Time | Transaction A | Transaction B |
|---|---|---|
| T0 | BEGIN; | BEGIN; |
| T1 | SELECT count(1) FROM employees WHERE id >= 1; — 2 件
表示 | |
| T2 | INSERT INTO employees (id, name, salary) VALUES (4, 'Jack', 10000.00); | |
| T3 | COMMIT; — トランザクション A の Commit を待機 | |
| T4 | SELECT count(1) FROM employees WHERE id >= 1; — 依然
として 2 件
表示 | |
| T5 | COMMIT; | |
| T6 | SELECT count(1) FROM employees WHERE id >= 1; — 3 件
表示 |
Distributed Locks
分散 ロックは、分散 システムにおいて複数 のノードが共有 リソースへのアクセスを調整 するために使用 される。
Centralized Locking
中央 集権的 ロック。単一 のノードがすべてのロックを管理 。
問題 ：単一障害点 （SPOF）
Token-Based Locking
トークンベースのロック。
問題 ：
- Token Lost（トークン紛失 ）
- Token Expired（トークン期限切 れ）
Quorum-Based Locking (RedLock)
Redis Cluster を例 に（5 ノードと仮定 ）：
手順 ：
- Acquire Time：クライアントがまず現在
時刻
T1を記録 - Lock Acquisition：
- 各
Redis ノードで
SETNXコマンドを使 ってロック取得 を試 みる - 各
ノードは以下
を返
す：
OK：ロック取得 成功NULL：ロックが他 のクライアントに保持 されている
- 各
SETNXコマンドで有効 期限 （TTL）を設定 し、ロックが永久 に存在 しないようにしてデッドロックを回避
- 各
Redis ノードで
- Calculate Elapsed Time：経過
時間
を計算
T2 - T1 - Quorum Check：
N/2 + 1 = 5/2 + 1 = 3、成功 ノード数 > 3 ならロック成功 - Unlocking：
DELを使 ってロックを解放
利点 ：
- Quorum Requirement：1〜2 つの Redis ノードが故障 しても、過半数 のノード（例 ：3 ノード）が正常 に動作 していればロックを正 しく取得 ・解放 できる
- Fault Tolerance：単一障害点 を回避