【SQL】テーブル・行ロック完全ガイド｜SELECT FOR UPDATE・共有ロック・楽観的ロック・デッドロック回避・MVCC・RDBMS別構文まで解説

-- stocks テーブル（在庫）
-- id | product_id | quantity | version | updated_at
--  1 |       1001 |       50 |       1 | 2024-04-01 10:00:00
--  2 |       1002 |        0 |       3 | 2024-04-01 09:30:00
--  3 |       1003 |      200 |       1 | 2024-04-01 08:00:00

-- orders テーブル（注文）
-- id | customer_id | product_id | quantity | status    | created_at
--  1 |           1 |       1001 |        1 | pending   | 2024-04-01 10:05:00
--  2 |           2 |       1001 |        2 | pending   | 2024-04-01 10:05:01

-- セッション A
BEGIN;
SELECT quantity FROM stocks WHERE id = 1;  -- 50 を取得
-- （セッション B も 50 を取得して更新する）
UPDATE stocks SET quantity = 49 WHERE id = 1;
COMMIT;

-- セッション B（同時実行）
BEGIN;
SELECT quantity FROM stocks WHERE id = 1;  -- 50 を取得（A の更新前）
UPDATE stocks SET quantity = 48 WHERE id = 1;  -- A の -1 が消える
COMMIT;
-- 結果: quantity = 48 （正しくは 48 でよいが A の更新が反映されていない状態が起きうる）

-- セッション A: 在庫をロックして更新
BEGIN;
SELECT quantity FROM stocks WHERE id = 1 FOR UPDATE;
-- quantity = 50 を取得し、同時に排他ロックを取得

-- この時点でセッション B が同じ行を FOR UPDATE しようとすると「待ち」になる

UPDATE stocks SET quantity = quantity - 1 WHERE id = 1;
-- quantity: 50 → 49
COMMIT;  -- ロック解放

-- セッション B（セッション A と同時）
BEGIN;
SELECT quantity FROM stocks WHERE id = 1 FOR UPDATE;
-- セッション A のコミット後に続行: quantity = 49 を取得

UPDATE stocks SET quantity = quantity - 1 WHERE id = 1;
-- quantity: 49 → 48
COMMIT;

-- MySQL: FOR UPDATE
BEGIN;
SELECT * FROM stocks WHERE id = 1 FOR UPDATE;
UPDATE stocks SET quantity = quantity - 1 WHERE id = 1;
COMMIT;

-- MySQL 8.0+: NOWAIT（待たずにエラー）/ SKIP LOCKED（スキップ）
SELECT * FROM stocks WHERE id = 1 FOR UPDATE NOWAIT;
-- → ロック取得できない場合即エラー（ER_LOCK_NOWAIT）

SELECT * FROM stocks WHERE quantity > 0 FOR UPDATE SKIP LOCKED;
-- → ロック中の行をスキップして取得できる行だけ返す（キューの処理に便利）

-- PostgreSQL
BEGIN;
SELECT * FROM stocks WHERE id = 1 FOR UPDATE;
UPDATE stocks SET quantity = quantity - 1 WHERE id = 1;
COMMIT;

-- PostgreSQL: NOWAIT / SKIP LOCKED
SELECT * FROM stocks WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM stocks WHERE quantity > 0 FOR UPDATE SKIP LOCKED;

-- SQL Server: WITH (UPDLOCK, ROWLOCK)
BEGIN TRANSACTION;
SELECT * FROM stocks WITH (UPDLOCK, ROWLOCK) WHERE id = 1;
UPDATE stocks SET quantity = quantity - 1 WHERE id = 1;
COMMIT TRANSACTION;

-- 在庫を「読み取り専用」でロック（他が更新できないようにする）
BEGIN;
SELECT quantity FROM stocks WHERE id = 1 LOCK IN SHARE MODE;
-- または MySQL 8.0+ / PostgreSQL:
SELECT quantity FROM stocks WHERE id = 1 FOR SHARE;

-- この間、他のトランザクションは:
-- ・ SELECT（ロックなし）→ OK（MVCC スナップショットを読む）
-- ・ SELECT ... FOR SHARE   → OK（S ロックは共存できる）
-- ・ SELECT ... FOR UPDATE  → 待ちになる
-- ・ UPDATE / DELETE        → 待ちになる

