MVCC আর isolation level — anomaly থেকে serializable

১ · একসাথে অনেকে — isolation কেন একটি দর-কষাকষি

পর্ব ৪৪-এ দেখেছেন, ACID-এর “I” মানে isolation: একসাথে চলা transaction-গুলো যেন একে অপরের অর্ধেক কাজ দেখে বিভ্রান্ত না হয়। পর্ব ৪৫-এ এর সবচেয়ে কড়া রূপের সংজ্ঞা পেয়েছেন — serializable: একসাথে চলার ফল এমন হতে হবে, যেন transaction-গুলো কোনো একটি ক্রমে একটির পর একটি চলেছে। আর পর্ব ৪৬-এ দেখেছেন, lock আর two-phase locking দিয়ে কীভাবে সেটি নিশ্চিত করা যায়।

পুরো serializable-এর দাম আছে। lock-ভিত্তিক ব্যবস্থায় পাঠক লেখকের জন্য, লেখক পাঠকের জন্য অপেক্ষা করে; একটি বড় রিপোর্ট-কোয়েরি পুরো টেবিল পড়লে সেই সময়ে কেউ লিখতে পারে না, আর deadlock-ও বাড়ে। তাই ১৯৭০-এর দশক থেকেই DBMS-গুলো কয়েকটি দুর্বল ধাপ দিয়েছে। প্রতিটি ধাপ কিছু নির্দিষ্ট ভুল ঠেকায় আর কিছু ঘটতে দেয়। এই ভুলগুলোর নাম anomaly (বা phenomenon) — এই পর্বের প্রথম কাজ এগুলো চেনা।

পুরো পর্বে একটি ছোট টেবিল ধরে নিন: acct(id, owner, bal) — অ্যাকাউন্ট নম্বর, মালিক আর ব্যালান্স (টাকা)। শুরুতে রহিমের (id 1) আর করিমের (id 2) প্রত্যেকের ১,০০০ টাকা। দুটি transaction-কে বলব T1 আর T2; timeline-এ সময় ওপর থেকে নিচে চলে।

২ · তিনটি ক্লাসিক read anomaly — dirty, non-repeatable আর phantom

SQL standard (SQL-92) তিনটি “ঘটনা” দিয়ে isolation level সংজ্ঞায়িত করেছে। নিচের ডায়াগ্রামে তিনটিই — প্রতিটি তিন ধাপে। বাঁ কলামে পাঠক T1, ডান কলামে লেখক T2. জায়গা বাঁচাতে SQL সংক্ষেপে: SELECT bal মানে SELECT bal FROM acct WHERE id = 1, অর্থাৎ রহিমের ব্যালান্স পড়া।

লক্ষ করুন, তিনটি ঘটনাই পড়ার সমস্যা, আর ক্রমশ সূক্ষ্ম: প্রথমটিতে মিথ্যা মান, দ্বিতীয়টিতে একটি সত্য মান বদলে যাওয়া, তৃতীয়টিতে শর্তের ফল বদলে যাওয়া। phantom ঠেকাতে lock-ভিত্তিক DBMS শুধু সারিতে নয়, শর্তে বা index-এর ফাঁকে (range) lock দেয় — পর্ব ৪৬-এর predicate lock বা MySQL-এর next-key lock, যা নিয়ে অংশ ৮-এ আবার আসব।

৩ · লেখার anomaly — lost update, read skew আর write skew

standard-এর তিনটি ঘটনার বাইরেও বাস্তবে আরও তিনটি ভুল প্রায়ই দেখা যায়। প্রথম দুটি নিচের ডায়াগ্রামে; উদাহরণে দোকানের একটি পণ্যের মজুত (stock) ৫টি, আর রহিম–করিমের যৌথ দুটি অ্যাকাউন্ট A আর B, প্রতিটিতে ৫০০ টাকা।

Lost update একটি সংযোগেই অনুকরণ করে দেখুন। এই এডিটর পাতার দোকান ডেটাবেস ব্যবহার করে — products(product_id, name, category_id, price, stock); product 110 হলো Smart Watch, মজুত ৫টি। দুই “সেশন”-এর অ্যাপ যা পড়ে মনে রাখে, তা রাখা হয়েছে একটি অস্থায়ী টেবিল app_mem-এ:

