Concurrency control — lock, 2PL, deadlock আর timestamp

১ · সমস্যা — শেষ সিটটি দুজনের হাতে

পর্ব ৪৪-এ দেখেছেন, ACID-এর “I” বা isolation বলে: একসাথে চলা transaction-গুলোর ফল এমন হবে, যেন তারা একে একে চলেছে। পর্ব ৪৫-এ (Schedule আর serializability) শিখেছেন কোন মিশ্র ক্রম বা schedule “ঠিক” — যেটি conflict serializable. কিন্তু DBMS কীভাবে নিশ্চিত করে যে শুধু ঠিক schedule-ই ঘটবে? সেই নিয়মের নাম concurrency control, আর এই পর্ব তার প্রধান পদ্ধতিগুলো নিয়ে।

সমস্যার মূলে “পড়ো, হিসাব করো, লেখো” (read-modify-write) ধাঁচ — পড়া আর লেখার মাঝে অন্য কেউ ঢুকে পড়ে, একে বলে race condition. পাতার SQLite-এ একটিই সংযোগ, তাই সত্যিকারের দুই transaction চালানো যায় না; নিচের এডিটরে দুই কর্মীর “মনে রাখা” মানটি একটি ছোট টেবিল seen-এ রেখে একই ঘটনা ঘটানো হয়েছে। train টেবিলে একটি ট্রেন (id, name, free_seats), আর ticket-এ বিক্রি হওয়া টিকিট (id, train_id, clerk).

-- দুই কর্মী একই মুহূর্তে পড়লেন
INSERT INTO seen SELECT 'A', free_seats FROM train WHERE id = 1;
INSERT INTO seen SELECT 'B', free_seats FROM train WHERE id = 1;
-- দুজনেই দেখলেন 1 > 0: টিকিট কাটলেন, (পড়া মান − 1) লিখলেন
INSERT INTO ticket (train_id, clerk) SELECT 1, clerk FROM seen WHERE v > 0;
UPDATE train SET free_seats = (SELECT v - 1 FROM seen WHERE clerk = 'A');
UPDATE train SET free_seats = (SELECT v - 1 FROM seen WHERE clerk = 'B');
SELECT free_seats, (SELECT COUNT(*) FROM ticket) AS tickets FROM train;

-- প্রতিকার: পড়া, যাচাই আর লেখা একটি বাক্যে
UPDATE train SET free_seats = 1; DELETE FROM ticket;
UPDATE train SET free_seats = free_seats - 1 WHERE id = 1 AND free_seats > 0;
UPDATE train SET free_seats = free_seats - 1 WHERE id = 1 AND free_seats > 0;
SELECT free_seats FROM train;

ব্যবস্থাগুলো তিন পরিবারের। Pessimistic: সংঘাত হবে ধরে নিয়ে আগেই lock (অংশ ২–৭)। Timestamp: প্রতিটি transaction-এর একটি জন্মক্রম, আর সেই ক্রম ভাঙলেই বাতিল। Optimistic: সংঘাত বিরল ধরে নিয়ে নির্বিঘ্নে কাজ, শেষে যাচাই (অংশ ৮)। চতুর্থ পরিবার MVCC — একই ডেটার কয়েকটি সংস্করণ রাখা — পর্ব ৪৭-এর বিষয়।

২ · Lock — shared আর exclusive, আর lock manager

একটি lock হলো কোনো ডেটা-অংশের (সারি, page বা টেবিল) ওপর একটি transaction-এর সাময়িক অধিকার। মৌলিক দুই রকম:

কোন lock কোনটির পাশে চলে, তা একটি compatibility matrix-এ লেখা থাকে:

এই হিসাব রাখে DBMS-এর lock manager. তার মূল তথ্য-কাঠামো lock table: একটি hash table (DSA কোর্সের hash table পর্ব), যার key হলো ডেটা-অংশের পরিচয়, আর মান একটি queue — অনুরোধগুলো আসার ক্রমে, প্রতিটিতে লেখা কোন transaction, কোন mode, মঞ্জুর (granted) না অপেক্ষায় (waiting). নিয়ম তিনটি:

• নতুন অনুরোধ মঞ্জুর হয় যদি সে queue-তে থাকা সব granted lock-এর সাথে compatible হয় এবং তার আগে কেউ অপেক্ষায় না থাকে; নইলে queue-র শেষে দাঁড়ায়।
• Unlock হলে অনুরোধটি মুছে queue-র মাথা থেকে যতগুলো সম্ভব অপেক্ষমাণকে মঞ্জুর করা হয়।
• Abort হলে সেই transaction-এর সব অনুরোধ মুছে যায় — তাই প্রতিটি transaction-এর lock-গুলোর একটি তালিকাও রাখা হয়।

প্রথম নিয়মের “আগে কেউ অপেক্ষায় নেই” অংশটি গুরুত্বপূর্ণ। ধরুন একটি সিটে কয়েকজন পড়ছে (S), একজন লিখতে চায় (X, অপেক্ষায়)। নতুন পাঠকদের সরাসরি S দিলে পাঠকের স্রোত কখনো থামবে না, আর লেখক অনন্তকাল দাঁড়িয়ে থাকবে — এর নাম starvation. queue-র ক্রম মানলে তা হয় না। নিচের এডিটরে lock table আর matrix দুটিই সাধারণ টেবিল:

-- প্রতিটি item-এর queue, আসার ক্রমে
SELECT item, txn, mode, status FROM lock_table ORDER BY item, seq;
-- নতুন অনুরোধ: T5 চায় seat_9-এ S. কোনো granted lock-এর সাথে সংঘাত?
SELECT CASE WHEN EXISTS (
         SELECT 1 FROM lock_table l JOIN compat c ON c.held = l.mode AND c.req = 'S'
         WHERE l.item = 'seat_9' AND l.status = 'granted' AND c.ok = 0)
       THEN 'wait' ELSE 'grant' END AS t5_decision;
-- T1 আর T2 COMMIT করল: তাদের lock মুছে queue-র মাথাকে মঞ্জুর
DELETE FROM lock_table WHERE txn IN ('T1', 'T2');
UPDATE lock_table SET status = 'granted' WHERE item = 'seat_7' AND txn = 'T3';
SELECT item, txn, mode, status FROM lock_table ORDER BY item, seq;

৩ · শুধু lock যথেষ্ট নয় — two-phase locking

প্রতিটি পড়া-লেখার আগে lock নিলেই কি সব ঠিক? না। কাজ শেষ হওয়ামাত্র lock ছেড়ে দিলে অন্য transaction মাঝপথের অবস্থা দেখে ফেলে। উদাহরণ: হিসাব A-তে ১,০০০ আর B-তে ২,০০০ টাকা। T1 A থেকে B-তে ১০০ টাকা পাঠায়; T2 দুই হিসাবের যোগফল দেখায় — সঠিক উত্তর সবসময় ৩,০০০.

Two-phase locking (2PL) প্রতিটি transaction-কে দুই পর্বে ভাগ করে। Growing phase-এ সে শুধু lock নিতে পারে, ছাড়তে পারে না। প্রথমবার কোনো lock ছাড়ামাত্র শুরু হয় shrinking phase — তখন শুধু ছাড়া, নতুন lock আর নয়। দুই পর্বের সীমানা, অর্থাৎ শেষ lock পাওয়ার মুহূর্ত, হলো lock point.

দুটি সতর্কতা। প্রথমত, 2PL যথেষ্ট শর্ত, প্রয়োজনীয় নয়: কিছু conflict serializable schedule 2PL দিয়ে তৈরি হয় না। দ্বিতীয়ত, 2PL deadlock ঠেকায় না, আর মূল রূপে cascading abort-ও ঠেকায় না — পরের অংশগুলো এই দুই ফাঁক নিয়ে।

৪ · Strict আর rigorous 2PL — cascading abort ঠেকানো

মূল 2PL-এ shrinking phase-এ X lock ছেড়ে দেওয়া যায় COMMIT-এর আগেই। বিপদটি দেখুন: T1 A-তে লিখে lock ছাড়ল; T2 সেই অপাকা মান পড়ল; তারপর T1 কোনো কারণে abort করল। T2 যে মান পড়েছে তা আর কখনো ছিলই না — তাই T2-কেও abort করতে হবে, আর T2-র লেখা যে পড়েছে তাকেও। এই শিকলের নাম cascading abort. আরও খারাপ: T2 যদি ইতিমধ্যে COMMIT করে থাকে, তাকে ফেরানো যায় না — schedule-টি recoverable-ও নয়।

বাস্তবের lock-ভিত্তিক DBMS প্রায় সবাই strict বা rigorous রূপ চালায়, আর lock নেওয়াটা হয় নিজে থেকে: আপনি শুধু UPDATE লেখেন, DBMS X lock নেয়, COMMIT-এ সব ছাড়ে। অ্যাপের দিক থেকে এর বড় শিক্ষা: transaction যত লম্বা, lock তত বেশিক্ষণ ধরা। transaction-এর ভেতরে ব্যবহারকারীর ক্লিকের অপেক্ষা, ধীর API কল বা ইমেইল পাঠানো রাখবেন না।

Lock upgrade আর downgrade

একটি transaction প্রায়ই আগে পড়ে, পরে ঠিক করে লিখবে কি না। তখন S থেকে X-এ upgrade করা হয় — এটি নতুন অধিকার নেওয়া, তাই শুধু growing phase-এ চলে। উল্টো, X থেকে S-এ downgrade অধিকার ছাড়া, তাই শুধু shrinking phase-এ। upgrade-এর একটি পরিচিত ফাঁদ: দুটি transaction একই সারিতে S ধরে আছে, দুজনেই X চায় — প্রত্যেকে অন্যের S ছাড়ার অপেক্ষায়, চিরকাল। এটি conversion deadlock. প্রতিকার: লেখার ইচ্ছা থাকলে শুরু থেকেই লেখার lock নেওয়া — SQL-এ SELECT … FOR UPDATE (অংশ ৯), আর SQL Server-এ এর জন্য আলাদা update (U) lock আছে, যা S-এর সাথে চলে কিন্তু আরেকটি U-এর সাথে নয়।

৫ · Granularity — সারি, page না পুরো টেবিল?

কিসের ওপর lock — একটি সারি, একটি page, পুরো টেবিল, না পুরো ডেটাবেস? এটিই lock granularity. সূক্ষ্ম (সারি) lock-এ বেশি concurrency — দুজন আলাদা সিট একসাথে বেচতে পারেন — কিন্তু লাখো সারি ছুঁলে লাখো lock, মেমরি আর সময় দুটিই বেশি। মোটা (টেবিল) lock একটিই, কিন্তু সবাইকে লাইনে দাঁড় করায়।

দুটি একসাথে চাইলে সমস্যা: T1 orders-এর একটি সারিতে X ধরে আছে। T2 পুরো orders টেবিলে S চায় (রিপোর্টের জন্য)। lock manager কীভাবে জানবে টেবিলের ভেতরে কোথাও X আছে? সব সারির lock খুঁজে দেখা অসম্ভব রকম ধীর। উত্তর: multiple-granularity locking, যেখানে গাছের ওপরের গিঁটে একটি “ইচ্ছার নোটিশ” রাখা হয় — intention lock:

• IS (intention shared): নিচে কোথাও S নেব।
• IX (intention exclusive): নিচে কোথাও X (বা S) নেব।
• SIX = S + IX: পুরো গিঁট পড়ব, আর নিচে কিছু অংশ বদলাব — যেমন পুরো টেবিল পড়ে কয়েকটি সারি সংশোধন।

প্রোটোকল: lock নেওয়া হয় ওপর থেকে নিচে (ডেটাবেস → টেবিল → page → সারি), ছাড়া হয় নিচ থেকে ওপরে। কোনো গিঁটে S বা IS নিতে হলে তার মাথার গিঁটে IS বা IX থাকতে হবে; X, IX বা SIX নিতে হলে মাথায় IX বা SIX. আর পুরো ব্যাপারটি 2PL মেনে। এখন T1 সারিতে X নেওয়ার আগে টেবিলে IX নিয়েছে; T2-র টেবিল-S অনুরোধ টেবিলের গিঁটেই IX-এর সাথে সংঘাত দেখে — একটি তুলনায় উত্তর। পাঁচটি mode-এর পূর্ণ matrix নিচের এডিটরে, ডেটা হিসেবে:

-- সারি = যে lock ধরা আছে, কলাম = নতুন অনুরোধ
SELECT g.held,
  MAX(CASE g.req WHEN 'IS'  THEN g.ok END) AS "IS",
  MAX(CASE g.req WHEN 'IX'  THEN g.ok END) AS "IX",
  MAX(CASE g.req WHEN 'S'   THEN g.ok END) AS "S",
  MAX(CASE g.req WHEN 'SIX' THEN g.ok END) AS "SIX",
  MAX(CASE g.req WHEN 'X'   THEN g.ok END) AS "X"
FROM gm g JOIN modes m ON m.m = g.held
GROUP BY g.held ORDER BY MIN(m.rk);
-- টেবিলে IX ধরা: কোন অনুরোধ এখনই মঞ্জুর?
SELECT req FROM gm WHERE held = 'IX' AND ok = '✓';

বাস্তবে: InnoDB টেবিলে IS/IX আর সারিতে S/X রাখে — LOCK TABLES … WRITE-এর মতো টেবিল-স্তরের অনুরোধ এই IX-এর সাথে মেলায়। SQL Server একটি টেবিলে খুব বেশি সারি-lock জমলে (নথিপত্র অনুযায়ী মোটামুটি ৫,০০০-এর কাছাকাছি) সেগুলোকে একটি টেবিল-lock-এ বদলে ফেলতে পারে — lock escalation; মেমরি বাঁচে, concurrency কমে। InnoDB আর PostgreSQL escalation করে না।

৬ · Deadlock — দুই transfer, উল্টো দিকে

রহিমের হিসাবে ৫,০০০ আর করিমের হিসাবে ৩,০০০ টাকা। একই মুহূর্তে T1 রহিম থেকে করিমকে ৫০০ পাঠায়, আর T2 করিম থেকে রহিমকে ৩০০. দুজনেই strict 2PL মেনে চলে:

Wait-for graph-এর প্রতিটি গিঁট একটি চলমান transaction, আর তীর Tᵢ → Tⱼ মানে Tᵢ এমন একটি lock-এর অপেক্ষায় যা Tⱼ-এর কাছে আছে। deadlock আছে যদি এবং কেবল যদি graph-এ চক্র থাকে। Deadlock detection মানে নিয়মিত (বা প্রতিটি নতুন অপেক্ষার সময়) এই graph-এ চক্র খোঁজা — DFS দিয়ে O(V + E) সময়ে, DSA কোর্সের DFS পর্বের cycle detection. পর্ব ২৫-এর recursive CTE দিয়েও কাজটি করা যায়:

WITH RECURSIVE path(start, node, route) AS (
  SELECT waiter, holder, waiter || '→' || holder FROM waits
  UNION ALL
  SELECT p.start, w.holder, p.route || '→' || w.holder
  FROM path p JOIN waits w ON w.waiter = p.node
  WHERE p.node <> p.start                          -- চক্র বন্ধ হলে থামো
    AND (w.holder = p.start OR instr(p.route, w.holder) = 0)
)
SELECT p.route AS cycle, t.rows_changed AS victim_cost
FROM path p JOIN txn t ON t.id = p.start
WHERE p.node = p.start
ORDER BY t.rows_changed;

Victim বাছাইয়ের মাপকাঠি: কতটা কাজ করেছে (কত সারি বদলেছে, log কতটা), কতগুলো lock ধরে আছে, শেষ হতে আর কত বাকি, আর আগে কতবার victim হয়েছে — শেষটি না দেখলে একই transaction বারবার বলি হয়ে starvation-এ পড়ে। rollback পুরোটা হতে পারে, বা একটি savepoint পর্যন্ত আংশিক। বাস্তব সিস্টেম:

• PostgreSQL: একটি lock-এর জন্য deadlock_timeout (সাধারণত ১ সেকেন্ড) অপেক্ষার পর চক্র খোঁজে; পেলে একটিকে বাতিল করে ত্রুটি দেয় deadlock detected (SQLSTATE 40P01).
• MySQL/InnoDB: প্রতিটি অপেক্ষার সাথে সাথে খোঁজে (innodb_deadlock_detect, সাধারণত চালু), আর “ছোট” transaction-টি — যে কম সারি বদলেছে — rollback করে; ত্রুটি 1213.

Timeout আরও সরল পথ: নির্দিষ্ট সময়ের বেশি অপেক্ষা করলে নিজেকেই বাতিল (InnoDB-র innodb_lock_wait_timeout, সাধারণত ৫০ সেকেন্ড; PostgreSQL-এর lock_timeout, সাধারণত বন্ধ)। graph লাগে না, কিন্তু সময় বেশি হলে deadlock অনেকক্ষণ টেকে, কম হলে নিছক ধীর transaction-ও বাতিল হয়।

অ্যাপের দিক থেকে সবচেয়ে কার্যকর প্রতিকার: সবসময় একই ক্রমে lock নেওয়া। দুই transfer-ই যদি আগে ছোট id-র হিসাব, তারপর বড়টি lock করে, তাহলে কেউ কারও “উল্টো দিকে” অপেক্ষা করতে পারে না — চক্রের জন্য অন্তত একটি তীর বড় থেকে ছোটর দিকে লাগে। তবু deadlock-ত্রুটি ধরে পুরো transaction আবার চালানোর কোড রাখুন:

BEGIN;
-- দুই হিসাব সবসময় id-র ক্রমে lock: (7 → 3) হোক বা (3 → 7)
SELECT id, balance FROM account WHERE id IN (3, 7) ORDER BY id FOR UPDATE;
UPDATE account SET balance = balance - 500 WHERE id = 7;
UPDATE account SET balance = balance + 500 WHERE id = 3;
COMMIT;
-- অ্যাপ: 40P01 (PostgreSQL) বা 1213 (MySQL) পেলে ছোট বিরতি দিয়ে আবার চেষ্টা

৭ · Deadlock ঠেকানো — wait-die আর wound-wait

ধরা না পড়া পর্যন্ত অপেক্ষার বদলে deadlock আগেই অসম্ভব করা যায়। দুটি ক্লাসিক পদ্ধতি (Rosenkrantz, Stearns ও Lewis, ১৯৭৮) প্রতিটি transaction-কে শুরুর সময় একটি timestamp দেয় — ছোট সংখ্যা মানে বয়সে বড় (আগে শুরু)। Tᵢ যখন এমন lock চায় যা Tⱼ-এর কাছে আছে:

এক লাইনে: wait-die-এ শুধু বড়রা অপেক্ষা করে; wound-wait-এ শুধু ছোটরা। abort হওয়া transaction নতুন করে শুরু হয় পুরোনো timestamp নিয়েই.

নিয়মগুলো এত সরল যে একটি CASE-এই লেখা যায়। req টেবিলের প্রতিটি সারি একটি অনুরোধ: কে চাইছে আর তার ts, কার কাছে lock আর তার ts:

SELECT requester || '(' || ts_req || ') → ' || holder || '(' || ts_hold || ')' AS request,
  CASE WHEN ts_req < ts_hold THEN 'wait'
       ELSE requester || ' dies' END AS wait_die,
  CASE WHEN ts_req < ts_hold THEN holder || ' wounded'
       ELSE 'wait' END AS wound_wait
FROM req;

৮ · Lock ছাড়া — timestamp ordering আর optimistic control

Timestamp ordering

Timestamp ordering (TO) কাউকে অপেক্ষা করায় না। প্রতিটি transaction-এর ts থেকেই serial ক্রম আগে ঠিক: ছোট ts আগে। প্রতিটি ডেটা Q-এর সাথে দুটি সংখ্যা থাকে — W-ts(Q): যে সবচেয়ে বড় ts-এর transaction Q লিখেছে, আর R-ts(Q): যে সবচেয়ে বড় ts-এর transaction Q পড়েছে। নিয়ম হলো, যে কাজ এই ক্রমে “দেরিতে পৌঁছেছে”, তাকে বাতিল করা:

প্রতিটি সংঘাতের কাজ ts-এর ক্রমেই ঘটে, তাই precedence graph-এর সব তীর ছোট ts থেকে বড় ts-এর দিকে — চক্র অসম্ভব, schedule conflict serializable. কেউ অপেক্ষা করে না, তাই deadlock-ও নেই। দাম: অনেক rollback, লম্বা transaction-এর বারবার বাতিল হওয়ার ঝুঁকি, আর অপাকা লেখা পড়া আটকাতে বাড়তি ব্যবস্থা না রাখলে cascading abort.

Thomas write rule (Robert Thomas, ১৯৭৯) চতুর্থ সারিটি নরম করে। T1 (ts ১) Q লিখতে এলো, কিন্তু T2 (ts ২) আগেই লিখে ফেলেছে, আর কেউ T1-এর মান পড়েনি (নইলে তৃতীয় সারিতেই ধরা পড়ত)। serial ক্রম T1 → T2-এ T1-এর লেখা এমনিতেই T2-র লেখার নিচে চাপা পড়ত — তাই লেখাটি চুপচাপ বাদ দিলেই হলো, rollback লাগে না। এতে এমন schedule-ও মেলে যা conflict serializable নয় কিন্তু view serializable (পর্ব ৪৫)।

Optimistic concurrency control

H. T. Kung আর John Robinson-এর ১৯৮১ সালের প্রস্তাব, optimistic concurrency control (OCC), সংঘাতকে বিরল ধরে নেয়। প্রতিটি transaction তিন পর্বে চলে:

1. Read: lock ছাড়াই পড়ে, সব লেখা নিজের ব্যক্তিগত খাতায় (workspace) রাখে; কী পড়েছে (read set) আর কী লিখবে (write set) টুকে রাখে।
2. Validation: COMMIT-এর আগে যাচাই — এর মধ্যে যারা COMMIT করেছে, তাদের write set-এর সাথে আমার read set-এর কোনো মিল আছে? মিল থাকলে আমি পুরোনো ডেটা দেখেছি — abort, আবার চেষ্টা।
3. Write: যাচাই পেরোলে খাতার লেখাগুলো ডেটাবেসে বসানো।

validation-এর ক্রমই serial ক্রম। সংঘাত কম হলে OCC lock-এর খরচ আর অপেক্ষা পুরো বাঁচায়; সংঘাত বেশি হলে বারবার পুরো কাজ ফেলে দিতে হয়। ঠিক এই চিন্তা অ্যাপে খুব প্রচলিত — optimistic locking: সারিতে একটি version কলাম, আর লেখার সময় শর্ত “আমি যে version পড়েছিলাম, সেটিই এখনো আছে তো?” product টেবিলে চালের মজুদ (id, name, stock, version):

-- দুই অ্যাপ-সেশন একই সারি পড়ল: stock 10, version 3
SELECT id, stock, version FROM product WHERE id = 1;
-- সেশন ১: 2 বস্তা বিক্রি
UPDATE product SET stock = 10 - 2, version = version + 1
WHERE id = 1 AND version = 3;
-- সেশন ২: 5 বস্তা বিক্রি, কিন্তু তার হাতে পুরোনো version 3
UPDATE product SET stock = 10 - 5, version = version + 1
WHERE id = 1 AND version = 3;
SELECT id, stock, version FROM product WHERE id = 1;
-- সেশন ২ নতুন করে পড়ে (8, version 4), আবার চেষ্টা
UPDATE product SET stock = 8 - 5, version = version + 1
WHERE id = 1 AND version = 4;
SELECT id, stock, version FROM product WHERE id = 1;

৯ · বাস্তব সিস্টেমে — phantom, FOR UPDATE, SKIP LOCKED আর SQLite

Phantom আর range lock

সারির lock শুধু যে সারি আছে তাকে আটকায়। T1 হিসাব করে “CSE বিভাগের সব শিক্ষকের মোট বেতন”, আর সেই সারিগুলোতে S নেয়। এর মধ্যে T2 CSE-তে একজন নতুন শিক্ষক INSERT করে — নতুন সারি, তাতে কারও lock নেই। T1 আবার হিসাব করলে নতুন একজন হাজির: phantom. সমাধান হলো শর্তটিকেই lock করা — predicate lock (“dept = 'CSE' মানে এমন যেকোনো সারি”) — কিন্তু দুটি predicate মিলে যায় কি না যাচাই সাধারণভাবে কঠিন আর দামি। তাই বাস্তবে index-এর একটি পরিসর lock করা হয়। InnoDB REPEATABLE READ-এ next-key lock নেয়: একটি index-রেকর্ড আর তার ঠিক আগের ফাঁক (gap lock). ধরুন age-এর index-এ 10, 20, 30 আছে; WHERE age BETWEEN 15 AND 25 FOR UPDATE মোটামুটি (10, 20] আর (20, 30] আটকায় — তখন age 18 বা 22-এর INSERT অপেক্ষা করে। PostgreSQL এই পথে না গিয়ে MVCC আর SERIALIZABLE-এ বিশেষ predicate lock ব্যবহার করে — পর্ব ৪৭-এ।

SELECT … FOR UPDATE, NOWAIT আর SKIP LOCKED

“পড়ো, তারপর লেখো” ধাঁচে পড়ার সময়েই লেখার lock নেওয়ার উপায় SELECT … FOR UPDATE — অংশ ৪-এর conversion deadlock এড়ায়। FOR SHARE শুধু পড়ার lock নেয় (অন্যরা বদলাতে পারে না, পড়তে পারে)। lock না পেলে অপেক্ষার বদলে NOWAIT সাথে সাথে ত্রুটি দেয়, আর SKIP LOCKED lock-ধরা সারি বাদ দিয়ে বাকিগুলো দেয়। শেষটি job queue-র জন্য চমৎকার: দশটি worker একসাথে “পরের কাজটি” চাইলে প্রত্যেকে আলাদা কাজ পায়, কেউ কারও জন্য দাঁড়ায় না। PostgreSQL 9.5 আর MySQL 8.0 থেকে SKIP LOCKED আছে (এখানে চালানো যায় না):

-- প্রতিটি worker: একটি কাজ দাবি করা
BEGIN;
SELECT id, task FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;          -- অন্য worker যেটি ধরেছে, সেটি বাদ
UPDATE jobs SET status = 'running', worker = 'w1' WHERE id = :id;
COMMIT;

SELECT * FROM seat WHERE id = 7 FOR UPDATE NOWAIT;  -- ধরা থাকলে সাথে সাথে ত্রুটি
SELECT * FROM seat WHERE id = 7 FOR SHARE;          -- MySQL 8.0-এর আগে: LOCK IN SHARE MODE

SQLite: একসাথে একজন লেখক

SQLite সারি বা টেবিল নয়, পুরো ডেটাবেস ফাইল lock করে। পুরোনো rollback-journal পদ্ধতিতে অনেক পাঠক একসাথে চলে (SHARED lock); একজন লেখক RESERVED lock নেয়, আর COMMIT-এর মুহূর্তে সব পাঠক সরে যাওয়ার অপেক্ষা করে EXCLUSIVE পায়। তাই FOR UPDATE-এর দরকারই নেই — সমর্থনও নেই। সাধারণ BEGIN (DEFERRED) প্রথম লেখার আগে কোনো লেখার lock নেয় না; BEGIN IMMEDIATE শুরুতেই নেয়। WAL mode-এ (write-ahead log, পর্ব ৪৮) পাঠক আর লেখক কেউ কাউকে আটকায় না: পাঠক তার শুরুর মুহূর্তের ছবি দেখে, লেখক নতুন পাতাগুলো আলাদা ফাইলে জমায় — তবে লেখক তখনো একজনই।

BEGIN IMMEDIATE;               -- শুরুতেই লেখার অধিকার
SELECT free_seats FROM train WHERE id = 1;
UPDATE train SET free_seats = free_seats - 1
WHERE id = 1 AND free_seats > 0;
COMMIT;
SELECT free_seats FROM train;
PRAGMA journal_mode;
-- অন্য DBMS-এর অভ্যাস SQLite-এ চলে না:
SELECT free_seats FROM train WHERE id = 1 FOR UPDATE;

SQLite-এ job queue চালাতে SKIP LOCKED লাগে না: “খোঁজা আর দাবি” একটি UPDATE-এ লিখলে দুই worker কখনো একই কাজ পায় না, কারণ একটি লেখা শেষ না হলে আরেকটি শুরুই হয় না। RETURNING (পর্ব ১৪) দাবি-করা কাজটি সাথে সাথে ফেরত দেয়:

-- worker w1: খোঁজা আর দাবি এক বাক্যে
UPDATE jobs SET status = 'running', worker = 'w1'
WHERE id = (SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1)
RETURNING id, task;
-- worker w2: একই বাক্য, পরের কাজ
UPDATE jobs SET status = 'running', worker = 'w2'
WHERE id = (SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1)
RETURNING id, task;
SELECT id, task, status, worker FROM jobs;

এবার সত্যিকারের দুটি সংযোগ — একটি অস্থায়ী ফাইলে, timeout=0 দিয়ে, যাতে lock না পেলে অপেক্ষা না করে সাথে সাথে database is locked আসে (Python-এর sqlite3 সাধারণত ৫ সেকেন্ড অপেক্ষা করে; দেখুন Python ও SQLite):

import sqlite3, tempfile, os
path = os.path.join(tempfile.mkdtemp(), "rail.db")
a = sqlite3.connect(path, timeout=0, isolation_level=None)   # clerk A
b = sqlite3.connect(path, timeout=0, isolation_level=None)   # clerk B
a.execute("CREATE TABLE seat (id INTEGER PRIMARY KEY, free INTEGER)")
a.execute("INSERT INTO seat VALUES (1, 1)")
def run(name, conn, sql):
    try: r = conn.execute(sql).fetchall(); print(name, sql, "->", r)
    except sqlite3.OperationalError as e: print(name, sql, "-> ERROR:", e)

run("A", a, "BEGIN IMMEDIATE")              # A takes the write lock now
run("A", a, "UPDATE seat SET free = 0")
run("B", b, "SELECT free FROM seat")        # reading is still allowed
run("B", b, "BEGIN IMMEDIATE")              # a second writer is refused
run("A", a, "COMMIT")
print("--- deferred BEGIN: both read first, then both try to write")
run("A", a, "BEGIN"); run("B", b, "BEGIN")
run("A", a, "SELECT free FROM seat"); run("B", b, "SELECT free FROM seat")
run("A", a, "UPDATE seat SET free = 1")
run("B", b, "UPDATE seat SET free = 1")
run("A", a, "COMMIT")                       # A must wait for B's read lock
run("B", b, "ROLLBACK"); run("A", a, "COMMIT")

WAL mode-এ একই দুই সংযোগ:

import sqlite3, tempfile, os
path = os.path.join(tempfile.mkdtemp(), "rail.db")
a = sqlite3.connect(path, timeout=0, isolation_level=None)   # writer
b = sqlite3.connect(path, timeout=0, isolation_level=None)   # reader
print("mode:", a.execute("PRAGMA journal_mode=WAL").fetchone()[0])
a.execute("CREATE TABLE seat (id INTEGER PRIMARY KEY, free INTEGER)")
a.execute("INSERT INTO seat VALUES (1, 1)")
def run(name, conn, sql):
    try: r = conn.execute(sql).fetchall(); print(name, sql, "->", r)
    except sqlite3.OperationalError as e: print(name, sql, "-> ERROR:", e)

run("B", b, "BEGIN"); run("B", b, "SELECT free FROM seat")   # B's snapshot
run("A", a, "BEGIN IMMEDIATE"); run("A", a, "UPDATE seat SET free = 0")
run("B", b, "SELECT free FROM seat")        # reader not blocked by writer
run("A", a, "COMMIT")                       # writer not blocked by reader
run("B", b, "SELECT free FROM seat")        # same snapshot: still old value
run("B", b, "UPDATE seat SET free = free - 1")   # stale snapshot cannot write
run("B", b, "ROLLBACK"); run("B", b, "SELECT free FROM seat")

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

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

AI-এর লেখা concurrency-কোড চালু করার আগে — চেকলিস্ট

• প্রতিটি “পড়ো-হিসাব-লেখো” জায়গায় শর্তযুক্ত UPDATE, version কলাম বা FOR UPDATE আছে
• দুই সংযোগে বা কয়েকটি থ্রেডে একসাথে চালিয়ে শেষ ফল আর সারির সংখ্যা মিলিয়েছি
• সব কোড-পথে সারি lock হয় একই ক্রমে
• deadlock, lock timeout আর serialization ত্রুটিতে পুরো transaction আবার চালানোর ব্যবস্থা আছে, সীমিত চেষ্টায়
• transaction ছোট: ভেতরে ব্যবহারকারীর অপেক্ষা, ইমেইল বা বাইরের API কল নেই
• isolation level আর DBMS-এর lock-আচরণ নথি থেকে মিলিয়েছি (SQLite, MySQL আর PostgreSQL আলাদা)