-- S ロックを保持したまま、参照した結果に基づいて処理を確認
-- この後 UPDATE する場合は X ロックへのエスカレーションが必要
UPDATE stocks SET quantity = quantity - 1 WHERE id = 1;
-- ※ S→X へのロックアップグレードが起きる（デッドロックに注意）
COMMIT;

-- ===== 主な用途：外部キー整合性の確認 =====
-- product を参照する stock を安全に INSERT する前に
-- 親テーブルの product が存在することを S ロックで確認
BEGIN;
SELECT id FROM products WHERE id = 1001 FOR SHARE;
-- product_id=1001 が削除されないように S ロックを取得
INSERT INTO stocks (product_id, quantity) VALUES (1001, 100);
COMMIT;

-- テーブル全体を書き込みロック（WRITE）
LOCK TABLES stocks WRITE;
-- この間、他のセッションは stocks への SELECT・UPDATE・DELETE が待ちになる

UPDATE stocks SET quantity = 0 WHERE product_id = 1002;

UNLOCK TABLES;  -- 必ずロックを解放する

-- READ ロック（読み取り専用・他セッションも SELECT は可）
LOCK TABLES stocks READ;
SELECT * FROM stocks;
UNLOCK TABLES;

-- 複数テーブルのロック（必要なすべてを一度に指定）
LOCK TABLES stocks WRITE, orders READ;
-- 注意: ロックしていないテーブルはアクセス不可になる
UNLOCK TABLES;

-- ※ LOCK TABLES は InnoDB では行ロック（FOR UPDATE）が推奨
-- テーブルロックは MyISAM や一括バッチ処理時に限定的に使う

-- PostgreSQL: LOCK TABLE
BEGIN;
LOCK TABLE stocks IN EXCLUSIVE MODE;
-- EXCLUSIVE MODE: SELECT は可・UPDATE/INSERT/DELETE は待ち
-- ACCESS EXCLUSIVE MODE（最強）: SELECT も含む全アクセスが待ち
LOCK TABLE stocks IN ACCESS EXCLUSIVE MODE;

UPDATE stocks SET quantity = 0 WHERE product_id = 1002;
COMMIT;  -- トランザクション終了でロック自動解放

-- PostgreSQL のロックモード一覧（強度順）
-- ACCESS SHARE        → 通常の SELECT が使用
-- ROW SHARE           → SELECT FOR UPDATE が使用
-- ROW EXCLUSIVE       → UPDATE/DELETE/INSERT が使用
-- SHARE UPDATE EXCLUSIVE
-- SHARE               → CREATE INDEX（非並行）が使用
-- SHARE ROW EXCLUSIVE
-- EXCLUSIVE
-- ACCESS EXCLUSIVE    → DROP TABLE/TRUNCATE など

-- SQL Server: TABLOCKX ヒント
BEGIN TRANSACTION;
SELECT * FROM stocks WITH (TABLOCKX);
-- または
UPDATE stocks WITH (TABLOCKX) SET quantity = 0 WHERE product_id = 1002;
COMMIT TRANSACTION;

-- stocks テーブルに version 列を追加（整合性チェック用）
ALTER TABLE stocks ADD COLUMN version INT NOT NULL DEFAULT 1;

-- ① 読み取り時に version を取得
SELECT id, quantity, version FROM stocks WHERE id = 1;
-- → quantity = 50, version = 1

-- ② 更新時に version を WHERE 条件に含める
UPDATE stocks
SET quantity = quantity - 1,
    version  = version + 1
WHERE id = 1
  AND version = 1;  -- ← 読んだ時点の version と一致するか確認

-- UPDATE の影響行数を確認
-- 影響行数 = 1 → 更新成功（競合なし）
-- 影響行数 = 0 → 競合発生（別トランザクションが先に更新した）
--                → アプリ側でリトライまたはエラー処理が必要

-- ③ タイムスタンプを version 代わりに使う方法
UPDATE stocks
SET quantity   = quantity - 1,
    updated_at = NOW()
WHERE id = 1
  AND updated_at = '2024-04-01 10:00:00';  -- 読んだ時点の updated_at

-- 在庫引き当ての完全な例（悲観的ロック）
BEGIN;

-- ① 在庫行を排他ロックで取得
SELECT id, quantity
FROM stocks
WHERE product_id = 1001
FOR UPDATE;
-- 他のトランザクションはこの行を FOR UPDATE できない