CREATE TEMP TABLE app_mem (session TEXT, seen INTEGER);
-- দুই সেশন একই মুহূর্তে মজুত পড়ে মনে রাখল
INSERT INTO app_mem SELECT 'S1', stock FROM products WHERE product_id = 110;
INSERT INTO app_mem SELECT 'S2', stock FROM products WHERE product_id = 110;
SELECT * FROM app_mem;
-- প্রত্যেকে নিজের দেখা মান থেকে ১ কমিয়ে লিখল
UPDATE products SET stock = (SELECT seen - 1 FROM app_mem WHERE session = 'S1')
WHERE product_id = 110;
UPDATE products SET stock = (SELECT seen - 1 FROM app_mem WHERE session = 'S2')
WHERE product_id = 110;
SELECT product_id, name, stock FROM products WHERE product_id = 110;

Write skew — দুজনেই নিয়ম মেনেছেন, তবু নিয়ম ভাঙল

ষষ্ঠ anomaly সবচেয়ে সূক্ষ্ম। একটি হাসপাতালের নিয়ম: রাতের shift-এ কমপক্ষে একজন ডাক্তার on call থাকবেন। ডা. অ্যালিস আর ডা. বব দুজনেই on call, দুজনেই অসুস্থ বোধ করছেন। প্রত্যেকে অ্যাপে “ছুটি নিন” চাপলেন; অ্যাপ প্রথমে গোনে কতজন on call, ২ হলে নিজের সারিতে on_call = 0 লেখে। দুটি transaction একসাথে চললে দুজনেই ২ দেখেন, দুজনেই নিজের আলাদা সারি বদলান — শেষে on call শূন্য। এটি write skew: দুজনে একই তথ্য পড়ে ভিন্ন ভিন্ন সারিতে লেখেন, আর দুই লেখা মিলে একটি নিয়ম ভাঙে যা কোনো একটি লেখা একা ভাঙত না।

একই গল্প ব্যাংকে: রহিম–করিমের যৌথ দুটি অ্যাকাউন্ট, নিয়ম “দুটির যোগফল কখনো ঋণাত্মক নয়”, প্রতিটিতে ৫০০. রহিম A থেকে ৯০০ আর করিম একই সময়ে B থেকে ৯০০ তুলতে চান। প্রত্যেকের transaction দেখে মোট ১,০০০ ≥ ৯০০, তাই অনুমতি দেয়। ফল A = −৪০০, B = −৪০০, মোট −৮০০. lost update-এর সাথে পার্থক্যটি মনে রাখুন: lost update-এ দুজন একই সারিতে লেখে; write skew-এ ভিন্ন সারিতে। এই পার্থক্যের কারণেই অংশ ৭-এর snapshot isolation প্রথমটি ঠেকায়, দ্বিতীয়টি পারে না।

৪ · SQL standard-এর চারটি level — আর তার সমালোচনা

SQL-92 standard চারটি isolation level দিয়েছে, আর প্রতিটির সংজ্ঞা দিয়েছে কোন ঘটনা ঘটতে পারবে না তা দিয়ে। স্টেটমেন্টটি সব DBMS-এ মোটামুটি এক:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;   -- পরের transaction-এর জন্য
-- PostgreSQL-এ BEGIN-এর সাথেই:
BEGIN ISOLATION LEVEL SERIALIZABLE;

টেবিলটি পরীক্ষায় সবচেয়ে বেশি আসে — মুখস্থ রাখুন। standard অনুযায়ী ডিফল্ট level SERIALIZABLE, যদিও বাস্তবের প্রায় কোনো বড় DBMS-এর ডিফল্ট সেটি নয় (অংশ ৮)। standard আরও বলে, SERIALIZABLE মানে শুধু তিনটি ঘটনার অনুপস্থিতি নয় — ফল কোনো serial ক্রমের সমান হতে হবে।

সমালোচনার পর বিস্তৃত তালিকাটি দাঁড়ায় এরকম (lock-ভিত্তিক REPEATABLE READ ধরে):

