PostgreSQLの行ロックを完全理解！FOR UPDATEからデッドロック対策まで

データベース・SQL

「在庫数を同時に更新したら、数が合わなくなった…」 「複数人で同じデータを編集したら、誰かの変更が消えてしまった」 「銀行の残高更新で、二重引き落としが発生してしまった」

こんなトラブル、経験ありませんか？

複数のユーザーが同時にデータベースを操作する時、 行ロックという仕組みがないと、データの整合性が保てなくなります。

たとえば、ECサイトで最後の1個の商品を、2人が同時に購入しようとしたら？ 行ロックがあれば、「先に処理を始めた人だけが購入できる」ように制御できるんです。

この記事では、PostgreSQLの行ロックの仕組みから実践的な使い方、 そしてデッドロック（お互いがロックを待ち続ける状態）の回避方法まで、 すべてお伝えします。

トランザクションの安全性を確保して、信頼できるシステムを作りましょう！

スポンサーリンク
  1. 1. PostgreSQLのロックの基本概念
    1. 🔐 ロックとは何か？
    2. 🔐 PostgreSQLのロックレベル
    3. 🔐 MVCC（多版型同時実行制御）の仕組み
  2. 2. SELECT FOR UPDATE：排他ロックの使い方
    1. 📌 基本構文と動作
    2. 📌 実例1：在庫管理での使用
    3. 📌 実例2：銀行口座の送金処理
  3. 3. SELECT FOR SHARE：共有ロックの使い方
    1. 📌 FOR SHAREとFOR UPDATEの違い
    2. 📌 実例：参照整合性の確保
  4. 4. ロックのオプションと待機制御
    1. ⏱️ NOWAIT：即座に失敗させる
    2. ⏱️ SKIP LOCKED：ロックされた行をスキップ
  5. 5. デッドロックの理解と対策
    1. 💀 デッドロックとは？
    2. 💀 デッドロック回避の基本戦略
  6. 6. ロックの監視と診断
    1. 🔍 現在のロック状況を確認
    2. 🔍 ブロッキングチェーンの確認
    3. 🔍 長時間実行中のトランザクション
  7. 7. アプリケーション実装のベストプラクティス
    1. 💡 楽観的ロック vs 悲観的ロック
    2. 💡 コネクションプールでの注意点
  8. 8. パフォーマンスとロックの最適化
    1. ⚡ インデックスとロックの関係
    2. ⚡ バッチ処理での工夫
    3. ⚡ アドバイザリーロック
  9. 9. トラブルシューティング
    1. ❌ エラー：deadlock detected
    2. ❌ エラー：lock timeout
    3. ❌ パフォーマンス低下
  10. 10. 実践的なユースケース集
    1. 🎯 ケース1：在庫予約システム
    2. 🎯 ケース2：連番採番システム
    3. 🎯 ケース3：ポイント残高管理
  11. まとめ：行ロックを使いこなして、安全で高速なシステムを！
    1. 🚀 次のステップ

1. PostgreSQLのロックの基本概念

🔐 ロックとは何か？

ロックは、データベースの「信号機」のようなものです。

ロックの役割：

  • 赤信号：他の人は待ってください（排他ロック）
  • 黄信号：読むのはOK、書き込みは待って（共有ロック）
  • 青信号：みんな自由に通れます（ロックなし）

🔐 PostgreSQLのロックレベル

PostgreSQLには、大きく分けて3つのロックレベルがあります。

1. テーブルレベルロック

テーブル全体をロック
例：ALTER TABLE、TRUNCATE実行時
影響範囲：大きい

2. 行レベルロック

特定の行だけをロック
例：UPDATE、DELETE、SELECT FOR UPDATE
影響範囲：小さい（推奨）

3. ページレベルロック

データページ単位のロック
PostgreSQLでは内部的に使用
ユーザーが直接制御することは少ない

🔐 MVCC（多版型同時実行制御）の仕組み

PostgreSQLの特徴はMVCCという仕組みです。

通常のSELECT：ロックしない！
UPDATE/DELETE：古いバージョンを残しながら更新
結果：読み取りと書き込みがお互いをブロックしない

これにより、高い同時実行性を実現しています。

2. SELECT FOR UPDATE：排他ロックの使い方