-- ② 在庫チェック（アプリ側で判定）
-- quantity >= 注文数量 であれば以下に進む

-- ③ 在庫を減らす
UPDATE stocks
SET quantity = quantity - 1
WHERE product_id = 1001;

-- ④ 注文レコードを作成
INSERT INTO orders (customer_id, product_id, quantity, status)
VALUES (1, 1001, 1, 'confirmed');

COMMIT;
-- コミット時にロック解放 → 待っていた別トランザクションが続行

-- ===== MySQL: innodb_lock_wait_timeout（デフォルト 50 秒）=====
-- セッション単位で変更（秒）
SET SESSION innodb_lock_wait_timeout = 5;

-- 確認
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- タイムアウト時のエラー
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- ===== MySQL 8.0+: NOWAIT / SKIP LOCKED =====
-- NOWAIT: ロック取得できなければ即エラー
SELECT * FROM stocks WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED: ロック中の行をスキップ（キュー処理向け）
SELECT * FROM stocks
WHERE quantity > 0
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

-- ===== PostgreSQL: lock_timeout =====
SET lock_timeout = '5s';
-- タイムアウト時: ERROR: canceling statement due to lock timeout

-- NOWAIT（PostgreSQL）
SELECT * FROM stocks WHERE id = 1 FOR UPDATE NOWAIT;

-- ===== SQL Server: ロックタイムアウト =====
SET LOCK_TIMEOUT 5000;  -- ミリ秒単位（5秒）
-- タイムアウト時: エラー 1222

-- セッション A と B が逆順でロックを取得するとデッドロックになる

-- セッション A
BEGIN;
SELECT * FROM stocks WHERE id = 1 FOR UPDATE;   -- id=1 を X ロック取得
-- （この後 id=2 を取ろうとする）
SELECT * FROM stocks WHERE id = 2 FOR UPDATE;   -- 待ち ← B が id=2 を持っている

-- セッション B（同時実行）
BEGIN;
SELECT * FROM stocks WHERE id = 2 FOR UPDATE;   -- id=2 を X ロック取得
-- （この後 id=1 を取ろうとする）
SELECT * FROM stocks WHERE id = 1 FOR UPDATE;   -- 待ち ← A が id=1 を持っている

-- → 互いに待ち合い → デッドロック
-- RDBMS がどちらかをロールバック（犠牲トランザクション）
-- MySQL: ERROR 1213 (40001): Deadlock found when trying to get lock
-- PostgreSQL: ERROR: deadlock detected

-- ===== 対策①: ロック取得の順序を統一する（最も有効）=====
-- 悪い例: セッション A は id 昇順、セッション B は id 降順でロック
-- 良い例: 全セッションで id 昇順（小さい id から）でロック

BEGIN;
SELECT * FROM stocks WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- ORDER BY id で昇順にロックを取得 → 全セッションで同じ順序になる
COMMIT;

-- ===== 対策②: ロック保持時間を最小化する =====
-- ロックを取得してから COMMIT するまでの時間を短くする
-- NG: ロック中に外部 API を呼ぶ（応答待ちの間ロックを保持）
BEGIN;
SELECT * FROM stocks WHERE id = 1 FOR UPDATE;
-- ← ここで外部 API を呼ぶのは NG
COMMIT;

-- ===== 対策③: NOWAIT でデッドロックを早期検知 =====
BEGIN;
SELECT * FROM stocks WHERE id = 1 FOR UPDATE NOWAIT;
-- 他がロック中なら即エラー → アプリ側でリトライ
COMMIT;

-- ===== 対策④: 楽観的ロックへの切り替え =====
-- 競合頻度が低い場合は SELECT FOR UPDATE をやめ version 列で楽観的ロックにする
-- → デッドロック自体が起きない

-- ===== MySQL: 最後のデッドロック情報を確認 =====
SHOW ENGINE INNODB STATUS;
-- LATEST DETECTED DEADLOCK セクションに詳細が出る

-- セッション A: トランザクション開始
BEGIN;
-- この時点でのスナップショットを保持

-- セッション B: 在庫を更新してコミット
BEGIN;
UPDATE stocks SET quantity = 40 WHERE id = 1;  -- 50 → 40
COMMIT;

