Transaction আর isolation level — anomaly, locking আর MVCC

১ · Transaction কেন — শেষ সিট, দুজন যাত্রী আর ACID-এর চার প্রতিশ্রুতি

ঈদের আগে রাত ঠিক ১২টায় একটি কাল্পনিক বাস কোম্পানির অনলাইন টিকিট খুলল। ঢাকা–রাজশাহী রাতের বাসে বাকি আছে একটিমাত্র সিট, B4. রাহিম মোবাইল অ্যাপে আর করিম ওয়েবসাইটে প্রায় একই মিলিসেকেন্ডে “কিনুন” চাপলেন। Load balancer দুটি অনুরোধ দুটি আলাদা app server-এ পাঠাল। দুই সার্ভারই database-এ জিজ্ঞেস করল “B4 খালি?” — দুজনেই উত্তর পেল “হ্যাঁ”। তারপর দুজনেই লিখল “B4 বিক্রি হয়েছে”। দুজনের ফোনেই টিকিট, বাসে সিট একটি।

এর নাম race condition: ফল নির্ভর করছে কে কখন কোন ধাপে পৌঁছাল তার ওপর। একা চালালে কোডটি নির্ভুল, একসাথে দুজন চালালেই ভুল — তাই টেস্টে প্রায় ধরা পড়ে না, অথচ ঈদের রাতের ভিড়ে প্রতি মিনিটে ঘটে।

Transaction হলো কয়েকটি পড়া আর লেখাকে একটি একক কাজ হিসেবে ধরা: BEGIN থেকে COMMIT পর্যন্ত। কাজটি হয় পুরোটা টেকে (commit), নয় পুরোটা মুছে যায় (rollback). Database এই একককে ঘিরে চারটি প্রতিশ্রুতি দেয়, যাদের আদ্যক্ষর মিলে ACID. নিচের ছবিতে দেখুন এগুলো একটি সাধারণ backend-এর কোথায় কাজ করে।

নকশাকারের চোখে ACID — কোন অক্ষর কোন প্রশ্নের উত্তর

দুটি ভুল ধারণা আগেই ভাঙা দরকার। এক, atomicity ব্যর্থতার বিষয়, একসাথে চলার নয় — “মাঝখানে কেউ ঢুকবে না” হলো isolation-এর কাজ। দুই, consistency-র বেশিরভাগটাই আপনার দায়িত্ব: database শুধু আপনার দেওয়া constraint (CHECK, UNIQUE, foreign key) পাহারা দেয়। “দিনে ২৫,০০০ টাকার বেশি পাঠানো যাবে না” — এমন নিয়ম রাখে আপনার transaction-এর যুক্তি, আর ভিড়ের মধ্যে সেই যুক্তি টিকবে কি না, তা ঠিক করে isolation.

২ · Anomaly-র তালিকা — dirty read থেকে lost update

একসাথে চলা transaction-গুলোর ফল যদি এমন হয়, যা কোনো একটি-একটি-করে (serial) ক্রমে চালালে কখনো হতো না, তাকে বলে anomaly. চেহারা চিনলে production-এর bug report পড়েই কারণ আন্দাজ করা যায়। উদাহরণে একটি কাল্পনিক mobile wallet: রাহিমের ব্যালান্স ১,০০০ টাকা।

Dirty write আর dirty read — অসমাপ্ত কাজ দেখা বা তার ওপর লেখা

Dirty write: T1 ব্যালান্সে কিছু লিখেছে কিন্তু commit করেনি, তার ওপরেই T2 লিখে দিল। এরপর T1 rollback করলে কোন মান ফিরবে? উত্তর নেই — তাই প্রায় সব database সব level-এ এটি নিষেধ করে, একটি সারিতে একজন লেখকের lock দিয়ে।

Dirty read: রাহিম ৭০০ টাকা cash-out শুরু করলেন (T1), ব্যালান্স হলো ৩০০, কিন্তু commit হয়নি। ঠিক তখন একটি fraud-check রিপোর্ট (T2) পড়ল ৩০০ আর রাহিমকে “কম ব্যালান্স” তালিকায় তুলল। তারপর এজেন্টের যন্ত্রে সমস্যা, T1 rollback — ব্যালান্স আবার ১,০০০। রিপোর্টটি এমন একটি সংখ্যার ওপর সিদ্ধান্ত নিল, যা কখনো সত্যি হয়নি।

Non-repeatable read আর read skew — দুই মুহূর্তের মিশ্র ছবি

Non-repeatable read: একই transaction-এ একই সারি দুবার পড়ে দুই রকম মান — মাঝখানে অন্য কেউ বদলে commit করেছে। এর বড় ভাই read skew: রাহিমের সঞ্চয়ী হিসাবে ১,০০০ আর ওয়ালেটে ১,০০০, মোট ২,০০০। দিনের শেষের হিসাব-মেলানো রিপোর্ট (T2) আগে সঞ্চয় পড়ল — ১,০০০। তারপর T1 সঞ্চয় থেকে ওয়ালেটে ৫০০ সরিয়ে commit করল। এবার T2 ওয়ালেট পড়ল — ১,৫০০। রিপোর্টে মোট ২,৫০০ — প্রতিটি সংখ্যা কোনো এক মুহূর্তে সত্যি, দুটি একসাথে কখনো নয়। Backup, export আর হিসাব-মেলানোয় এটি মারাত্মক।