📌 基本構文と動作

SELECT * FROM テーブル名
WHERE 条件
FOR UPDATE;

FOR UPDATEの効果：

  • 選択した行に排他ロックをかける
  • 他のトランザクションは、この行を更新できない
  • トランザクション終了まで ロックは維持される

📌 実例1：在庫管理での使用

準備：在庫テーブルの作成

-- 商品在庫テーブル
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    stock_quantity INT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- サンプルデータ
INSERT INTO inventory VALUES 
    (1, 'ノートPC', 5, CURRENT_TIMESTAMP),
    (2, 'マウス', 100, CURRENT_TIMESTAMP),
    (3, 'キーボード', 3, CURRENT_TIMESTAMP);

安全な在庫更新処理：

-- トランザクション1：商品ID 1の在庫を減らす
BEGIN;

-- 在庫を確認して、同時にロック
SELECT * FROM inventory 
WHERE product_id = 1 
FOR UPDATE;

-- 結果：stock_quantity = 5

-- 在庫があることを確認してから更新
UPDATE inventory 
SET stock_quantity = stock_quantity - 1,
    last_updated = CURRENT_TIMESTAMP
WHERE product_id = 1 
AND stock_quantity > 0;

COMMIT;

同時実行時の動作：

-- トランザクション2：同じ商品を同時に購入しようとする
BEGIN;

-- この SELECT は トランザクション1の COMMIT まで待機！
SELECT * FROM inventory 
WHERE product_id = 1 
FOR UPDATE;  -- ここでブロックされる

-- トランザクション1の COMMIT 後に実行される
-- この時点で stock_quantity = 4 になっている

📌 実例2：銀行口座の送金処理

-- 口座テーブル
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(100),
    balance DECIMAL(12,2),
    version INT DEFAULT 0
);

-- サンプルデータ
INSERT INTO accounts VALUES 
    (1, '田中太郎', 100000.00, 0),
    (2, '鈴木花子', 50000.00, 0);

-- 送金処理（A→Bへ10,000円）
BEGIN;

-- 送金元と送金先を同時にロック（デッドロック防止のため、ID順）
SELECT * FROM accounts 
WHERE account_id IN (1, 2)
ORDER BY account_id
FOR UPDATE;

-- 残高確認
SELECT balance FROM accounts WHERE account_id = 1;
-- 結果：100000.00

-- 送金実行
UPDATE accounts 
SET balance = balance - 10000.00,
    version = version + 1
WHERE account_id = 1
AND balance >= 10000.00;  -- 残高チェック

UPDATE accounts 
SET balance = balance + 10000.00,
    version = version + 1
WHERE account_id = 2;

COMMIT;

3. SELECT FOR SHARE：共有ロックの使い方

📌 FOR SHAREとFOR UPDATEの違い

SELECT * FROM テーブル名
WHERE 条件
FOR SHARE;

比較表：

機能FOR UPDATEFOR SHARE
他のSELECT可能可能
他のFOR SHARE待機可能
他のFOR UPDATE待機待機
UPDATE/DELETE待機待機

📌 実例：参照整合性の確保

-- 注文処理での使用例
BEGIN;

-- 顧客が存在することを確認（削除されないように）
SELECT * FROM customers 
WHERE customer_id = 123 
FOR SHARE;

-- 顧客が存在する間は削除できない
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (123, CURRENT_DATE, 5000.00);

COMMIT;

4. ロックのオプションと待機制御

⏱️ NOWAIT：即座に失敗させる

-- ロックが取得できない場合、即座にエラー
SELECT * FROM inventory 
WHERE product_id = 1 
FOR UPDATE NOWAIT;

-- エラー例：
-- ERROR: could not obtain lock on row in relation "inventory"

使用例：リトライ処理

DO $$
DECLARE
    retry_count INT := 0;
    max_retries INT := 3;
    success BOOLEAN := FALSE;