-- セッション A: スナップショット読み取り（MVCC）
SELECT quantity FROM stocks WHERE id = 1;
-- → 50 が返る（B のコミット後でも A のスナップショット時点 = 50）

-- セッション A: FOR UPDATE は最新データを読む
SELECT quantity FROM stocks WHERE id = 1 FOR UPDATE;
-- → 40 が返る（最新のコミット済みデータ）

-- ※ これが MVCC と FOR UPDATE の重要な違い
-- MVCC の SELECT は「絵葉書（スナップショット）」を読む
-- FOR UPDATE は「今の現実（最新コミット）」を読んでロックを取る

ROLLBACK;

-- ===== 分離レベルとの関係 =====
-- READ COMMITTED: コミット済みデータを常に最新で読む
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ（MySQL のデフォルト）: 同一トランザクション内では同じ値を返す
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- SERIALIZABLE: 全ての SELECT が自動的に FOR SHARE 相当のロックを取得
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- ===== 現在のロック待ち状況 =====
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- MySQL 8.0 以降: performance_schema を使用
SELECT
    OBJECT_SCHEMA, OBJECT_NAME,
    LOCK_TYPE, LOCK_MODE, LOCK_STATUS,
    PROCESSLIST_ID, PROCESSLIST_INFO
FROM performance_schema.data_locks dl
JOIN performance_schema.threads t ON dl.THREAD_ID = t.THREAD_ID;

-- ロック中のプロセスを確認して強制終了
SHOW PROCESSLIST;
KILL 123;  -- スレッド ID を指定して強制切断

-- 最後のデッドロック情報
SHOW ENGINE INNODB STATUS\G

-- ロック待ちセッションの確認
SELECT
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    a.query,
    age(now(), a.query_start) AS query_duration
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock';

-- 詳細なロック情報（どの行・テーブルがロック中か）
SELECT
    l.pid,
    l.relation::regclass AS table_name,
    l.mode,
    l.granted,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;  -- 待ち状態のロックのみ

-- ブロックしているセッションを強制終了
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 456;

-- ロック待ちの確認
SELECT
    r.session_id AS waiting_session,
    r.blocking_session_id AS blocking_session,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_seconds,
    r.command,
    t.text AS query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0;

-- ロック中の全セッション
SELECT *
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE r.blocking_session_id > 0;

-- セッションを強制切断
KILL 57;  -- session_id を指定

-- 在庫不足時は注文を作らない完全なパターン
BEGIN;

SELECT quantity
FROM stocks
WHERE product_id = 1001
FOR UPDATE;
-- quantity を取得しつつ排他ロック

-- アプリ: quantity >= order_quantity なら続行

UPDATE stocks
SET quantity = quantity - 2  -- 2個注文
WHERE product_id = 1001
  AND quantity >= 2;  -- 念のためDBでも在庫チェック

-- UPDATE の影響行数 = 0 なら在庫不足 → ROLLBACK

INSERT INTO orders (customer_id, product_id, quantity, status)
VALUES (1, 1001, 2, 'confirmed');

COMMIT;

-- 採番テーブル
-- CREATE TABLE sequences (name VARCHAR(50) PRIMARY KEY, next_val INT);
-- INSERT INTO sequences VALUES ('order_no', 1);

BEGIN;
SELECT next_val FROM sequences WHERE name = 'order_no' FOR UPDATE;
-- next_val = 1001 を取得・ロック

UPDATE sequences SET next_val = next_val + 1 WHERE name = 'order_no';

-- 取得した 1001 を注文番号として使用
INSERT INTO orders (order_no, customer_id, amount)
VALUES (1001, 1, 12000);

COMMIT;
-- 次のトランザクションは next_val = 1002 から始まる

-- job_queue テーブル（status: pending/processing/done）
-- MySQL 8.0+ / PostgreSQL

-- ワーカーが「処理中でない」ジョブを 1 件取得
BEGIN;

UPDATE job_queue
SET status = 'processing',
    started_at = NOW()
WHERE id = (
    SELECT id FROM job_queue
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED  -- ロック中のジョブをスキップ
)
RETURNING id, payload;  -- PostgreSQL の場合

COMMIT;

-- 複数ワーカーが並行して実行しても、各ワーカーが別のジョブを取得する
-- SKIP LOCKED がなければワーカーが同じジョブを取り合いデッドロックになりやすい