予約の二重受付を防ぐ実装の選び方。アプリ側の空き確認が同時の申込で破れることを並列リクエストで再現し、一意制約・ロック・条件つき更新・排他制約を比べる

// 方式1：アプリ側で空きを確かめてから登録する（同時の申込で破れる）
async function naive(slotId, customer) {
  const slot = db.prepare("SELECT capacity FROM slots WHERE id = ?").get(slotId);
  const { n } = db.prepare("SELECT COUNT(*) AS n FROM bookings WHERE slot_id = ?").get(slotId);
  if (n >= slot.capacity) return [409, { error: "満席" }];
  await sleep(DELAY); // 本番ではここに入力の検証や外部APIの呼び出しが挟まる
  db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
  return [201, { ok: true }];
}

CREATE TABLE bookings_unique (
  id       INTEGER PRIMARY KEY,
  slot_id  INTEGER NOT NULL REFERENCES slots(id),
  customer TEXT NOT NULL,
  UNIQUE (slot_id)
);

// 方式2：一意制約に任せる（定員1の枠向け）
async function unique(slotId, customer) {
  await sleep(DELAY);
  try {
    db.prepare("INSERT INTO bookings_unique (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
    return [201, { ok: true }];
  } catch (err) {
    if (err.errcode === 2067) return [409, { error: "満席" }]; // SQLITE_CONSTRAINT_UNIQUE
    throw err;
  }
}

CREATE TABLE slots (
  id        INTEGER PRIMARY KEY,
  starts_at TEXT NOT NULL,
  capacity  INTEGER NOT NULL,
  booked    INTEGER NOT NULL DEFAULT 0 CHECK (booked <= capacity)
);

// 方式4：条件つきの UPDATE で枠の残りを1つ減らし、成功したときだけ登録する（定員2以上の枠向け）
async function counter(slotId, customer) {
  await sleep(DELAY);
  db.exec("BEGIN IMMEDIATE");
  try {
    const r = db
      .prepare("UPDATE slots SET booked = booked + 1 WHERE id = ? AND booked < capacity")
      .run(slotId);
    if (r.changes === 0) {
      db.exec("ROLLBACK");
      return [409, { error: "満席" }];
    }
    db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
    db.exec("COMMIT");
    return [201, { ok: true }];
  } catch (err) {
    if (db.isTransaction) db.exec("ROLLBACK");
    throw err;
  }
}

// 方式3：トランザクションの最初に書き込みのロックを取り、確認と登録をまとめる
// 時間のかかる処理はトランザクションの前に済ませ、ロックの間は await しない
async function lock(slotId, customer) {
  await sleep(DELAY); // 入力の検証や外部APIの呼び出しは、ここ（ロックの前）で行う
  db.exec("BEGIN IMMEDIATE"); // PostgreSQL なら SELECT ... FOR UPDATE で枠の行をロックする
  try {
    const slot = db.prepare("SELECT capacity FROM slots WHERE id = ?").get(slotId);
    const { n } = db.prepare("SELECT COUNT(*) AS n FROM bookings WHERE slot_id = ?").get(slotId);
    if (n >= slot.capacity) {
      db.exec("ROLLBACK");
      return [409, { error: "満席" }];
    }
    db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
    db.exec("COMMIT");
    return [201, { ok: true }];
  } catch (err) {
    if (db.isTransaction) db.exec("ROLLBACK");
    throw err;
  }
}

// PostgreSQL（pg のプール）での書き方の例。ここでは実行していない
async function reserveWithLock(pool, slotId, customer) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const slot = await client.query("SELECT capacity FROM slots WHERE id = $1 FOR UPDATE", [slotId]);
    const { rows } = await client.query("SELECT count(*)::int AS n FROM bookings WHERE slot_id = $1", [slotId]);
    if (rows[0].n >= slot.rows[0].capacity) {
      await client.query("ROLLBACK");
      return "満席";
    }
    await client.query("INSERT INTO bookings (slot_id, customer) VALUES ($1, $2)", [slotId, customer]);
    await client.query("COMMIT");
    return "受付";
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservations (
  id         bigserial PRIMARY KEY,
  room_id    integer   NOT NULL,
  customer   text      NOT NULL,
  during     tstzrange NOT NULL,
  status     text      NOT NULL DEFAULT 'confirmed',
  -- 同じ部屋で、時間帯が重なる「確定」の予約を許さない
  CONSTRAINT no_overlap EXCLUDE USING gist (
    room_id WITH =,
    during  WITH &&
  ) WHERE (status = 'confirmed')
);

OK    A 10:00-11:00
NG    B 10:30-11:30（Aと重なる）: 23P01 conflicting key value violates exclusion constraint "no_overlap"
OK    C 11:00-12:00（Aの終わりと接する）
OK    D 10:30-11:30（別の部屋）
OK    E 10:15-10:45（取消済みとして記録）

// setup.mjs
// テスト用のデータベースを作り直す
import { DatabaseSync } from "node:sqlite";
import { rmSync } from "node:fs";

const FILE = "booking.db";
for (const f of [FILE, FILE + "-wal", FILE + "-shm"]) rmSync(f, { force: true });

const db = new DatabaseSync(FILE);
db.exec(`
  PRAGMA journal_mode = WAL;

  -- 予約枠（定員つき）
  CREATE TABLE slots (
    id       INTEGER PRIMARY KEY,
    starts_at TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    booked   INTEGER NOT NULL DEFAULT 0 CHECK (booked <= capacity)
  );

  -- 方式1〜3で使う予約の表（制約なし）
  CREATE TABLE bookings (
    id       INTEGER PRIMARY KEY,
    slot_id  INTEGER NOT NULL REFERENCES slots(id),
    customer TEXT NOT NULL
  );

  -- 方式2で使う予約の表（1枠1件を一意制約で守る）
  CREATE TABLE bookings_unique (
    id       INTEGER PRIMARY KEY,
    slot_id  INTEGER NOT NULL REFERENCES slots(id),
    customer TEXT NOT NULL,
    UNIQUE (slot_id)
  );

  INSERT INTO slots (id, starts_at, capacity) VALUES
    (1, '2026-10-10T10:00', 1),  -- 定員1の枠
    (2, '2026-10-10T11:00', 3);  -- 定員3の枠
`);
db.close();
console.log("setup done");

// server.mjs
// 予約を受け付けるHTTPサーバー。プロセスを4つ立て、それぞれが別の接続でDBを使う
import cluster from "node:cluster";
import http from "node:http";
import { DatabaseSync } from "node:sqlite";
import { setTimeout as sleep } from "node:timers/promises";

const PORT = 8920;
const WORKERS = 4;
const DELAY = Number(process.env.DELAY ?? 20); // 確認と登録のあいだに挟まる処理の時間（ミリ秒）

if (cluster.isPrimary) {
  for (let i = 0; i < WORKERS; i++) cluster.fork();
} else {
  const db = new DatabaseSync("booking.db");
  db.exec("PRAGMA busy_timeout = 5000"); // 書き込みの順番待ちを最大5秒まで待つ

  const handlers = { naive, "check-only": checkOnly, unique, deferred, lock, "lock-await": lockAwait, counter };

  http.createServer(async (req, res) => {
    const url = new URL(req.url, "http://localhost");
    const handler = handlers[url.pathname.slice(1)];
    if (!handler) return send(res, 404, { error: "not found" });
    const slotId = Number(url.searchParams.get("slot"));
    const customer = url.searchParams.get("customer");
    try {
      send(res, ...(await handler(slotId, customer)));
    } catch (err) {
      send(res, 500, { error: String(err.message) });
    }
  }).listen(PORT);

  // 方式1：アプリ側で空きを確かめてから登録する（同時の申込で破れる）
  async function naive(slotId, customer) {
    const slot = db.prepare("SELECT capacity FROM slots WHERE id = ?").get(slotId);
    const { n } = db.prepare("SELECT COUNT(*) AS n FROM bookings WHERE slot_id = ?").get(slotId);
    if (n >= slot.capacity) return [409, { error: "満席" }];
    await sleep(DELAY); // 本番ではここに入力の検証や外部APIの呼び出しが挟まる
    db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
    return [201, { ok: true }];
  }

  // 方式1の別形：確認と登録のあいだに await を挟まない。それでも別のプロセスとは同時に動く
  async function checkOnly(slotId, customer) {
    await sleep(DELAY);
    const slot = db.prepare("SELECT capacity FROM slots WHERE id = ?").get(slotId);
    const { n } = db.prepare("SELECT COUNT(*) AS n FROM bookings WHERE slot_id = ?").get(slotId);
    if (n >= slot.capacity) return [409, { error: "満席" }];
    db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
    return [201, { ok: true }];
  }

  // 方式2：一意制約に任せる（定員1の枠向け）
  async function unique(slotId, customer) {
    await sleep(DELAY);
    try {
      db.prepare("INSERT INTO bookings_unique (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
      return [201, { ok: true }];
    } catch (err) {
      if (err.errcode === 2067) return [409, { error: "満席" }]; // SQLITE_CONSTRAINT_UNIQUE
      throw err;
    }
  }

  // 方式3：トランザクションの最初に書き込みのロックを取り、確認と登録をまとめる
  // 時間のかかる処理はトランザクションの前に済ませ、ロックの間は await しない
  async function lock(slotId, customer) {
    await sleep(DELAY); // 入力の検証や外部APIの呼び出しは、ここ（ロックの前）で行う
    db.exec("BEGIN IMMEDIATE"); // PostgreSQL なら SELECT ... FOR UPDATE で枠の行をロックする
    try {
      const slot = db.prepare("SELECT capacity FROM slots WHERE id = ?").get(slotId);
      const { n } = db.prepare("SELECT COUNT(*) AS n FROM bookings WHERE slot_id = ?").get(slotId);
      if (n >= slot.capacity) {
        db.exec("ROLLBACK");
        return [409, { error: "満席" }];
      }
      db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
      db.exec("COMMIT");
      return [201, { ok: true }];
    } catch (err) {
      if (db.isTransaction) db.exec("ROLLBACK");
      throw err;
    }
  }

  // 方式3の比較：ふつうの BEGIN（DEFERRED）。読み取りから書き込みへ移るときにぶつかる
  async function deferred(slotId, customer) {
    await sleep(DELAY);
    db.exec("BEGIN");
    try {
      const slot = db.prepare("SELECT capacity FROM slots WHERE id = ?").get(slotId);
      const { n } = db.prepare("SELECT COUNT(*) AS n FROM bookings WHERE slot_id = ?").get(slotId);
      if (n >= slot.capacity) {
        db.exec("ROLLBACK");
        return [409, { error: "満席" }];
      }
      db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
      db.exec("COMMIT");
      return [201, { ok: true }];
    } catch (err) {
      if (db.isTransaction) db.exec("ROLLBACK");
      throw err;
    }
  }

  // 方式3の失敗例：ロックを取ったまま await する
  async function lockAwait(slotId, customer) {
    db.exec("BEGIN IMMEDIATE");
    try {
      const slot = db.prepare("SELECT capacity FROM slots WHERE id = ?").get(slotId);
      const { n } = db.prepare("SELECT COUNT(*) AS n FROM bookings WHERE slot_id = ?").get(slotId);
      if (n >= slot.capacity) {
        db.exec("ROLLBACK");
        return [409, { error: "満席" }];
      }
      await sleep(DELAY); // ロックを持ったまま待つ
      db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
      db.exec("COMMIT");
      return [201, { ok: true }];
    } catch (err) {
      if (db.isTransaction) db.exec("ROLLBACK");
      throw err;
    }
  }

  // 方式4：条件つきの UPDATE で枠の残りを1つ減らし、成功したときだけ登録する（定員2以上の枠向け）
  async function counter(slotId, customer) {
    await sleep(DELAY);
    db.exec("BEGIN IMMEDIATE");
    try {
      const r = db
        .prepare("UPDATE slots SET booked = booked + 1 WHERE id = ? AND booked < capacity")
        .run(slotId);
      if (r.changes === 0) {
        db.exec("ROLLBACK");
        return [409, { error: "満席" }];
      }
      db.prepare("INSERT INTO bookings (slot_id, customer) VALUES (?, ?)").run(slotId, customer);
      db.exec("COMMIT");
      return [201, { ok: true }];
    } catch (err) {
      if (db.isTransaction) db.exec("ROLLBACK");
      throw err;
    }
  }
}

function send(res, status, body) {
  res.writeHead(status, { "content-type": "application/json; charset=utf-8" });
  res.end(JSON.stringify(body));
}

// race.mjs
// 同じ枠に、同時に N 件の申込を送り、何件受け付けられたかを数える
// 使い方: node race.mjs <方式> <枠のID> <件数>
import { DatabaseSync } from "node:sqlite";

const [mode = "naive", slot = "1", count = "10"] = process.argv.slice(2);
const N = Number(count);

const results = await Promise.all(
  Array.from({ length: N }, (_, i) =>
    fetch(`http://127.0.0.1:8920/${mode}?slot=${slot}&customer=c${i + 1}`).then((r) => r.status),
  ),
);

const tally = results.reduce((acc, s) => ((acc[s] = (acc[s] ?? 0) + 1), acc), {});
const db = new DatabaseSync("booking.db", { readOnly: true });
const table = mode === "unique" ? "bookings_unique" : "bookings";
const { n } = db.prepare(`SELECT COUNT(*) AS n FROM ${table} WHERE slot_id = ?`).get(Number(slot));
const { capacity } = db.prepare("SELECT capacity FROM slots WHERE id = ?").get(Number(slot));
console.log(`${mode} 枠${slot}（定員${capacity}）に${N}件: 応答 ${JSON.stringify(tally)} / 登録された件数 ${n}`);

# run-all.sh
#!/bin/bash
# 方式ごとに、DBを作り直してサーバーを起動し、同じ枠へ同時に30件の申込を送る
run() {
  node setup.mjs >/dev/null 2>&1
  node server.mjs > server.log 2>&1 &
  SERVER=$!
  sleep 1.5
  for args in "$@"; do node race.mjs $args 2>/dev/null; done
  kill $SERVER; wait $SERVER 2>/dev/null
}
for mode in naive check-only unique deferred lock-await lock counter; do
  if [ "$mode" = "unique" ]; then run "unique 1 30"; else run "$mode 1 30" "$mode 2 30"; fi
done

// pg-lock.mjs
// PGlite で、行ロックと条件つき UPDATE の SQL が通るかを確かめる（1接続のみ。同時実行は確かめていない）
import { PGlite } from "@electric-sql/pglite";

const db = new PGlite();
await db.exec(`
  CREATE TABLE slots (
    id       integer PRIMARY KEY,
    capacity integer NOT NULL,
    booked   integer NOT NULL DEFAULT 0 CHECK (booked <= capacity)
  );
  CREATE TABLE bookings (
    id       bigserial PRIMARY KEY,
    slot_id  integer NOT NULL REFERENCES slots(id),
    customer text    NOT NULL
  );
  CREATE TABLE bookings_unique (
    id       bigserial PRIMARY KEY,
    slot_id  integer NOT NULL REFERENCES slots(id),
    customer text    NOT NULL,
    CONSTRAINT one_booking_per_slot UNIQUE (slot_id)
  );
  INSERT INTO slots (id, capacity) VALUES (1, 1), (2, 3);
`);

// 方式3：枠の行を FOR UPDATE でロックしてから数える
async function reserveWithLock(slotId, customer) {
  return db.transaction(async (tx) => {
    const slot = (await tx.query("SELECT capacity FROM slots WHERE id = $1 FOR UPDATE", [slotId])).rows[0];
    const { n } = (await tx.query("SELECT count(*)::int AS n FROM bookings WHERE slot_id = $1", [slotId])).rows[0];
    if (n >= slot.capacity) return "満席";
    await tx.query("INSERT INTO bookings (slot_id, customer) VALUES ($1, $2)", [slotId, customer]);
    return "受付";
  });
}

// 方式4：条件つき UPDATE で残りを減らす
async function reserveWithCounter(slotId, customer) {
  return db.transaction(async (tx) => {
    const r = await tx.query(
      "UPDATE slots SET booked = booked + 1 WHERE id = $1 AND booked < capacity RETURNING booked",
      [slotId],
    );
    if (r.rows.length === 0) return "満席";
    await tx.query("INSERT INTO bookings (slot_id, customer) VALUES ($1, $2)", [slotId, customer]);
    return "受付";
  });
}

const lockResults = [];
for (let i = 1; i <= 3; i++) lockResults.push(await reserveWithLock(1, `L${i}`));
console.log("FOR UPDATE（定員1に3件）:", lockResults.join(" / "));

const counterResults = [];
for (let i = 1; i <= 5; i++) counterResults.push(await reserveWithCounter(2, `C${i}`));
console.log("条件つき UPDATE（定員3に5件）:", counterResults.join(" / "));

await db.query("INSERT INTO bookings_unique (slot_id, customer) VALUES (1, 'U1')");
try {
  await db.query("INSERT INTO bookings_unique (slot_id, customer) VALUES (1, 'U2')");
} catch (err) {
  console.log("一意制約（同じ枠に2件目）:", err.code, err.message);
}

FOR UPDATE（定員1に3件）: 受付 / 満席 / 満席
条件つき UPDATE（定員3に5件）: 受付 / 受付 / 受付 / 満席 / 満席
一意制約（同じ枠に2件目）: 23505 duplicate key value violates unique constraint "one_booking_per_slot"

// pg-exclusion.mjs
// PGlite（WebAssembly で動く PostgreSQL）で、排他制約が重なる予約を拒むかを確かめる
import { PGlite } from "@electric-sql/pglite";
import { btree_gist } from "@electric-sql/pglite/contrib/btree_gist";

const db = new PGlite({ extensions: { btree_gist } });
console.log((await db.query("SELECT version()")).rows[0].version);

await db.exec(`
  CREATE EXTENSION IF NOT EXISTS btree_gist;

  CREATE TABLE reservations (
    id         bigserial PRIMARY KEY,
    room_id    integer   NOT NULL,
    customer   text      NOT NULL,
    during     tstzrange NOT NULL,
    status     text      NOT NULL DEFAULT 'confirmed',
    -- 同じ部屋で、時間帯が重なる「確定」の予約を許さない
    CONSTRAINT no_overlap EXCLUDE USING gist (
      room_id WITH =,
      during  WITH &&
    ) WHERE (status = 'confirmed')
  );
`);

async function tryInsert(label, roomId, from, to, status = "confirmed") {
  try {
    await db.query(
      "INSERT INTO reservations (room_id, customer, during, status) VALUES ($1, $2, tstzrange($3, $4, '[)'), $5)",
      [roomId, label, from, to, status],
    );
    console.log(`OK    ${label}`);
  } catch (err) {
    console.log(`NG    ${label}: ${err.code} ${err.message}`);
  }
}

await tryInsert("A 10:00-11:00",               1, "2026-10-10 10:00+09", "2026-10-10 11:00+09");
await tryInsert("B 10:30-11:30（Aと重なる）",  1, "2026-10-10 10:30+09", "2026-10-10 11:30+09");
await tryInsert("C 11:00-12:00（Aの終わりと接する）", 1, "2026-10-10 11:00+09", "2026-10-10 12:00+09");
await tryInsert("D 10:30-11:30（別の部屋）",   2, "2026-10-10 10:30+09", "2026-10-10 11:30+09");
await tryInsert("E 10:15-10:45（取消済みとして記録）", 1, "2026-10-10 10:15+09", "2026-10-10 10:45+09", "cancelled");