BEGIN
    WHILE retry_count < max_retries AND NOT success LOOP
        BEGIN
            -- NOWAITで即座に結果を得る
            PERFORM * FROM inventory 
            WHERE product_id = 1 
            FOR UPDATE NOWAIT;
            
            -- ロック取得成功
            UPDATE inventory 
            SET stock_quantity = stock_quantity - 1
            WHERE product_id = 1;
            
            success := TRUE;
        EXCEPTION
            WHEN lock_not_available THEN
                retry_count := retry_count + 1;
                PERFORM pg_sleep(0.1 * retry_count);  -- 待機時間を増やす
        END;
    END LOOP;
    
    IF NOT success THEN
        RAISE EXCEPTION 'ロック取得に失敗しました';
    END IF;
END $$;

⏱️ SKIP LOCKED：ロックされた行をスキップ

-- ロックされていない行だけを取得
SELECT * FROM tasks 
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

使用例：ジョブキューの実装

-- タスクキューテーブル
CREATE TABLE job_queue (
    job_id SERIAL PRIMARY KEY,
    job_type VARCHAR(50),
    payload JSONB,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMP,
    completed_at TIMESTAMP
);

-- ワーカーがジョブを取得
CREATE OR REPLACE FUNCTION get_next_job()
RETURNS job_queue AS $$
DECLARE
    next_job job_queue;
BEGIN
    -- 他のワーカーが処理中のジョブはスキップ
    SELECT * INTO next_job
    FROM job_queue
    WHERE status = 'pending'
    FOR UPDATE SKIP LOCKED
    LIMIT 1;
    
    IF FOUND THEN
        UPDATE job_queue
        SET status = 'processing',
            started_at = CURRENT_TIMESTAMP
        WHERE job_id = next_job.job_id;
    END IF;
    
    RETURN next_job;
END;
$$ LANGUAGE plpgsql;

5. デッドロックの理解と対策

💀 デッドロックとは？

デッドロックは、2つ以上のトランザクションが お互いのロックを待ち続ける状態です。

典型的なデッドロックパターン：

-- トランザクション1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;  -- ロックA
-- （ここで少し時間経過）
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;  -- ロックB待ち

-- トランザクション2（同時実行）
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;   -- ロックB
-- （ここで少し時間経過）
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;   -- ロックA待ち

-- 結果：デッドロック発生！
-- ERROR: deadlock detected

💀 デッドロック回避の基本戦略

1. ロック順序の統一

-- 常にID順でロックを取得
CREATE OR REPLACE FUNCTION transfer_money(
    from_account INT,
    to_account INT,
    amount DECIMAL
) RETURNS VOID AS $$
DECLARE
    min_id INT;
    max_id INT;
BEGIN
    -- IDの小さい順にロック
    min_id := LEAST(from_account, to_account);
    max_id := GREATEST(from_account, to_account);
    
    -- 順序を守ってロック
    PERFORM * FROM accounts 
    WHERE account_id = min_id 
    FOR UPDATE;
    
    PERFORM * FROM accounts 
    WHERE account_id = max_id 
    FOR UPDATE;
    
    -- 送金処理
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE account_id = from_account;
    
    UPDATE accounts 
    SET balance = balance + amount 
    WHERE account_id = to_account;
END;
$$ LANGUAGE plpgsql;

2. ロック範囲の最小化

-- 悪い例：大量の行をロック
BEGIN;
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
FOR UPDATE;  -- 数千行をロック

-- 良い例：必要最小限をロック
BEGIN;
SELECT * FROM orders 
WHERE order_id = 12345 
FOR UPDATE;  -- 1行だけロック

3. ロック時間の短縮

-- 悪い例：ロック中に重い処理
BEGIN;
SELECT * FROM inventory FOR UPDATE;
-- 外部API呼び出し（時間がかかる）
-- 複雑な計算処理
UPDATE inventory SET ...;
COMMIT;

-- 良い例：事前に計算してからロック
-- 先に重い処理を済ませる
DECLARE
    new_price DECIMAL;
BEGIN
    -- ロック前に計算
    new_price := calculate_complex_price();
    
    -- 最短時間でロック→更新→解放
    UPDATE inventory 
    SET price = new_price 
    WHERE product_id = 1;
END;

6. ロックの監視と診断

🔍 現在のロック状況を確認

-- アクティブなロックを表示
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    wait_event_type,
    wait_event,
    state,
    query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- より詳細なロック情報