Lost update — সবচেয়ে চেনা production bug

দুজন একই পুরোনো মান পড়েন, অ্যাপে হিসাব করেন, তারপর লেখেন — পরের লেখা আগেরটিকে নিঃশব্দে মুছে দেয়। রাহিম অ্যাপে ৭০০ টাকা cash-out (T1) আর একই সময়ে এজেন্ট পয়েন্টে ৬০০ টাকা উত্তোলন (T2) করলেন। নিচের সময়রেখায় প্রতিটি পড়া (R), লেখা (W) আর commit (C) দেখুন।

এবার একই ঘটনা ধাপে ধাপে দেখুন। প্রতিটি ধাপে কে কী জানে আর invariant — “ব্যালান্স = ১,০০০ − মোট বেরোনো টাকা” — টিকে আছে কি না, খেয়াল করুন।

৩ · Write skew আর phantom — যখন দুজন দুটি আলাদা সারিতে লেখেন

Lost update-এ দুজন একই সারিতে লেখেন, তাই database চাইলে সংঘাত দেখতে পায়। আরও সূক্ষ্ম অসুখ write skew: দুজন একই তথ্য পড়েন, কিন্তু লেখেন আলাদা সারিতে। কেউ কারও লেখা মোছেন না, অথচ দুই লেখা মিলে নিয়ম ভাঙে।

ক্লাসিক উদাহরণ একটি হাসপাতালের (কাল্পনিক) রাতের shift. নিয়ম: অন্তত একজন ডাক্তার on call থাকবেন। অ্যালিস আর বব দুজনেই on call, দুজনেরই একটু অসুস্থ লাগছে, আর দুজনে প্রায় একসাথে অ্যাপে “ছুটি চাই” চাপলেন। প্রত্যেকের transaction গুনল: on call ২ জন — “আমি ছুটি নিলেও একজন থাকবেন।” অ্যালিসের transaction অ্যালিসের সারি বদলাল, ববেরটি ববের সারি। দুটিই commit — on call শূন্য।

Snapshot isolation-ও এখানে হারে, কারণ সে শুধু দেখে দুজন একই সারিতে লিখেছেন কি না:

Phantom — যে সারি এখনো নেই, তাকে lock করবেন কীভাবে?

শুরুর বাসের সিটের গল্পটি write skew-এর আরও কঠিন রূপ। দুই সার্ভার খুঁজল SELECT … WHERE trip_id = 7 AND seat_no = 'B4' — কোনো সারি নেই। তারপর দুজনেই নতুন সারি INSERT করল। একটি শর্তের খোঁজের ফল অন্যের লেখায় বদলে যাওয়াকে বলে phantom. Lock দেওয়ার মতো কোনো সারিই তখন ছিল না, তাই সারি-স্তরের lock অকেজো।

একই ছাঁচ: একই username দুজনের, একই মিটিং রুম একই সময়ে দুবার, বা দুটি যৌথ হিসাবের “মোট ব্যালান্স ≥ ০” ভাঙা। প্রতিকার তিন দিকে। Lock-ভিত্তিক database শর্ত বা index-এর পরিসরে lock নেয় (predicate lock, InnoDB-এর next-key lock). SSI খোঁজের পরিসরও মনে রাখে। আর নকশাকারের সবচেয়ে সহজ হাতিয়ার: নিয়মটি schema-তে লেখা — UNIQUE (trip_id, seat_no) (অংশ ৬)।

৪ · Isolation level — কোনটি কী ঠেকায়, আর নামের ফাঁদ

পুরো isolation (সব সময় serializable) দামি, তাই SQL standard (SQL-92) চারটি ধাপ দিয়েছে। Isolation level যত ওপরে, তত কম anomaly, কিন্তু তত বেশি অপেক্ষা বা abort. Standard ধাপগুলো সংজ্ঞায়িত করেছে মাত্র তিনটি ঘটনা দিয়ে — dirty read, non-repeatable read আর phantom. বাস্তবের database আর পরের গবেষণা মিলিয়ে আরও পূর্ণ ছবিটি এমন (সাধারণ বাস্তবায়ন ধরে):

* অ্যাপে পড়ে, হিসাব করে, তারপর মান লিখলে। SET x = x − 1-এর মতো এক বাক্যের atomic update-এ READ COMMITTED-ও নিরাপদ (অংশ ৬)। আর dirty write প্রায় সব বাস্তব database সব level-এই ঠেকায়।

একই নাম, আলাদা আচরণ — database-ভেদে

এখানেই নকশাকারের সবচেয়ে বড় ফাঁদ। Standard-এর ভাষা অস্পষ্ট ছিল, তাই প্রতিটি database নিজের যন্ত্র অনুযায়ী নামগুলো বসিয়েছে। লেখার সময়ের সাধারণ চিত্র (সংস্করণভেদে বদলাতে পারে — নিজের সংস্করণের documentation দেখুন):

