الـ MVCC ومستويات الـ Isolation: transactions كتير في نفس الوقت

import { twoClients, one } from "./db.js";

const [a, b] = await twoClients();
await a.query("DROP TABLE IF EXISTS counters; CREATE TABLE counters (id int PRIMARY KEY, n int); INSERT INTO counters VALUES (1, 0);");

// كل UPDATE بيعمل نسخة جديدة من الصف (ctid مختلف)، والقديمة بتفضل لحد ما VACUUM يشيلها
console.log("قبل:", await one(a, "SELECT ctid, xmin, n FROM counters"));

await a.query("BEGIN");
await a.query("UPDATE counters SET n = n + 1 WHERE id = 1");
console.log("A شايف:", await one(a, "SELECT ctid, n FROM counters"));
console.log("B شايف:", await one(b, "SELECT ctid, n FROM counters"), "← القديم، ومن غير ما يستنى");
await a.query("COMMIT");
console.log("B بعد COMMIT:", await one(b, "SELECT ctid, n FROM counters"));
await a.end();
await b.end();

$ node main.js
قبل: { ctid: '(0,1)', xmin: '733', n: 0 }
A شايف: { ctid: '(0,2)', n: 1 }
B شايف: { ctid: '(0,1)', n: 0 } ← القديم، ومن غير ما يستنى
B بعد COMMIT: { ctid: '(0,2)', n: 1 }

import { twoClients, one } from "./db.js";

const [a, b] = await twoClients();
await a.query("DROP TABLE IF EXISTS stock; CREATE TABLE stock (id int PRIMARY KEY, qty int); INSERT INTO stock VALUES (1, 10);");

// اتنين بيشتروا في نفس الوقت: اقرا، احسب في JavaScript، اكتب
async function buyStep1(c) {
  await c.query("BEGIN");
  return (await one(c, "SELECT qty FROM stock WHERE id = 1")).qty;
}
async function buyStep2(c, qty) {
  await c.query("UPDATE stock SET qty = $1 WHERE id = 1", [qty - 1]);
  await c.query("COMMIT");
}

const qa = await buyStep1(a);
const qb = await buyStep1(b);
await buyStep2(a, qa);
await buyStep2(b, qb);
console.log("Read Committed + اقرا/اكتب:", await one(a, "SELECT qty FROM stock"), "← اتباع اتنين، والمخزن نقص واحد بس");

// الحل 1: خلي Postgres يحسب (atomic update)
await a.query("UPDATE stock SET qty = 10");
await Promise.all([
  a.query("UPDATE stock SET qty = qty - 1 WHERE id = 1"),
  b.query("UPDATE stock SET qty = qty - 1 WHERE id = 1"),
]);
console.log("atomic update:", await one(a, "SELECT qty FROM stock"));

// الحل 2: REPEATABLE READ بيكشف التعارض
await a.query("UPDATE stock SET qty = 10");
await a.query("BEGIN ISOLATION LEVEL REPEATABLE READ");
await b.query("BEGIN ISOLATION LEVEL REPEATABLE READ");
const ra = (await one(a, "SELECT qty FROM stock")).qty;
const rb = (await one(b, "SELECT qty FROM stock")).qty;
await a.query("UPDATE stock SET qty = $1", [ra - 1]);
await a.query("COMMIT");
try {
  await b.query("UPDATE stock SET qty = $1", [rb - 1]);
} catch (err) {
  console.log("B:", err.code, err.message);
  await b.query("ROLLBACK");
}
await a.end();
await b.end();

$ node main.js
Read Committed + اقرا/اكتب: { qty: 9 } ← اتباع اتنين، والمخزن نقص واحد بس
atomic update: { qty: 8 }
B: 40001 could not serialize access due to concurrent update

import { twoClients, one } from "./db.js";

const [a, b] = await twoClients();
await a.query(`DROP TABLE IF EXISTS doctors;
  CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean);
  INSERT INTO doctors VALUES ('مُنى', true), ('عمر', true);`);

// القاعدة: لازم دكتور واحد على الأقل on call
async function leave(c, name, level) {
  await c.query(`BEGIN ISOLATION LEVEL ${level}`);
  const { n } = await one(c, "SELECT count(*)::int AS n FROM doctors WHERE on_call");
  return async () => {
    if (n >= 2) await c.query("UPDATE doctors SET on_call = false WHERE name = $1", [name]);
    await c.query("COMMIT");
  };
}

for (const level of ["REPEATABLE READ", "SERIALIZABLE"]) {
  await a.query("UPDATE doctors SET on_call = true");
  const finishA = await leave(a, "مُنى", level);
  const finishB = await leave(b, "عمر", level);
  await finishA();
  try {
    await finishB();
  } catch (err) {
    console.log(`${level}: B وقع ${err.code}`);
    await b.query("ROLLBACK");
  }
  const { n } = await one(a, "SELECT count(*)::int AS n FROM doctors WHERE on_call");
  console.log(`${level}: on call = ${n}`);
}
await a.end();
await b.end();

$ node main.js
REPEATABLE READ: on call = 0
SERIALIZABLE: B وقع 40001
SERIALIZABLE: on call = 1

import { twoClients, one } from "./db.js";

const [a, b] = await twoClients();
await a.query(`DROP TABLE IF EXISTS seats;
  CREATE TABLE seats (course text PRIMARY KEY, taken int NOT NULL, capacity int NOT NULL);
  INSERT INTO seats VALUES ('node-live', 29, 30);`);


async function withSerializable(c, fn, tries = 3) {
  for (let i = 1; i <= tries; i++) {
    try {
      await c.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
      const result = await fn(c);
      await c.query("COMMIT");
      return result;
    } catch (err) {
      await c.query("ROLLBACK");
      if (err.code !== "40001" || i === tries) throw err;
      console.log(`retry ${i} بعد ${err.code}`);
    }
  }
}

async function book(c) {
  const { taken, capacity } = await one(c, "SELECT taken, capacity FROM seats WHERE course = 'node-live'");
  if (taken >= capacity) throw new Error("الكورس كامل");
  await new Promise((r) => setTimeout(r, 50)); // وقت تفكير، علشان الاتنين يتداخلوا
  await c.query("UPDATE seats SET taken = $1 WHERE course = 'node-live'", [taken + 1]);
  return "حجزت";
}

const results = await Promise.allSettled([withSerializable(a, book), withSerializable(b, book)]);
for (const r of results) console.log(r.status, r.value ?? r.reason.message);
console.log("taken =", (await one(a, "SELECT taken FROM seats")).taken);
await a.end();
await b.end();

$ node main.js
retry 1 بعد 40001
fulfilled حجزت
rejected الكورس كامل
taken = 30