SELECT 
    l.locktype,
    l.database,
    l.relation::regclass AS table_name,
    l.page,
    l.tuple,
    l.virtualxid,
    l.transactionid,
    l.classid,
    l.objid,
    l.objsubid,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted,
    l.fastpath
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY l.pid;

🔍 ブロッキングチェーンの確認

-- 誰が誰をブロックしているか
WITH blocking AS (
    SELECT 
        blocked_locks.pid AS blocked_pid,
        blocked_activity.usename AS blocked_user,
        blocking_locks.pid AS blocking_pid,
        blocking_activity.usename AS blocking_user,
        blocked_activity.query AS blocked_query,
        blocking_activity.query AS blocking_query
    FROM pg_locks blocked_locks
    JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_locks blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.granted
)
SELECT * FROM blocking;

🔍 長時間実行中のトランザクション

-- 5分以上実行中のトランザクション
SELECT 
    pid,
    usename,
    application_name,
    state,
    NOW() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE state != 'idle'
AND xact_start < NOW() - INTERVAL '5 minutes'
ORDER BY duration DESC;

-- 問題のあるセッションを強制終了（慎重に！）
-- SELECT pg_terminate_backend(pid);

7. アプリケーション実装のベストプラクティス

💡 楽観的ロック vs 悲観的ロック

楽観的ロック（バージョン番号を使用）：

-- テーブル設計
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),
    version INT DEFAULT 0
);

-- 更新処理
CREATE OR REPLACE FUNCTION update_product_optimistic(
    p_id INT,
    p_new_price DECIMAL,
    p_expected_version INT
) RETURNS BOOLEAN AS $$
DECLARE
    rows_updated INT;
BEGIN
    UPDATE products
    SET price = p_new_price,
        version = version + 1
    WHERE product_id = p_id
    AND version = p_expected_version;
    
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    
    IF rows_updated = 0 THEN
        RAISE EXCEPTION 'バージョン競合: データが他で更新されています';
    END IF;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

悲観的ロック（FOR UPDATE）：

-- 確実に更新したい場合
CREATE OR REPLACE FUNCTION update_product_pessimistic(
    p_id INT,
    p_new_price DECIMAL
) RETURNS VOID AS $$
BEGIN
    -- まずロック
    PERFORM * FROM products
    WHERE product_id = p_id
    FOR UPDATE;
    
    -- その後更新
    UPDATE products
    SET price = p_new_price
    WHERE product_id = p_id;
END;
$$ LANGUAGE plpgsql;

💡 コネクションプールでの注意点

# Python + psycopg2の例
import psycopg2
from psycopg2 import pool
from contextlib import contextmanager

# コネクションプールの作成
connection_pool = psycopg2.pool.SimpleConnectionPool(
    1, 20,
    host="localhost",
    database="mydb",
    user="user",
    password="password"
)

@contextmanager
def get_db_cursor():
    conn = connection_pool.getconn()
    try:
        with conn.cursor() as cursor:
            yield cursor
            conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        connection_pool.putconn(conn)

# 使用例
def update_inventory_safely(product_id, quantity):
    with get_db_cursor() as cursor:
        # FOR UPDATE で確実にロック
        cursor.execute("""
            SELECT stock_quantity 
            FROM inventory 
            WHERE product_id = %s 
            FOR UPDATE
        """, (product_id,))
        
        current_stock = cursor.fetchone()[0]
        
        if current_stock >= quantity:
            cursor.execute("""
                UPDATE inventory 
                SET stock_quantity = stock_quantity - %s
                WHERE product_id = %s
            """, (quantity, product_id))
            return True
        else:
            return False

8. パフォーマンスとロックの最適化

⚡ インデックスとロックの関係

-- インデックスがない場合：テーブル全体をスキャン→多くの行をロック
-- インデックスがある場合：必要な行だけロック

-- インデックス作成
CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, order_date);

-- この更新は効率的にロック
UPDATE orders 
SET status = 'shipped'
WHERE customer_id = 123 
AND order_date = '2024-06-01';

⚡ バッチ処理での工夫

-- 悪い例：大量の行を一度にロック
UPDATE large_table 
SET processed = true 
WHERE processed = false;  -- 100万行をロック！

-- 良い例：小さなバッチで処理
DO $$
DECLARE
    batch_size INT := 1000;
    rows_updated INT;