* একই শর্তে আবার পড়লে snapshot isolation নতুন সারি দেখায় না, তাই standard-এর অর্থে phantom নেই; কিন্তু phantom-ঘটিত write skew (যেমন “এই সময়ে কোনো booking নেই” দেখে দুজনেই নতুন সারি ঢোকানো) ঘটতে পারে। Berenson ও সহকর্মীরা দেখান, snapshot isolation আর lock-ভিত্তিক REPEATABLE READ একে অপরের চেয়ে কড়া নয় — প্রতিটি এমন কিছু ঠেকায় যা অন্যটি দেয়। পরে Atul Adya-র ১৯৯৯ সালের PhD গবেষণা isolation-কে lock বা প্রয়োগ-পদ্ধতি থেকে আলাদা করে, শুধু transaction-গুলোর পারস্পরিক নির্ভরতার গ্রাফ দিয়ে সংজ্ঞায়িত করে — আধুনিক আলোচনার ভাষা অনেকটাই সেখান থেকে।

৫ · MVCC — একটি সারির অনেক version

lock-ভিত্তিক ব্যবস্থার বড় কষ্ট: পাঠক আর লেখক একে অপরকে আটকায়। MVCC (multi-version concurrency control) সমাধান দেয় একটি সরল ভাবনায়: সারি বদলালে পুরোনোটি মোছা নয় — নতুন একটি version বানানো। তাহলে যে পাঠক আগে শুরু করেছে, সে পুরোনো version পড়তে থাকে; লেখক নতুনটি লেখে; কেউ কারও জন্য দাঁড়ায় না। দুই লেখক একই সারিতে লিখতে চাইলে অবশ্য একজনকে অপেক্ষা করতে হয় — MVCC লেখা-লেখার সংঘাত দূর করে না।

Snapshot-এর সংজ্ঞা: একটি নির্দিষ্ট মুহূর্তে যে transaction-গুলো commit হয়ে গেছে, তাদের সেট। নিয়ম: একটি transaction শুধু সেই version-গুলো দেখে, যা তার snapshot-এর কোনো transaction লিখেছে (সাথে নিজের লেখা), আর যা snapshot-এর কোনো transaction মুছে বা বদলে ফেলেনি। snapshot কখন নেওয়া হয়, তা-ই level ঠিক করে: READ COMMITTED-এ প্রতিটি স্টেটমেন্টের শুরুতে নতুন snapshot, আর REPEATABLE READ বা snapshot isolation-এ পুরো transaction-এ একটিই।

PostgreSQL: xmin, xmax আর visibility

PostgreSQL প্রতিটি transaction-কে একটি ক্রমবর্ধমান নম্বর দেয় — transaction ID বা xid. টেবিলের প্রতিটি version-এ (PostgreSQL-এর ভাষায় tuple) দুটি লুকানো কলাম থাকে: xmin — কোন xid এই version তৈরি করেছে, আর xmax — কোন xid একে মুছেছে বা নতুন version দিয়ে বদলেছে (না হলে খালি)। UPDATE আসলে “পুরোনোটির xmax-এ নিজের xid লেখো + নতুন version ঢোকাও, xmin = নিজের xid”. আর commit বা abort-এর খবর থাকে আলাদা একটি commit log-এ (pg_xact). Visibility-র নিয়ম, সরল রূপে:

এবার নিয়মটি নিজের হাতে SQL-এ চালান। তিনটি ছোট টেবিল মডেল করে: acct_v(id, bal, xmin, xmax) — সব version; tx(xid, status) — commit log (100, 101, 103 committed; 102 running; 104 aborted); আর snap(snap, xid) — প্রতিটি snapshot-এ কোন xid আছে। S1 = {100}, S2 = {100, 101}, S3 = {100, 101, 103}, আর T102 হলো চলমান transaction 102-এর নিজের দৃষ্টি = {100, 101} + নিজে। xid 103 সুমনের নতুন অ্যাকাউন্ট (id 3) খুলেছে, আর 104 করিমের ব্যালান্স ০ করতে গিয়ে abort হয়েছে:

SELECT * FROM acct_v ORDER BY id, xmin;
-- প্রতিটি snapshot কোন version দেখে (visibility-র নিয়ম)
SELECT s.snap,
       GROUP_CONCAT(v.id || ':' || v.bal, '  ') AS visible_rows
FROM (SELECT DISTINCT snap FROM snap) s
JOIN acct_v v
  ON v.xmin IN (SELECT xid FROM snap WHERE snap = s.snap)
 AND (v.xmax IS NULL
      OR v.xmax NOT IN (SELECT xid FROM snap WHERE snap = s.snap))
GROUP BY s.snap ORDER BY s.snap;

VACUUM, dead tuple আর xid wraparound

পুরোনো version জমতে থাকে; কেউ না সরালে টেবিল ফুলে যায় (bloat), আর scan-কে অকারণে মৃত সারি পেরোতে হয়। PostgreSQL-এ VACUUM সেই version সরায় যা আর কোনো চলমান snapshot দেখতে পারে না; সাধারণত autovacuum পেছনে নিজে চালায়। পর্ব ৩৯-এ দেখা VACUUM-এর এটিই কারণ। একই মডেলে দেখুন কোন version মোছা যায়:

-- S1 (সবচেয়ে পুরোনো snapshot) শেষ হলো
DELETE FROM snap WHERE snap = 'S1';
SELECT v.id, v.bal, v.xmin, v.xmax,
  CASE
    WHEN v.xmin IN (SELECT xid FROM tx WHERE status = 'aborted') THEN 'dead'
    WHEN v.xmax IS NOT NULL AND NOT EXISTS (
         SELECT 1 FROM (SELECT DISTINCT snap FROM snap) s
         WHERE v.xmax NOT IN (SELECT xid FROM snap WHERE snap = s.snap))
      THEN 'dead'
    ELSE 'needed'
  END AS vacuum_says
FROM acct_v v ORDER BY v.id, v.xmin;

আরেকটি সূক্ষ্ম কাজও VACUUM-এর। PostgreSQL-এর xid ৩২-বিটের, তাই প্রায় ৪০০ কোটি নম্বরের পর আবার ঘুরে আসে; তুলনা হয় বৃত্তাকারে — প্রতিটি xid-এর “অতীত” প্রায় ২০০ কোটি, “ভবিষ্যৎ” প্রায় ২০০ কোটি। খুব পুরোনো version-কে সময়মতো freeze (সবার কাছে চিরকাল দৃশ্যমান বলে চিহ্নিত) না করলে একদিন তার xmin হঠাৎ “ভবিষ্যতের” মনে হবে আর সারিটি অদৃশ্য হবে — একে বলে transaction ID wraparound. এটি ঠেকাতে autovacuum নিয়মিত freeze করে, আর বিপদসীমার কাছে গেলে PostgreSQL নতুন xid দেওয়াই বন্ধ করে দেয়। ব্যস্ত ডেটাবেসে autovacuum বন্ধ রাখা তাই বিপজ্জনক।

৬ · InnoDB, Oracle আর SQLite — অন্য পথে একই লক্ষ্য

PostgreSQL নতুন version টেবিলেই নতুন জায়গায় লেখে, পুরোনোটি সেখানেই থাকে। MySQL-এর InnoDB আর Oracle উল্টো পথ নেয়: সারি জায়গাতেই বদলায় (in-place), আর পুরোনো মান যায় একটি আলাদা জায়গায়।

দুই নকশার দাম আলাদা জায়গায়। PostgreSQL-এ পড়া সরল — দরকারি version টেবিলেই — কিন্তু প্রতিটি UPDATE একটি নতুন tuple আর সাধারণত প্রতিটি index-এ নতুন entry (PostgreSQL-এর HOT update কিছু ক্ষেত্রে এড়ায়), আর VACUUM-এর কাজ। InnoDB-তে নতুন মান জায়গায়, তাই টেবিল কম ফোলে, কিন্তু পুরোনো snapshot-এর পাঠককে undo-শিকল বেয়ে পেছনে যেতে হয়; একটি দীর্ঘ transaction purge আটকালে undo জমে (MySQL-এ “history list length” বাড়ে) আর সেই শিকল লম্বা হয়। লক্ষ্য একই, ঝামেলা ভিন্ন।

পাতার SQL রানার একটিই সংযোগ, তাই দুই সেশন দেখাতে পারে না। নিচের Python প্রোগ্রাম একটি অস্থায়ী ফাইলে SQLite ডেটাবেস খোলে WAL mode-এ, আর দুটি আলাদা সংযোগ (দুই সেশন) দিয়ে একই timeline চালায়। isolation_level=None মানে BEGIN আর COMMIT আমরা নিজে লিখব; timeout=0 মানে lock পেলে অপেক্ষা না করে সাথে সাথে ত্রুটি:

import sqlite3, tempfile, os

path = os.path.join(tempfile.mkdtemp(), "bank.db")
db = sqlite3.connect(path)
print("journal mode:", db.execute("PRAGMA journal_mode=WAL").fetchone()[0])
db.execute("CREATE TABLE acct (id INTEGER PRIMARY KEY, owner TEXT, bal INTEGER)")
db.executemany("INSERT INTO acct VALUES (?, ?, ?)", [(1, "Rahim", 1000), (2, "Karim", 1000)])
db.commit(); db.close()

t1 = sqlite3.connect(path, isolation_level=None, timeout=0)   # session 1
t2 = sqlite3.connect(path, isolation_level=None, timeout=0)   # session 2
bal = lambda c: c.execute("SELECT bal FROM acct WHERE id = 1").fetchone()[0]

t1.execute("BEGIN")
print("T1 first read     :", bal(t1))      # snapshot starts here
t2.execute("BEGIN")
t2.execute("UPDATE acct SET bal = bal - 300 WHERE id = 1")
print("T1 while T2 open  :", bal(t1))      # no dirty read
t2.execute("COMMIT")
print("T1 after T2 commit:", bal(t1))      # same snapshot, same value
print("T2 after commit   :", bal(t2))
try:
    t1.execute("UPDATE acct SET bal = bal + 50 WHERE id = 1")
except sqlite3.OperationalError as e:
    print("T1 write fails    :", e.sqlite_errorname)
t1.execute("ROLLBACK")
print("T1 new transaction:", bal(t1))

৭ · Snapshot isolation-এর গ্যারান্টি, write skew আর SSI

Snapshot isolation (SI)-এর দুই নিয়ম: (১) প্রতিটি transaction তার শুরুর snapshot থেকে পড়ে; (২) দুটি একসাথে চলা transaction একই সারিতে লিখলে শুধু একটি commit করতে পারে। দ্বিতীয় নিয়মের দুই রূপ: Berenson-এর first-committer-wins — যে আগে commit করে সে জেতে; আর PostgreSQL-এর বাস্তব রূপ first-updater-wins — দ্বিতীয় লেখক প্রথমজনের row lock-এর জন্য অপেক্ষা করে, প্রথমজন commit করলে দ্বিতীয়জন ত্রুটি পায় could not serialize access due to concurrent update.

কেন SI প্রথম পাঁচটি anomaly ঠেকায় — সংক্ষিপ্ত যুক্তি। snapshot একটি স্থির সেট: শুধু শুরুর আগে commit হওয়া transaction-গুলো। প্রতিটি পড়া সেই সেটের লেখা version ফেরত দেয়। তাই (ক) খসড়া কখনো সেটে নেই — dirty read নেই; (খ) একই সারি বা একই শর্ত বারবার পড়লে একই সেট থেকে একই উত্তর — non-repeatable read আর snapshot-পড়ায় phantom নেই; (গ) দুটি সম্পর্কিত সারি একই মুহূর্ত থেকে পড়া — read skew নেই; (ঘ) lost update-এ দুজনেই একই সারিতে লেখে, যা নিয়ম (২) অনুযায়ী অসম্ভব — একজন বাতিল হয়।

কেন write skew ফসকে যায় — প্রমাণ। ডাক্তারদের উদাহরণে T1 (অ্যালিস) পড়ে দুই সারি, লেখে শুধু অ্যালিসের সারি; T2 (বব) পড়ে দুই সারি, লেখে শুধু ববের সারি। লেখার সেট আলাদা, তাই নিয়ম (২) কিছু বলে না — দুজনেই commit. এখন পর্ব ৪৫-এর precedence graph আঁকুন। T1 ববের সারির পুরোনো version পড়েছে, যা T2 পরে বদলেছে: তাই কোনো সমতুল্য serial ক্রমে T1-কে T2-এর আগে থাকতে হবে (T1 → T2). একইভাবে T2 অ্যালিসের পুরোনো সারি পড়েছে যা T1 বদলেছে: T2 → T1. গ্রাফে চক্র, তাই কোনো serial ক্রম এই ফল দিতে পারে না — schedule-টি serializable নয়। এই ধরনের প্রান্ত, “একজন যা পড়েছে, অন্যজন পরে তার নতুন version লিখেছে”, হলো rw-antidependency. ২০০৫ সালে Alan Fekete ও সহকর্মীরা প্রমাণ করেন: SI-তে প্রতিটি অ-serializable চক্রে পরপর দুটি rw-antidependency প্রান্ত থাকে, যাদের দুই প্রান্তের transaction একসাথে চলেছে — একে বলে “dangerous structure”.

এই উপপাদ্যই serializable snapshot isolation (SSI)-এর ভিত্তি। Michael Cahill, Uwe Röhm আর Alan Fekete ২০০৮ সালে SIGMOD-এ দেখান: SI চালিয়ে যান, শুধু চলমান transaction-গুলোর rw-dependency নজরে রাখুন; কোনো transaction-এ ঢোকা আর বেরোনো দুটি rw-প্রান্ত দেখা দিলে একটি transaction বাতিল করুন। কোনো অপেক্ষা নেই, পাঠক এখনো লেখককে আটকায় না। PostgreSQL 9.1 (২০১১) থেকে SERIALIZABLE level এভাবেই চলে — Kevin Grittner আর Dan Ports-এর কাজ। পড়া ট্র্যাক করতে PostgreSQL একটি বিশেষ, আটকায় না এমন predicate lock নেয় (pg_locks-এ mode SIReadLock).

SQLite-এ একই নাটক দেখুন, retry loop-সহ। SQLite-এ একসাথে একজনই লেখক, আর পুরোনো snapshot থেকে লেখা নিষিদ্ধ — তাই write skew এখানে অসম্ভব, শুধু কে আগে লিখল তার ওপর নির্ভর করে কোনজন retry করবে:

import sqlite3, tempfile, os

path = os.path.join(tempfile.mkdtemp(), "hospital.db")
db = sqlite3.connect(path)
db.execute("PRAGMA journal_mode=WAL")
db.execute("CREATE TABLE doctors (name TEXT PRIMARY KEY, on_call INTEGER)")
db.executemany("INSERT INTO doctors VALUES (?, 1)", [("Alice",), ("Bob",)])
db.commit(); db.close()

alice = sqlite3.connect(path, isolation_level=None, timeout=0)
bob = sqlite3.connect(path, isolation_level=None, timeout=0)
count = lambda c: c.execute("SELECT COUNT(*) FROM doctors WHERE on_call = 1").fetchone()[0]
leave = lambda c, who: c.execute("UPDATE doctors SET on_call = 0 WHERE name = ?", (who,))

alice.execute("BEGIN"); bob.execute("BEGIN")
print("Alice sees on call:", count(alice), "| Bob sees:", count(bob))
leave(alice, "Alice"); alice.execute("COMMIT")
try:
    leave(bob, "Bob")
except sqlite3.OperationalError as e:
    print("Bob's UPDATE:", e.sqlite_errorname)
    bob.execute("ROLLBACK")

def go_off_duty(con, who, tries=3):          # the retry loop
    for attempt in range(1, tries + 1):
        try:
            con.execute("BEGIN")
            n = count(con)
            if n < 2:
                con.execute("ROLLBACK")
                return f"attempt {attempt}: refused, {n} on call"
            leave(con, who); con.execute("COMMIT")
            return f"attempt {attempt}: off duty"
        except sqlite3.OperationalError:
            con.execute("ROLLBACK")
    return "gave up"

print("Bob retries ->", go_off_duty(bob, "Bob"))
print("final:", alice.execute("SELECT * FROM doctors ORDER BY name").fetchall())

৮ · কোন DBMS আসলে কী দেয়

একই নামের level ভিন্ন DBMS-এ ভিন্ন জিনিস। নিচের টেবিল প্রচলিত সংস্করণের নথিভুক্ত আচরণের সারসংক্ষেপ — সংস্করণ আর সেটিং অনুযায়ী খুঁটিনাটি বদলায়, তাই নিজের DBMS-এর documentation মিলিয়ে নিন।

দুটি পরিণতি মনে রাখুন। এক, একটি অ্যাপ MySQL থেকে PostgreSQL-এ (বা উল্টো) সরালে একই REPEATABLE READ লেখা থাকলেও আচরণ বদলায় — PostgreSQL ত্রুটি দিয়ে retry চাইবে যেখানে MySQL নীরবে সর্বশেষ মানে লিখত। দুই, Oracle-এ বা পুরোনো PostgreSQL-এ (9.1-এর আগে) “SERIALIZABLE” লিখে যাঁরা নিশ্চিন্ত ছিলেন, তাঁরা আসলে snapshot isolation পেতেন — write skew সহ।

৯ · বাস্তবে level বাছাই আর অ্যাপের প্যাটার্ন

বাস্তবের বেশিরভাগ ওয়েব আর মোবাইল অ্যাপ ডিফল্ট READ COMMITTED-এ চলে, আর নির্দিষ্ট জায়গায় নিরাপত্তা যোগ করে। মূল কৌশল: সংঘাতকে এমন জায়গায় আনুন, যেখানে ডেটাবেস নিজে তা দেখতে পায় — একই সারিতে, বা একটি constraint-এ। চারটি প্যাটার্ন:

১. Atomic UPDATE — পড়া আর লেখা এক স্টেটমেন্টে

অংশ ৩-এর lost update-এর প্রতিকার: অ্যাপে হিসাব না করে ডেটাবেসকেই হিসাব করতে দিন, আর শর্তটি WHERE-এ রাখুন। একই Smart Watch (মজুত ৫); সাথে Power Bank (103), যার মজুত ০:

-- দুটি আলাদা বিক্রি, প্রতিটি ১টি করে
UPDATE products SET stock = stock - 1 WHERE product_id = 110 AND stock >= 1;
UPDATE products SET stock = stock - 1 WHERE product_id = 110 AND stock >= 1;
SELECT name, stock FROM products WHERE product_id = 110;
-- ৩টির অর্ডার, তারপর আরও ১টি
UPDATE products SET stock = stock - 3 WHERE product_id = 110 AND stock >= 3;
UPDATE products SET stock = stock - 1 WHERE product_id = 110 AND stock >= 1;
-- মজুত নেই এমন পণ্য
UPDATE products SET stock = stock - 1 WHERE product_id = 103 AND stock >= 1;
SELECT product_id, name, stock FROM products WHERE product_id IN (103, 110);

২. UNIQUE constraint — দুবার booking অসম্ভব

ট্রেনের আসন: “আসনটি খালি কি না দেখো, তারপর INSERT” — দুই transaction একসাথে খালি দেখে দুজনেই ঢোকাতে পারে (phantom-ঘটিত write skew). প্রতিকার: নিয়মটি schema-তে লিখুন। seat_booking(trip_id, seat_no, passenger)-এ (trip_id, seat_no) primary key:

INSERT INTO seat_booking VALUES (501, 'B4', 'Rahim');
-- করিমও B4 চান: নীরবে বাদ, অ্যাপ 0 rows দেখে অন্য আসন দেখাবে
INSERT INTO seat_booking VALUES (501, 'B4', 'Karim') ON CONFLICT DO NOTHING;
INSERT INTO seat_booking VALUES (501, 'B5', 'Karim') ON CONFLICT DO NOTHING;
SELECT * FROM seat_booking ORDER BY seat_no;
-- ON CONFLICT ছাড়া: ত্রুটি
INSERT INTO seat_booking VALUES (501, 'B4', 'Sumon');

৩. সংঘাতকে একটি সারিতে আনা — ডাক্তারদের shift

write skew-এর মূল সমস্যা: দুজন ভিন্ন সারিতে লেখেন। যদি দুজনকেই একটি সাধারণ সারিতে লিখতে হয়, সংঘাত দৃশ্যমান হয় — একে বলে materializing the conflict. shift(shift_id, on_call_count) টেবিলে on call-এর সংখ্যা রাখা, সাথে CHECK (on_call_count >= 1); ছুটি নিতে হলে একই transaction-এ এই সারিও কমাতে হয়:

-- অ্যালিসের ছুটি
BEGIN;
UPDATE shift SET on_call_count = on_call_count - 1 WHERE shift_id = 1;
UPDATE doctors SET on_call = 0 WHERE name = 'Alice';
COMMIT;
SELECT d.name, d.on_call, s.on_call_count FROM doctors d JOIN shift s USING (shift_id);
-- ববের ছুটি
BEGIN;
UPDATE shift SET on_call_count = on_call_count - 1 WHERE shift_id = 1;
UPDATE doctors SET on_call = 0 WHERE name = 'Bob';
COMMIT;

৪. SELECT … FOR UPDATE — যা পড়লেন তাতে lock

যখন নিয়মটি constraint-এ লেখা কঠিন, তখন যে সারিগুলোর ওপর সিদ্ধান্ত নিচ্ছেন সেগুলো পড়ার সময়েই lock নিন। SELECT … FOR UPDATE সর্বশেষ commit হওয়া version পড়ে আর সেই সারিগুলোতে row lock নেয়; অন্য কেউ একই সারি FOR UPDATE-এ পড়তে বা বদলাতে চাইলে অপেক্ষা করে। SQLite-এ এই সিনট্যাক্স নেই (সেখানে পুরো ডেটাবেস-স্তরের lock), তাই তুলনার জন্য:

BEGIN;
SELECT name FROM doctors
WHERE shift_id = 1 AND on_call = 1
FOR UPDATE;                 -- দুই ডাক্তারের সারিই lock
-- অ্যাপ গুনল: ২ জন, তাই অনুমতি
UPDATE doctors SET on_call = 0 WHERE name = 'Alice';
COMMIT;                     -- বব এতক্ষণ অপেক্ষায় ছিলেন; এখন ১ জন দেখবেন

সতর্কতা: FOR UPDATE শুধু বিদ্যমান সারি lock করে। “কোনো সারি নেই” দেখে সিদ্ধান্ত (যেমন খালি আসন বা ফাঁকা সময়) এতে রক্ষা পায় না — সেখানে UNIQUE বা exclusion constraint, বা SERIALIZABLE লাগে। MySQL-এর next-key lock index-এর ফাঁকেও lock দিয়ে এই phantom-এর একটা অংশ ঠেকায়, তবে শুধু সঠিক index থাকলে।

১০ · AI-এর যুগে isolation আর concurrency

AI-কে “একটি টাকা পাঠানোর API লেখো” বললে প্রায়ই সুন্দর, চালানো যায় এমন কোড আসে — যা একজন ব্যবহারকারীর পরীক্ষায় নিখুঁত, আর একসাথে একশো অনুরোধে ভাঙে। concurrency-র ভুল একক পরীক্ষায় ধরা পড়ে না। সাধারণ ভুল:

AI-এর দেওয়া transaction কোড চালু করার আগে — চেকলিস্ট

• প্রতিটি “পড়ে তারপর লেখা” জায়গা খুঁজেছি; সেখানে atomic UPDATE, FOR UPDATE বা constraint আছে
• ব্যবসায়িক নিয়ম (মজুত ≥ ০, এক আসনে একজন) schema-তে CHECK/UNIQUE হিসেবে লেখা আছে, শুধু অ্যাপে নয়
• isolation level স্পষ্টভাবে লেখা, আর আমার DBMS-এ সেই level আসলে কী করে তা নথিতে মিলিয়েছি
• serialization failure আর deadlock-এ পুরো transaction retry হয়, সীমিতবার, idempotent ভাবে
• দুই বা বেশি সংযোগ দিয়ে সমান্তরাল পরীক্ষা চালিয়ে মোট যোগফল বা সারির সংখ্যা মিলিয়েছি
• BEGIN আর COMMIT-এর মাঝে কোনো নেটওয়ার্ক কল বা ব্যবহারকারীর অপেক্ষা নেই