الـ Locks والـ Deadlocks: مين مستني مين

import { connect, one, sleep, reset } from "./db.js";

const a = await connect("A");
const b = await connect("B");
const watcher = await connect("watcher");
await reset(a);

await a.query("BEGIN");
await a.query("SELECT * FROM accounts WHERE id = 1 FOR UPDATE"); // A قفل الصف

// B عايز يعدّل نفس الصف: هيستنى
const bDone = b.query("UPDATE accounts SET balance = balance - 1 WHERE id = 1");
await sleep(100);

// مين مستني مين؟
const { rows } = await watcher.query(`
  SELECT application_name AS who, wait_event_type AS waiting_on, pg_blocking_pids(pid) AS blocked_by_pid,
         (SELECT application_name FROM pg_stat_activity x WHERE x.pid = (pg_blocking_pids(s.pid))[1]) AS blocked_by
  FROM pg_stat_activity s WHERE application_name IN ('A', 'B') ORDER BY 1`);
console.log(rows.map(({ who, waiting_on, blocked_by }) => ({ who, waiting_on, blocked_by })));

// NOWAIT: بدل ما تستنى، ارمي على طول
const c = await connect("C");
try {
  await c.query("SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT");
} catch (err) {
  console.log("C:", err.code, err.message);
}

await a.query("COMMIT");
await bDone;
console.log("B خلص بعد ما A عمل COMMIT:", await one(a, "SELECT balance FROM accounts WHERE id = 1"));
for (const x of [a, b, c, watcher]) await x.end();

$ node main.js
[
  { who: 'A', waiting_on: 'Client', blocked_by: null },
  { who: 'B', waiting_on: 'Lock', blocked_by: 'A' }
]
C: 55P03 could not obtain lock on row in relation "accounts"
B خلص بعد ما A عمل COMMIT: { balance: 999 }

import { connect, sleep, reset } from "./db.js";

const a = await connect("A");
const b = await connect("B");
await reset(a);

// A بيحوّل من 1 لـ 2، و B من 2 لـ 1، في نفس الوقت
async function transfer(c, from, to) {
  await c.query("BEGIN");
  try {
    await c.query("UPDATE accounts SET balance = balance - 10 WHERE id = $1", [from]);
    await sleep(100);
    await c.query("UPDATE accounts SET balance = balance + 10 WHERE id = $1", [to]);
    await c.query("COMMIT");
    return "تم";
  } catch (err) {
    await c.query("ROLLBACK");
    return `${err.code} ${err.message}`;
  }
}

console.log(await Promise.all([transfer(a, 1, 2), transfer(b, 2, 1)]));
await a.end();
await b.end();

$ node main.js
[ 'تم', '40P01 deadlock detected' ]

import { connect, sleep, reset } from "./db.js";

const report = await connect("report");
const admin = await connect("migration");
const user = await connect("user");
await reset(report);

// transaction طويلة (تقرير) شايلة lock خفيف
await report.query("BEGIN");
await report.query("SELECT count(*) FROM accounts");

// migration محتاجة lock تقيل: بتستنى التقرير
await admin.query("SET lock_timeout = '300ms'");
const migration = admin.query("ALTER TABLE accounts ADD COLUMN note text").then(() => "migration تمت", (e) => `migration: ${e.message}`);
await sleep(50);

// وأي query عادية دلوقتي بتقف ورا الـ migration في الطابور!
const t0 = Date.now();
await user.query("SELECT * FROM accounts WHERE id = 1");
console.log(await migration);
console.log(`select المستخدم استنى حوالي ${Math.round((Date.now() - t0) / 100) * 100}ms`);
await report.query("COMMIT");
for (const x of [report, admin, user]) await x.end();

$ node main.js
migration: canceling statement due to lock timeout
select المستخدم استنى حوالي 300ms

import { connect, reset } from "./db.js";

const conns = await Promise.all(["w1", "w2", "w3", "w4"].map(connect));
await reset(conns[0]);


async function transfer(c, from, to, amount) {
  for (let i = 1; i <= 3; i++) {
    try {
      await c.query("BEGIN");
      await c.query("SET LOCAL lock_timeout = '2s'");
      const { rows } = await c.query(
        "SELECT id, balance FROM accounts WHERE id = ANY($1) ORDER BY id FOR UPDATE",
        [[from, to]],
      );
      if (rows.find((r) => r.id === from).balance < amount) {
        await c.query("ROLLBACK");
        return "رصيد مش كفاية";
      }
      await c.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, from]);
      await c.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, to]);
      await c.query("COMMIT");
      return "تم";
    } catch (err) {
      await c.query("ROLLBACK");
      if (!["40P01", "55P03"].includes(err.code) || i === 3) throw err;
    }
  }
}

const results = await Promise.allSettled(
  conns.map(async (c, k) => {
    const out = [];
    for (let i = 0; i < 10; i++) out.push(await transfer(c, (i + k) % 2 ? 1 : 2, (i + k) % 2 ? 2 : 1, 10));
    return out;
  }),
);
const done = results.flatMap((r) => r.value ?? []).filter((x) => x === "تم").length;
const errors = results.filter((r) => r.status === "rejected").length;
const { sum } = (await conns[0].query("SELECT sum(balance)::int AS sum FROM accounts")).rows[0];
console.log("تم:", done);
console.log("errors:", errors);
console.log("المجموع:", sum);
for (const c of conns) await c.end();

$ node main.js
تم: 40
errors: 0
المجموع: 2000