BEGIN
    LOOP
        WITH batch AS (
            SELECT id 
            FROM large_table 
            WHERE processed = false 
            LIMIT batch_size
            FOR UPDATE SKIP LOCKED  -- 他の処理とぶつからない
        )
        UPDATE large_table t
        SET processed = true
        FROM batch b
        WHERE t.id = b.id;
        
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        
        EXIT WHEN rows_updated = 0;
        
        COMMIT;  -- バッチごとにコミット
        PERFORM pg_sleep(0.1);  -- 他の処理に配慮
    END LOOP;
END $$;

⚡ アドバイザリーロック

-- アプリケーションレベルのロック
-- 特定の処理を排他制御したい場合

-- ロックの取得
SELECT pg_advisory_lock(12345);  -- 12345は任意のID

-- 処理実行
-- ...

-- ロックの解放
SELECT pg_advisory_unlock(12345);

-- トランザクションスコープのアドバイザリーロック
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- トランザクション終了時に自動解放
-- ...
COMMIT;

9. トラブルシューティング

❌ エラー：deadlock detected

-- デッドロック情報の詳細をログに出力
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

-- デッドロックが発生しやすい処理の特定
SELECT 
    query,
    COUNT(*) as deadlock_count
FROM pg_stat_database_conflicts
GROUP BY query
ORDER BY deadlock_count DESC;

❌ エラー：lock timeout

-- タイムアウト設定の確認
SHOW lock_timeout;
SHOW statement_timeout;

-- セッション単位で設定
SET lock_timeout = '10s';
SET statement_timeout = '30s';

-- 永続的な設定
ALTER DATABASE mydb SET lock_timeout = '10s';

❌ パフォーマンス低下

-- ロック待機時間の統計
SELECT 
    wait_event_type,
    wait_event,
    COUNT(*) as count,
    SUM(EXTRACT(EPOCH FROM (NOW() - state_change))) as total_wait_seconds
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY total_wait_seconds DESC;

-- ロック競合が多いテーブルの特定
SELECT 
    schemaname,
    tablename,
    n_tup_upd + n_tup_del as write_activity,
    n_live_tup as row_count,
    n_dead_tup as dead_rows
FROM pg_stat_user_tables
ORDER BY write_activity DESC
LIMIT 10;

10. 実践的なユースケース集

🎯 ケース1：在庫予約システム

-- 在庫予約テーブル
CREATE TABLE inventory_reservations (
    reservation_id SERIAL PRIMARY KEY,
    product_id INT,
    quantity INT,
    user_id INT,
    reserved_until TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active'
);

-- 在庫予約処理
CREATE OR REPLACE FUNCTION reserve_inventory(
    p_product_id INT,
    p_quantity INT,
    p_user_id INT,
    p_duration INTERVAL DEFAULT '15 minutes'
) RETURNS INT AS $$
DECLARE
    v_available_stock INT;
    v_reserved_stock INT;
    v_reservation_id INT;
BEGIN
    -- 在庫と予約を同時にロック
    SELECT stock_quantity INTO v_available_stock
    FROM inventory
    WHERE product_id = p_product_id
    FOR UPDATE;
    
    -- アクティブな予約の合計
    SELECT COALESCE(SUM(quantity), 0) INTO v_reserved_stock
    FROM inventory_reservations
    WHERE product_id = p_product_id
    AND status = 'active'
    AND reserved_until > CURRENT_TIMESTAMP
    FOR UPDATE;
    
    -- 利用可能在庫の確認
    IF v_available_stock - v_reserved_stock >= p_quantity THEN
        INSERT INTO inventory_reservations 
            (product_id, quantity, user_id, reserved_until)
        VALUES 
            (p_product_id, p_quantity, p_user_id, CURRENT_TIMESTAMP + p_duration)
        RETURNING reservation_id INTO v_reservation_id;
        
        RETURN v_reservation_id;
    ELSE
        RAISE EXCEPTION '在庫が不足しています';
    END IF;
END;
$$ LANGUAGE plpgsql;

🎯 ケース2：連番採番システム