ফল: MySQL থেকে PostgreSQL-এ সরালে একই REPEATABLE READ-এ যেখানে দ্বিতীয় লেখক নীরবে লিখত, এখন ত্রুটি আসবে আর retry লাগবে।

৫ · ভেতরের যন্ত্র — two-phase locking, MVCC আর SSI

Two-phase locking (2PL) — আগে lock, শেষে ছাড়া

প্রথম পথটি সোজা: পড়ার আগে shared lock (S), লেখার আগে exclusive lock (X). একই সারিতে অনেকের S একসাথে চলে, কিন্তু X থাকলে আর কেউ কিছু পায় না। Two-phase মানে একটি transaction প্রথমে শুধু lock নেয় (growing), আর একবার কোনো lock ছাড়লে আর নতুন lock নেয় না (shrinking). বাস্তবে প্রায় সবাই strict 2PL চালায়: সব lock commit বা abort পর্যন্ত ধরে রাখা।

১৯৭৬ সালে Eswaran, Gray, Lorie আর Traiger প্রমাণ করেন, সব transaction 2PL মানলে ফল কোনো serial ক্রমের সমান হয়। তাঁরা phantom-এর সমস্যাও তখনই দেখান, আর সমাধান হিসেবে শর্তের ওপর predicate lock প্রস্তাব করেন। দাম: পাঠক লেখককে আটকায়, লেখক পাঠককে, আর অপেক্ষা থেকে আসে deadlock.

Deadlock — চক্রে আটকে থাকা, আর database কীভাবে খোলে

ঢাকার চার রাস্তার মোড়ে চার দিক থেকে ঢোকা চারটি গাড়ি, প্রত্যেকে সামনেরটির অপেক্ষায় — কেউ নড়ে না। Database-এ: T1 হিসাব ৭ lock করে হিসাব ৯ চায়, T2 হিসাব ৯ ধরে হিসাব ৭ চায়। কে কার অপেক্ষায়, তা একটি wait-for graph: তীর A → B মানে “A, B-এর lock-এর অপেক্ষায়”। গ্রাফে চক্র মানেই deadlock. Database মাঝে মাঝে (PostgreSQL-এ ডিফল্টভাবে প্রায় ১ সেকেন্ড অপেক্ষার পরে) চক্র খোঁজে, আর একটি “victim” বেছে rollback করে। অ্যাপ পায় একটি ত্রুটি — PostgreSQL-এ 40P01, MySQL-এ 1213 — আর তার কাজ পুরো transaction আবার চালানো।

নিচের simulator-এ চারটি টাকা পাঠানোর transaction প্রত্যেকে দুটি হিসাব lock করে। প্রথমে “যে হিসাব থেকে টাকা যাবে, সেটি আগে”, তারপর “ছোট নম্বরের হিসাব আগে” নিয়মে:

// Four money transfers; each must lock two accounts. T1 started first, T4 last.
const TX = { T1: ["acc7", "acc9"], T2: ["acc9", "acc4"], T3: ["acc4", "acc7"], T4: ["acc2", "acc7"] };
function waitFor(order) {                         // edge A -> B: "A waits for a lock B holds"
  const holder = {}, edge = {};
  const ask = (t, acc) => (holder[acc] ? (edge[t] = holder[acc]) : (holder[acc] = t));
  for (const [t, accs] of Object.entries(TX)) ask(t, order(accs)[0]);          // round 1: first lock
  for (const [t, accs] of Object.entries(TX)) if (!edge[t]) ask(t, order(accs)[1]); // round 2
  return edge;
}
function findCycle(edge) {                        // each T waits for at most one holder here
  for (const start of Object.keys(edge)) {
    const path = []; let t = start;
    while (t && !path.includes(t)) { path.push(t); t = edge[t]; }
    if (t) return path.slice(path.indexOf(t));
  }
  return null;
}
const plans = {
  "lock 'from' account first": (a) => a,
  "lock lower account id first": (a) => [...a].sort((x, y) => +x.slice(3) - +y.slice(3)),
};
for (const [name, order] of Object.entries(plans)) {
  const edge = waitFor(order), cyc = findCycle(edge);
  console.log(name + "\n  wait-for: " + Object.entries(edge).map(([a, b]) => a + "->" + b).join("  "));
  if (!cyc) { console.log("  no cycle: no deadlock, the waits drain one by one\n"); continue; }
  const victim = cyc.reduce((a, b) => (+b.slice(1) > +a.slice(1) ? b : a));   // youngest
  console.log("  cycle: " + cyc.concat(cyc[0]).join(" -> ") + "  => DEADLOCK");
  console.log("  victim: " + victim + " (youngest) is rolled back; the app must retry it\n");
}

lock 'from' account first
  wait-for: T1->T2  T2->T3  T3->T1  T4->T1
  cycle: T1 -> T2 -> T3 -> T1  => DEADLOCK
  victim: T3 (youngest) is rolled back; the app must retry it

lock lower account id first
  wait-for: T3->T2  T2->T1  T4->T1
  no cycle: no deadlock, the waits drain one by one

MVCC আর snapshot isolation — পাঠক লেখককে আটকায় না

দ্বিতীয় পথ: সারির ওপর লেখা না বসিয়ে প্রতিটি লেখায় একটি নতুন version তৈরি করা। পুরোনো version কিছুক্ষণ রয়ে যায়। প্রতিটি transaction (বা প্রতিটি বাক্য) একটি snapshot পায় — কোন version তার কাছে দৃশ্যমান, তার নিয়ম। এই হলো MVCC (multi-version concurrency control). ফল: পাঠক লেখককে আটকায় না, লেখক পাঠককে আটকায় না। দীর্ঘ রিপোর্টের সময়ও বিক্রি চলে, আর রিপোর্ট একটি মুহূর্তেরই হিসাব দেখে।

Snapshot isolation-এ লেখার সংঘাত ধরা হয় first-committer-wins নিয়মে: দুজন একই সারিতে লিখলে যে আগে commit করে সে টেকে, অন্যজন abort. তাই lost update আপনাআপনি ধরা পড়ে, কিন্তু write set আলাদা হলে (অংশ ৩) write skew থেকে যায়। পুরোনো version-গুলো পরিষ্কার করতে হয় (PostgreSQL-এ VACUUM, InnoDB-তে purge) — অংশ ৭-এ এর দাম দেখবেন। xmin, xmax আর visibility-র খুঁটিনাটি DBMS কোর্সের MVCC অংশে।

Serializable snapshot isolation (SSI) — snapshot-এর গতি, serializable-এর নিশ্চয়তা

২০০৮ সালে Michael Cahill, Uwe Röhm আর Alan Fekete একটি পথ দেখান: snapshot isolation রেখেই কে কী পড়েছে আর কে তা পরে লিখেছে, সেই “পড়া-তারপর-লেখা” নির্ভরতাগুলো চলার সময় খেয়াল রাখা। কোনো transaction-এর একদিকে একটি ঢোকা আর অন্যদিকে একটি বেরোনো এমন নির্ভরতা থাকলে — বিপজ্জনক কাঠামো — একজনকে abort করা। ডাক্তারদের ঘটনায় ঠিক তা-ই ঘটে। PostgreSQL ২০১১ সালের 9.1 সংস্করণে তাদের SERIALIZABLE এভাবে বানায়। দাম: lock-এর অপেক্ষা নেই, কিন্তু কখনো নির্দোষ transaction-ও abort হয় (false positive), তাই retry বাধ্যতামূলক।

চারটি mode পাশাপাশি — interleaving simulator

এবার তিনটি ঘটনা — ওয়ালেট, ডাক্তার আর সিট B4 — চারটি mode-এ চালান। প্রতিটি transaction-এর তিন ধাপ: পড়া (r), লেখা (w), commit (c); SCHED বলে কোন ধাপ কখন। খেলনা-মডেলে none-এ লেখা সঙ্গে সঙ্গে সবার চোখে; read committed-এ শুধু commit হওয়া মান পড়া যায়; snapshot-এ শুরুর ছবি আর first-committer-wins; serializable (2PL)-এ commit পর্যন্ত ধরা S/X lock, আর সিটের খোঁজে একটি পরিসর-lock (B4-range, next-key lock-এর মতো)।

const SCHED = ["1r", "2r", "1w", "1c", "2w", "2c"];  // toy model: whose step next (read, write, commit)
const AMT = [700, 600], DOC = ["alice", "bob"], nB4 = (v) => Object.values(v).filter((x) => x === "B4").length;
const SC = {
  "wallet: lost update": { init: { bal: 1000 }, rk: () => ["bal"], wk: () => ["bal"],
    run: (v, i) => (v.bal >= AMT[i] ? { bal: v.bal - AMT[i] } : {}),
    ok: (s, done) => s.bal >= 0 && s.bal === 1000 - done.reduce((t, i) => t + AMT[i], 0) },
  "doctors: write skew": { init: { alice: 1, bob: 1 }, rk: () => DOC, wk: (i) => [DOC[i]],
    run: (v, i) => (v.alice + v.bob >= 2 ? { [DOC[i]]: 0 } : {}), ok: (s) => s.alice + s.bob >= 1 },
  "seat B4: double booking": { init: {}, rk: () => ["B4-range"], wk: (i) => ["bk" + (i + 1), "B4-range"],
    run: (v, i) => (nB4(v) ? {} : { ["bk" + (i + 1)]: "B4" }), ok: (s) => nB4(s) <= 1 } };
function sim(sc, mode) {
  let com = { ...sc.init }, cur = { ...com }, clock = 0; const ver = {}, L = {}, note = [], done = [];
  const snap = [], start = [], plan = [{}, {}], st = ["run", "run"], q = [[], []];
  const lk = (k) => (L[k] ||= { S: new Set(), X: null }), log = (m) => note.includes(m) || note.push(m);
  const can = (i, k, x) => [null, i].includes(lk(k).X) && (!x || [...lk(k).S].every((j) => j === i));
  const free = (i) => Object.values(L).forEach((l) => { l.S.delete(i); if (l.X === i) l.X = null; });
  const abort = (i, why) => { st[i] = "aborted"; q[i] = []; free(i); log(`T${i + 1} aborted: ${why}`); };
  function step(i, op) { if (mode === "snapshot" && !snap[i]) [snap[i], start[i]] = [{ ...com }, clock];
    const ks = op === "r" ? sc.rk(i) : op === "w" && Object.keys(plan[i]).length ? sc.wk(i) : [];
    if (mode.includes("2PL")) { if (!ks.every((k) => can(i, k, op === "w"))) { log(`T${i + 1} waits`); return false; }
      ks.forEach((k) => (op === "w" ? (lk(k).X = i) : lk(k).S.add(i))); }         // hold until commit
    if (op === "r") plan[i] = sc.run(mode === "none" ? cur : mode === "snapshot" ? snap[i] : com, i);
    if (op === "w" && mode === "none") cur = { ...cur, ...plan[i] };          // visible at once
    if (op !== "c") return true; const keys = Object.keys(plan[i]);
    if (mode === "snapshot" && keys.some((k) => (ver[k] || 0) > start[i])) return abort(i, "first-committer-wins"), true;
    clock++; keys.forEach((k) => (ver[k] = clock)); com = { ...com, ...plan[i] };
    st[i] = "done"; if (keys.length) done.push(i); free(i); return true; }
  for (const s of SCHED) { const i = +s[0] - 1; if (st[i] === "run") q[i].push(s[1]);
    for (let moved = true; moved; ) { moved = false;
      for (const j of [0, 1]) while (q[j].length && step(j, q[j][0])) { q[j].shift(); moved = true; }
      if (q[0].length && q[1].length) { log("deadlock"); abort(1, "victim"); moved = true; } } }
  const fin = mode === "none" ? cur : com;
  return mode.padEnd(19) + JSON.stringify(fin).padEnd(26) + (sc.ok(fin, done) ? "OK     " : "BROKEN ") + note.join("; "); }
for (const [name, sc] of Object.entries(SC)) { console.log(`\n${name}   (schedule ${SCHED.join(" ")})`);
  for (const m of ["none", "read committed", "snapshot", "serializable (2PL)"]) console.log("  " + sim(sc, m)); }

wallet: lost update   (schedule 1r 2r 1w 1c 2w 2c)
  none               {"bal":400}               BROKEN
  read committed     {"bal":400}               BROKEN
  snapshot           {"bal":300}               OK     T2 aborted: first-committer-wins
  serializable (2PL) {"bal":300}               OK     T1 waits; T2 waits; deadlock; T2 aborted: victim

doctors: write skew   (schedule 1r 2r 1w 1c 2w 2c)
  none               {"alice":0,"bob":0}       BROKEN
  read committed     {"alice":0,"bob":0}       BROKEN
  snapshot           {"alice":0,"bob":0}       BROKEN
  serializable (2PL) {"alice":0,"bob":1}       OK     T1 waits; T2 waits; deadlock; T2 aborted: victim

seat B4: double booking   (schedule 1r 2r 1w 1c 2w 2c)
  none               {"bk1":"B4","bk2":"B4"}   BROKEN
  read committed     {"bk1":"B4","bk2":"B4"}   BROKEN
  snapshot           {"bk1":"B4","bk2":"B4"}   BROKEN
  serializable (2PL) {"bk1":"B4"}              OK     T1 waits; T2 waits; deadlock; T2 aborted: victim

৬ · অ্যাপ-স্তরের প্যাটার্ন — সংঘাতকে database-এর চোখের সামনে আনা

বাস্তবের বেশিরভাগ ওয়েব আর মোবাইল backend ডিফল্ট READ COMMITTED-এ চলে, আর বিপজ্জনক জায়গাগুলোতে আলাদা করে নিরাপত্তা যোগ করে। সব প্যাটার্নের পেছনে একটিই ভাবনা: সংঘাতকে এমন জায়গায় আনুন, যেখানে database নিজে তা দেখতে পায় — একই সারিতে, একই বাক্যে, বা একটি constraint-এ। নিচের লাইভ SQL চলে SQLite-এ, যেখানে লেখকেরা এমনিতেই লাইনে দাঁড়ায়; তাই এগুলো একসাথে চলা দেখায় না — দেখায় সঠিক বাক্যটি কেমন, আর প্রতিটির পরে কয়টি সারি বদলাল।

ক · শর্তসহ atomic update — পড়া, যাচাই আর লেখা এক বাক্যে

Lost update হয় কারণ অ্যাপ পড়ে, তারপর memory-তে হিসাব করে লেখে। Atomic update-এ হিসাবটি database-কেই দিন, আর শর্তটি WHERE-এ রাখুন। Database এক বাক্যের ভেতরে সারিটি lock করে সর্বশেষ মান দেখে, তাই দুজনের মাঝে কেউ ঢুকতে পারে না।

-- One bus trip with 2 seats left. CHECK is the last line of defence.
CREATE TABLE trips (trip_id INTEGER PRIMARY KEY,
                    seats_left INTEGER NOT NULL CHECK (seats_left >= 0));
INSERT INTO trips VALUES (7, 2);
-- WRONG: both servers read 2, computed 2 - 1 in the app, and wrote 1
UPDATE trips SET seats_left = 1 WHERE trip_id = 7;
UPDATE trips SET seats_left = 1 WHERE trip_id = 7;
SELECT seats_left AS after_naive FROM trips WHERE trip_id = 7;
UPDATE trips SET seats_left = 2 WHERE trip_id = 7;   -- reset
-- RIGHT: one atomic statement each, the guard inside WHERE
UPDATE trips SET seats_left = seats_left - 1 WHERE trip_id = 7 AND seats_left > 0;
UPDATE trips SET seats_left = seats_left - 1 WHERE trip_id = 7 AND seats_left > 0;
UPDATE trips SET seats_left = seats_left - 1 WHERE trip_id = 7 AND seats_left > 0;
SELECT seats_left AS after_atomic FROM trips WHERE trip_id = 7;

খ · SELECT … FOR UPDATE — যা পড়লেন, তাতেই lock

যখন সিদ্ধান্তটি এক বাক্যে আঁটে না — যেমন ব্যালান্স, দৈনিক সীমা আর ঝুঁকির নিয়ম মিলিয়ে দেখা — তখন পড়ার সময়েই সারিটি lock করুন। SELECT … FOR UPDATE সর্বশেষ commit হওয়া মান পড়ে আর সারিতে লেখার lock নেয়; অন্য কেউ একই সারি FOR UPDATE-এ পড়তে বা বদলাতে চাইলে অপেক্ষা করে। এটি pessimistic পথ — আগে থেকেই ধরে নেওয়া যে সংঘাত হবে।

BEGIN;
SELECT balance, daily_sent FROM wallets WHERE id = 42 FOR UPDATE;  -- row locked
-- app checks: balance >= 600 AND daily_sent + 600 <= limit, risk rules ...
UPDATE wallets SET balance = balance - 600, daily_sent = daily_sent + 600 WHERE id = 42;
INSERT INTO ledger (wallet_id, amount, request_id) VALUES (42, -600, 'req-b1');
COMMIT;                        -- the other withdrawal was waiting; now it sees the new row

তিনটি সতর্কতা। এক, lock commit পর্যন্ত থাকে, তাই ভেতরে network call বা ব্যবহারকারীর অপেক্ষা নয় (অংশ ৭)। দুই, কয়েকটি সারি lock করলে সবখানে একই ক্রমে — নইলে deadlock. তিন, FOR UPDATE শুধু বিদ্যমান সারি lock করে; “কোনো সারি নেই” দেখে নেওয়া সিদ্ধান্ত (খালি সিট) এতে রক্ষা পায় না। Job queue-তে PostgreSQL আর MySQL 8-এর FOR UPDATE SKIP LOCKED অন্যের ধরা সারি এড়িয়ে পরেরটি নেয় (পর্ব ৩৭)।

গ · Version কলাম — optimistic concurrency

উল্টো পথ: lock না নিয়ে সবাইকে পড়তে দিন, কিন্তু লেখার মুহূর্তে যাচাই করুন যে পড়ার পরে কেউ বদলায়নি। প্রতিটি সারিতে একটি version সংখ্যা থাকে; লেখা হয় শুধু তখনই, যখন version এখনো সেটিই। এটিই optimistic concurrency — একটি compare-and-set. পর্ব ৮-এ দেখেছেন API-র স্তরে একই কাজ ETag আর If-Match দিয়ে (412 উত্তর)। এখানে দেখুন ভেতরের database-এ, ধাপে ধাপে:

CREATE TABLE wallets (id INTEGER PRIMARY KEY,
                      balance INTEGER NOT NULL CHECK (balance >= 0),
                      version INTEGER NOT NULL);
INSERT INTO wallets VALUES (42, 1000, 3);
-- Server A and server B both read: balance 1000, version 3.
-- A withdraws 700 and writes only if the version is still 3:
UPDATE wallets SET balance = 300, version = version + 1 WHERE id = 42 AND version = 3;
-- B withdraws 600 using the same old read:
UPDATE wallets SET balance = 400, version = version + 1 WHERE id = 42 AND version = 3;
SELECT id, balance, version FROM wallets;
-- B re-reads (300, v4). A 200 withdrawal would now pass:
UPDATE wallets SET balance = 100, version = version + 1 WHERE id = 42 AND version = 4;
SELECT id, balance, version FROM wallets;

ঘ · UNIQUE constraint — নিয়মটি schema-তে লেখা

Phantom-ঘটিত double booking-এর সবচেয়ে নির্ভরযোগ্য সমাধান: নিয়মটি database-কে বলে দেওয়া। একই ট্রিপের একই সিট দুবার — এটি একটি unique constraint. Unique index-এ একই key দুবার ঢোকানো যায় না, যেকোনো isolation level-এ: দ্বিতীয় insert প্রথমটির ফলের অপেক্ষা করে, প্রথমটি commit করলে ত্রুটি পায়।

-- Without a constraint the database cannot know a seat is sold once.
CREATE TABLE bookings_naive (trip_id INTEGER, seat_no TEXT, passenger TEXT);
INSERT INTO bookings_naive VALUES (7, 'B4', 'Rahim');
INSERT INTO bookings_naive VALUES (7, 'B4', 'Karim');  -- both "saw" B4 free
SELECT trip_id, seat_no, COUNT(*) AS sold FROM bookings_naive GROUP BY trip_id, seat_no;
-- With the rule written into the schema:
CREATE TABLE bookings (id INTEGER PRIMARY KEY, trip_id INTEGER NOT NULL,
                       seat_no TEXT NOT NULL, passenger TEXT NOT NULL,
                       UNIQUE (trip_id, seat_no));
INSERT INTO bookings (trip_id, seat_no, passenger) VALUES (7, 'B4', 'Rahim');
INSERT INTO bookings (trip_id, seat_no, passenger) VALUES (7, 'B5', 'Karim');
INSERT INTO bookings (trip_id, seat_no, passenger) VALUES (8, 'B4', 'Sumi');
SELECT trip_id, seat_no, passenger FROM bookings ORDER BY trip_id, seat_no;
-- Karim tries B4 on trip 7 again (this error is the point):
INSERT INTO bookings (trip_id, seat_no, passenger) VALUES (7, 'B4', 'Karim');

ঙ · সংঘাতকে একটি সারিতে আনা — materializing the conflict

যখন নিয়মটি UNIQUE-এ লেখা যায় না, তখন এমন একটি সাধারণ সারি তৈরি করুন, যা সংঘাতে থাকা সবাইকে লিখতে হয়। একে বলে materializing the conflict. দুটি পরিচিত রূপ:

• আগে থেকে বানানো সিটের সারি: প্রতিটি ট্রিপ তৈরির সময়ই প্রতিটি সিটের একটি সারি — seats(trip_id, seat_no, status, held_until). Booking মানে UPDATE seats SET status = 'held' … WHERE trip_id = 7 AND seat_no = 'B4' AND status = 'free' — phantom এখন একটি সাধারণ বিদ্যমান সারির atomic update. “১০ মিনিট সিট ধরে রাখা, তারপর পেমেন্ট” এভাবেই হয় (পর্ব ৬০)।
• গণনার সারি: ডাক্তারদের shift-এর একটি সারি shifts(shift_id, on_call_count CHECK (on_call_count >= 1)). ছুটি নিতে হলে একই transaction-এ এটিও কমাতে হয়; দুজনের লেখা এখন একই সারিতে, তাই lock বা first-committer-wins সংঘাতটি দেখে। DBMS কোর্সে এটি লাইভ SQL-এ চালিয়ে দেখতে পারেন।

৭ · Transaction-এর পরিধি — ছোট রাখা, দীর্ঘ transaction-এর দাম আর retry

Level বাছাইয়ের মতোই জরুরি transaction-এর ভেতরে কী রাখবেন। মূল নিয়ম: transaction-এর ভেতরে শুধু database-এর কাজ, আর যত কম সময়। পেমেন্ট গেটওয়েতে call, SMS পাঠানো, অন্য সার্ভিসের API, বা ব্যবহারকারীর “নিশ্চিত করুন” চাপার অপেক্ষা — এগুলোর কোনোটিই নয়।

দীর্ঘ transaction কী কী ভাঙে

• Lock-এর লাইন: খোলা transaction-এর lock-এর পেছনে অন্যরা দাঁড়ায়; throughput নামে, অপেক্ষারত অনুরোধ connection pool ফুরিয়ে দেয় (পর্ব ১৮-এর ধসের চক্র)।
• MVCC-র ফোলা: সবচেয়ে পুরোনো খোলা snapshot যতক্ষণ আছে, তার দেখার মতো পুরোনো version মোছা যায় না। PostgreSQL-এ মৃত সারি জমে টেবিল আর index ফোলে, VACUUM কাজ করতে পারে না; InnoDB-তে undo-র ইতিহাস লম্বা হয় আর পুরোনো snapshot-এর পড়া ধীর হয়। একটি ভুলে-যাওয়া “idle in transaction” session — যে BEGIN করে আর কিছু করছে না — ঘণ্টার পর ঘণ্টা এটি ঘটাতে পারে।
• বেশি abort: যত বেশি সময় খোলা, তত বেশি সংঘাত — snapshot-এ abort, SSI-তে false positive.

প্রতিকার: সব প্রস্তুতি (validation, দাম হিসাব, বাইরের call) transaction-এর বাইরে, তারপর ছোট একটি transaction-এ শুধু পড়া-যাচাই-লেখা। বাইরের কাজ commit-এর পরে, নির্ভরযোগ্যভাবে — একই transaction-এ একটি outbox সারি লিখে পরে পাঠানো (পর্ব ২৫)। আর database-কে সীমা দিন: PostgreSQL-এ statement_timeout, lock_timeout আর idle_in_transaction_session_timeout-এর মতো সেটিং আছে (নাম আর ডিফল্ট সংস্করণভেদে দেখে নিন)।

Retry — পুরো transaction, সীমিত বার, idempotent ভাবে

Snapshot isolation, SSI বা deadlock — সবগুলোই শেষে অ্যাপকে একটি ত্রুটি দেয়: “আবার চেষ্টা করুন”। PostgreSQL-এ serialization failure-এর SQLSTATE 40001, deadlock 40P01; MySQL-এ deadlock 1213. সঠিক retry-র তিনটি শর্ত:

1. পুরো transaction আবার, শুরু থেকে। শুধু ব্যর্থ বাক্যটি নয় — কারণ আগের পড়াগুলোই পুরোনো হয়ে গেছে।
2. সীমিত বার, অপেক্ষা বাড়িয়ে। ৩ থেকে ৫ বার, exponential backoff আর jitter সহ, নইলে ব্যস্ত সারিতে retry নিজেই ভিড় বাড়ায় (পর্ব ১৮, পর্ব ৪৮)।
3. বাইরের প্রভাব idempotent. ভেতরে SMS বা গেটওয়ে-call থাকলে retry তা দুবার করবে; commit-এর মাঝে connection কাটলে ফল অজানা — UNIQUE কলামে একটি idempotency key দ্বিতীয় চেষ্টাকে চেনায় (পর্ব ৩৪; আর এই পর্বের ভাবনার প্রশ্ন ৫)।

for attempt in 1..4:
    try:
        BEGIN ISOLATION LEVEL SERIALIZABLE
        ... read, check, write (no network calls in here) ...
        INSERT INTO outbox (...)          -- SMS / webhook sent later, once
        COMMIT
        return OK
    except error with SQLSTATE 40001 or 40P01:
        ROLLBACK
        sleep(random(0, 20ms * 2^attempt))   -- backoff with jitter
    except connection lost during COMMIT:
        look up the idempotency key: did it commit? never guess
return "busy, please try again"

৮ · কোন কাজে কোন level — payment, analytics, counter আর booking

সব কাজ একই level-এ চালানোর দরকার নেই। প্রতিটি কাজের প্রশ্ন আলাদা: কোন anomaly এখানে ক্ষতি করে, আর তার দাম কে দেবে? সারণিটি শুরুর বিন্দু, চূড়ান্ত উত্তর নয়।

একটি database ছাড়িয়ে গেলে

এই পর্বের সব নিশ্চয়তা একটি database-এর ভেতরের। ডেটা কয়েকটি shard-এ ভাগ হলে (পর্ব ২৩), বা টাকা এক সার্ভিসে আর টিকিট আরেকটিতে থাকলে, একটি BEGIN … COMMIT দুই জায়গা ঢাকে না — তখন two-phase commit, saga বা outbox (পর্ব ২৫)। কপিগুলোর মাঝে “সবাই সর্বশেষ মান দেখবে কি” প্রশ্নটি consistency model-এর (পর্ব ৪০)। Database-এর বাইরে একটি কাজ একবারই চালাতে distributed lock আর fencing token (পর্ব ৪৩)। আর পুরো টিকিট বুকিং আর পেমেন্টের নকশা পর্ব ৬০-এ।

৯ · AI-এর যুগে transaction-এর নকশা যাচাই

“সিটে double booking হচ্ছে, কী করব?” — AI প্রায়ই বলে “Redis-এ distributed lock, Kafka-তে queue, microservice-এ ভাগ।” অথচ উত্তর প্রায়ই একটি লাইন: UNIQUE (trip_id, seat_no). আবার AI-এর কোড প্রায়ই BEGIN … COMMIT-এর ভেতরে SELECT করে, অ্যাপে হিসাব করে, তারপর UPDATE — READ COMMITTED-এ অংশ ২-এর lost update. সাধারণ ভুলগুলো:

AI-এর transaction-নকশা হাতে পেলে — যাচাইয়ের চেকলিস্ট

• প্রতিটি invariant-এর পাশে লিখেছি কে সেটি রক্ষা করে — constraint, atomic update, lock, না isolation level
• প্রতিটি “পড়ে, হিসাব করে, লেখা”-র জন্য দুটি transaction-এর একটি খারাপ interleaving হাতে লিখে দেখেছি
• নিজের সংস্করণের documentation মিলিয়েছি, আর দুই session-এ অন্তত একটি সংঘাত নিজে ঘটিয়েছি
• Transaction-এর ভেতরে কোনো network call বা ব্যবহারকারীর অপেক্ষা নেই; ব্যস্ত সারির জন্য 1 ÷ h সীমা নিজে হিসাব করেছি
• Retry পুরো transaction-এর, সীমিত বার, backoff-সহ; বাইরের প্রভাব idempotency key বা outbox দিয়ে একবারই
• Failure-mode পর্যালোচনা: deadlock, serialization failure, commit-এর মাঝে connection কাটা, দীর্ঘ transaction-এ MVCC-র ফোলা