-- 連番管理テーブル
CREATE TABLE sequence_numbers (
    sequence_name VARCHAR(50) PRIMARY KEY,
    current_value BIGINT DEFAULT 0,
    prefix VARCHAR(10),
    padding INT DEFAULT 6
);

-- 連番取得関数（ギャップなし保証）
CREATE OR REPLACE FUNCTION get_next_number(
    p_sequence_name VARCHAR
) RETURNS VARCHAR AS $$
DECLARE
    v_current BIGINT;
    v_prefix VARCHAR;
    v_padding INT;
    v_formatted VARCHAR;
BEGIN
    -- 排他ロックで確実に連番を取得
    SELECT current_value, prefix, padding
    INTO v_current, v_prefix, v_padding
    FROM sequence_numbers
    WHERE sequence_name = p_sequence_name
    FOR UPDATE;
    
    -- 次の番号に更新
    UPDATE sequence_numbers
    SET current_value = current_value + 1
    WHERE sequence_name = p_sequence_name;
    
    -- フォーマット（例：INV000001）
    v_formatted := v_prefix || LPAD((v_current + 1)::TEXT, v_padding, '0');
    
    RETURN v_formatted;
END;
$$ LANGUAGE plpgsql;

🎯 ケース3：ポイント残高管理

-- ポイント履歴テーブル
CREATE TABLE point_transactions (
    transaction_id SERIAL PRIMARY KEY,
    user_id INT,
    points INT,  -- 正：加算、負：減算
    balance_after INT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ポイント使用処理
CREATE OR REPLACE FUNCTION use_points(
    p_user_id INT,
    p_points INT,
    p_description TEXT
) RETURNS BOOLEAN AS $$
DECLARE
    v_current_balance INT;
    v_new_balance INT;
BEGIN
    -- 最新の残高を排他ロックで取得
    SELECT balance_after INTO v_current_balance
    FROM point_transactions
    WHERE user_id = p_user_id
    ORDER BY transaction_id DESC
    LIMIT 1
    FOR UPDATE;
    
    -- 残高が存在しない場合は0
    v_current_balance := COALESCE(v_current_balance, 0);
    
    -- 残高チェック
    IF v_current_balance < p_points THEN
        RETURN FALSE;  -- 残高不足
    END IF;
    
    -- 新しい残高を計算
    v_new_balance := v_current_balance - p_points;
    
    -- トランザクション記録
    INSERT INTO point_transactions 
        (user_id, points, balance_after, description)
    VALUES 
        (p_user_id, -p_points, v_new_balance, p_description);
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

まとめ：行ロックを使いこなして、安全で高速なシステムを！

PostgreSQLの行ロック、思っていたより奥が深いですよね。 でも、基本をしっかり理解すれば、トラブルなく使いこなせます。

重要ポイントのおさらい：

✅ FOR UPDATEは最強の排他ロック

  • 確実に更新したい時に使用
  • 在庫管理、残高更新などで必須

✅ デッドロックは予防が大切

  • ロック順序の統一
  • ロック範囲の最小化
  • ロック時間の短縮

✅ SKIP LOCKEDとNOWAITを活用

  • キュー処理にSKIP LOCKED
  • リトライ処理にNOWAIT

✅ 監視とメンテナンスを忘れずに

  • pg_stat_activityで監視
  • 長時間トランザクションに注意
  • 定期的な統計情報更新

使い分けの指針：

ケース推奨方法理由
在庫管理FOR UPDATE確実性が最重要
大量バッチSKIP LOCKED並列処理可能
参照チェックFOR SHARE削除防止だけ
軽い更新楽観的ロックパフォーマンス優先

行ロックは、データの整合性を守る最後の砦です。 適切に使えば、安全で高速なシステムを構築できます。

🚀 次のステップ

今すぐ試すべきこと：

  1. pg_stat_activityでロック状況を確認
  2. FOR UPDATEを使った更新処理を実装
  3. デッドロックを意図的に発生させて挙動を確認

スキルアップのために：

  1. アドバイザリーロックの活用
  2. 分離レベルの理解を深める
  3. パーティションテーブルでのロック戦略

この記事が、あなたのPostgreSQLシステムの 安全性と性能向上に役立つことを願っています！

トランザクションの安全性を確保して、 信頼されるシステムを構築しましょう！

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました