Transaction আর ACID

১ · Transaction কী — কাজের একটি অবিভাজ্য একক

Rahim তাঁর bKash থেকে Karim-কে ২,০০০ টাকা পাঠালেন। ডেটাবেসের দিক থেকে এটি অন্তত দুটি আলাদা লেখা: Rahim-এর ব্যালান্স ২,০০০ কমানো, আর Karim-এর ব্যালান্স ২,০০০ বাড়ানো। সাথে হয়তো একটি লেনদেনের রেকর্ড। এখন ভাবুন, প্রথম লেখাটির পরই সার্ভারের বিদ্যুৎ চলে গেল। Rahim-এর টাকা কাটা গেছে, Karim কিছু পাননি — দুই হাজার টাকা স্রেফ হাওয়া। উল্টো ক্রমে লিখলেও বিপদ: Karim পেলেন, Rahim-এর কাটা হলো না — টাকা শূন্য থেকে তৈরি।

এই সমস্যার সমাধানই transaction: কয়েকটি read আর write অপারেশনের একটি দল, যাকে DBMS একটি logical unit of work হিসেবে দেখে। দলটির সব কাজ একসাথে পাকা হবে, নয়তো কোনোটিই হবে না। পর্ব ৩-এ যে transaction manager আর recovery manager-এর নাম শুনেছিলেন, এই মডিউলে তাদের ভেতরে ঢুকবেন।

তত্ত্বের বইয়ে একটি transaction লেখা হয় তার read আর write অপারেশনের ক্রম হিসেবে। A আর B দুটি ডেটা আইটেম (এখানে দুটি ব্যালান্স) হলে টাকা পাঠানো T₁:

T1:  read(A)
     A := A - 2000
     write(A)
     read(B)
     B := B + 2000
     write(B)
     commit

read(X) ডেটাবেস থেকে X-এর মান transaction-এর নিজের মেমরিতে আনে; write(X) সেই মান ডেটাবেসে ফেরত দেয় (আগে buffer pool-এ, পরে ডিস্কে — পর্ব ৩৯)। এই সরল খাতাই পর্ব ৪৫-এ schedule আর serializability-র ভাষা হবে। লক্ষ করুন, write(A)-এর পর আর write(B)-এর আগে ডেটাবেস একটি “ভুল” অবস্থায় থাকে — মোট টাকা ২,০০০ কম। Transaction-এর পুরো প্রতিশ্রুতি হলো: এই মধ্যবর্তী অবস্থা কেউ দেখবে না, আর এই অবস্থায় আটকে থাকা যাবে না।

এই পর্বের লাইভ SQL চলে একটি ছোট ওয়ালেট ডেটাবেসে। wallet টেবিলে তিনটি হিসাব: id, owner (মালিক) আর balance (টাকা, পূর্ণসংখ্যা) — Rahim ৫,০০০, Karim ১,২০০, Shapla ৩০০; মোট ৬,৫০০. balance-এ একটি CHECK constraint আছে: balance >= 0. transfers টেবিলে প্রতিটি লেনদেনের রেকর্ড: from_id, to_id (দুটিই wallet-এর foreign key), amount (> 0) আর idem_key (UNIQUE; অংশ ৯-এ কাজে লাগবে)। প্রতিটি এডিটর নতুন করে এই ডেটা বানায় — “নমুনা ডেটাবেস” বাক্সে পুরো স্ক্রিপ্ট দেখতে পাবেন।

২ · Transaction-এর পাঁচটি অবস্থা

প্রতিটি transaction তার জীবনে কয়েকটি নির্দিষ্ট অবস্থার মধ্য দিয়ে যায়। Silberschatz, Korth আর Sudarshan-এর পাঠ্যবই থেকে প্রচলিত মডেলটিতে পাঁচটি অবস্থা: active, partially committed, committed, failed আর aborted. committed বা aborted — এই দুটির যেকোনো একটিতে পৌঁছালে transaction-টি “terminated” — শেষ। নিচের ডায়াগ্রামে দুটি পথই দেখুন: Rahim → Karim ২,০০০ টাকা সফলভাবে, আর Shapla → Karim ৫০০ টাকা ব্যর্থভাবে (Shapla-র আছে মাত্র ৩০০)।

দুটি সূক্ষ্ম কথা। এক, “committed” অবস্থাটি এক-দিকের দরজা: একবার commit হলে সেই transaction আর ROLLBACK করা যায় না। ভুল হলে উল্টো কাজের একটি নতুন transaction লাগে — হিসাবরক্ষণে যাকে বলে compensating entry, যেমন ভুল করে পাঠানো টাকা ফেরতের আলাদা লেনদেন। দুই, ডায়াগ্রামের পাশের টেবিলে দেখুন, “পাকা” মোট টাকা কখনো বদলায়নি। মধ্যবর্তী অবস্থাগুলো ছিল, কিন্তু শুধু transaction-এর নিজের ভেতরে।

৩ · Atomicity — হয় সব, নয় কিছুই না

১৯৮৩ সালে Theo Härder আর Andreas Reuter একটি প্রবন্ধে transaction-এর চারটি গুণকে একটি সংক্ষিপ্ত নাম দেন: ACID. তার আগে, ১৯৮১ সালে Jim Gray “The Transaction Concept: Virtues and Limitations” প্রবন্ধে atomicity, consistency আর durability-র কথা লিখেছিলেন। প্রথম অক্ষর Atomicity: transaction-এর সব কাজ একসাথে কার্যকর হয়, নয়তো একটিও না। গ্রিক “atomos” মানে যা ভাঙা যায় না।

Atomicity-র শত্রু তিন রকম: স্টেটমেন্টের ত্রুটি (constraint ভাঙা, শূন্য দিয়ে ভাগ), অ্যাপের সিদ্ধান্ত (“টাকা নেই, বাতিল করো”), আর crash (বিদ্যুৎ, OS, ডিস্ক)। প্রথম দুটিতে DBMS চালু থাকে, তাই সরাসরি ফেরানো যায়। তৃতীয়টি কঠিন: crash-এর সময় কিছু পরিবর্তন হয়তো ডিস্কে পৌঁছে গেছে। কৌশলটি হলো undo log: কোনো পরিবর্তন ডিস্কে যাওয়ার আগে তার আগের মান একটি log-এ লিখে রাখা। Crash-এর পর DBMS চালু হলে log পড়ে দেখে, কোন transaction-এর commit record নেই — তাদের সব পরিবর্তন আগের মান দিয়ে মুছে দেয়। এই crash recovery-র বিস্তারিত (WAL, checkpoint, ARIES) পর্ব ৪৮-এ। এখন দেখুন, transaction থাকা আর না থাকায় কী তফাত:

এটি শুধু ছবি নয়। নিচের Python প্রোগ্রামটি একটি আলাদা প্রক্রিয়া (child process) চালায়, যে Rahim-এর টাকা কেটেই os._exit(1) দিয়ে হঠাৎ মারা যায় — বিদ্যুৎ চলে যাওয়ার মতো, কোনো পরিষ্কার-করা ছাড়া। তারপর মূল প্রোগ্রাম ডেটাবেস ফাইলটি আবার খোলে। একবার autocommit-এ, একবার transaction-এর ভেতরে:

import sqlite3, subprocess, sys, tempfile, os
CHILD = """
import sqlite3, sys, os
con = sqlite3.connect(sys.argv[1], isolation_level=None)
if sys.argv[2] == "txn": con.execute("BEGIN")
con.execute("UPDATE wallet SET balance = balance - 2000 WHERE id = 1")
os._exit(1)   # power cut: Karim's credit never runs
"""
def trial(mode):
    path = os.path.join(tempfile.mkdtemp(), "wallet.db")
    con = sqlite3.connect(path, isolation_level=None)
    con.execute("CREATE TABLE wallet (id INTEGER PRIMARY KEY, balance INTEGER)")
    con.execute("INSERT INTO wallet VALUES (1, 5000), (2, 1200)")
    con.close()
    subprocess.run([sys.executable, "-c", CHILD, path, mode])
    con = sqlite3.connect(path)          # restart after the crash
    rows = [r[0] for r in con.execute("SELECT balance FROM wallet ORDER BY id")]
    print(f"{mode:10} after crash: {rows} total {sum(rows)}")
trial("autocommit")
trial("txn")

৪ · SQL-এ transaction — BEGIN, COMMIT আর ROLLBACK

তিনটি কমান্ডেই মূল কাজ: BEGIN (বা স্ট্যান্ডার্ড SQL-এর START TRANSACTION) transaction শুরু করে; COMMIT সব পরিবর্তন পাকা করে; ROLLBACK সব বাতিল করে BEGIN-এর আগের অবস্থায় ফেরায়। প্রথমে একটি সফল টাকা পাঠানো:

BEGIN;
UPDATE wallet SET balance = balance - 2000 WHERE id = 1;
UPDATE wallet SET balance = balance + 2000 WHERE id = 2;
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 2000);
COMMIT;
SELECT * FROM wallet;
SELECT SUM(balance) AS total FROM wallet;

এবার Shapla Karim-কে ৫০০ পাঠাতে চান, যদিও তাঁর আছে ৩০০. অ্যাপটি আগে Karim-এর হিসাবে যোগ করে, তারপর Shapla-র থেকে কাটে (ক্রমটি ইচ্ছে করে “ভুল”, যাতে প্রথম লেখাটি সফল হয়):

BEGIN;
UPDATE wallet SET balance = balance + 500 WHERE id = 2;
UPDATE wallet SET balance = balance - 500 WHERE id = 3;
COMMIT;

এখানে দুই স্তরের atomicity আলাদা করে চিনুন। Statement-level: একটি স্টেটমেন্ট — ধরুন ১০০ সারির UPDATE — হয় পুরোটা, নয়তো কিছুই না; প্রায় সব আধুনিক DBMS এটি সবসময় দেয়। Transaction-level: কয়েকটি স্টেটমেন্টের পুরো দল হয় পুরোটা, নয়তো কিছুই না; এটি পেতে আপনাকে BEGIN … COMMIT লিখতে হয়, আর ত্রুটিতে ROLLBACK. ত্রুটির পর DBMS-গুলোর আচরণও আলাদা:

-- PostgreSQL: একটি ত্রুটিতেই পুরো transaction "aborted"
BEGIN;
UPDATE wallet SET balance = balance + 500 WHERE id = 2;
UPDATE wallet SET balance = balance - 500 WHERE id = 3;
-- ERROR: new row for relation "wallet" violates check constraint "wallet_balance_check"
SELECT * FROM wallet;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT;   -- PostgreSQL উত্তর দেয় ROLLBACK — কিছুই পাকা হয় না

-- MySQL (InnoDB): শুধু ব্যর্থ স্টেটমেন্টটি ফেরে, transaction খোলা থাকে
START TRANSACTION;
UPDATE wallet SET balance = balance + 500 WHERE id = 2;
UPDATE wallet SET balance = balance - 500 WHERE id = 3;   -- ERROR 3819 (CHECK, 8.0.16+)
ROLLBACK;   -- অ্যাপকে নিজে পাঠাতে হবে

PostgreSQL-এর কঠোরতা নিরাপদ: ভুল করে আধা-কাজ COMMIT হওয়ার উপায় নেই। SQLite আর MySQL নমনীয়: ব্যর্থ স্টেটমেন্ট বাদ দিয়ে আপনি চাইলে অন্য পথে এগোতে পারেন — কিন্তু তখন দায়িত্বটি পুরোপুরি আপনার। একটি ব্যতিক্রম মনে রাখুন: MySQL-এ deadlock হলে InnoDB পুরো transaction-ই ফেরায়।

ত্রুটির অপেক্ষা না করে অ্যাপ নিজেও বাতিলের সিদ্ধান্ত নিতে পারে। একটি প্রচলিত কৌশল শর্তযুক্ত UPDATE: WHERE balance >= 500 জুড়ে দিলে টাকা না থাকলে কোনো সারি বদলায় না, আর অ্যাপ “০টি সারি” দেখে ROLLBACK পাঠায়:

BEGIN;
UPDATE wallet SET balance = balance + 500 WHERE id = 2;
UPDATE wallet SET balance = balance - 500
WHERE id = 3 AND balance >= 500;
SELECT * FROM wallet;   -- transaction-এর ভেতর থেকে দেখা
ROLLBACK;
SELECT * FROM wallet;   -- ROLLBACK-এর পরে

৫ · SAVEPOINT — transaction-এর ভেতরে ফেরার চিহ্ন

কখনো পুরো transaction বাতিল করতে চান না, শুধু শেষ কয়েকটি কাজ। একটি অনলাইন মুদি দোকানের অর্ডারে পাঁচটি আইটেম; একটির স্টক নেই। পুরো অর্ডার বাতিলের বদলে শুধু সেই আইটেমটি বাদ দিলেই হয়। এর জন্য SAVEPOINT: transaction-এর ভেতরে একটি নাম-দেওয়া চিহ্ন।

• SAVEPOINT name — এখানে একটি চিহ্ন বসানো।
• ROLLBACK TO name — চিহ্নের পরের সব পরিবর্তন বাতিল করা; transaction চলতে থাকে, আর চিহ্নটিও থেকে যায় (আবার ফেরা যায়)।
• RELEASE name — চিহ্নটি তুলে নেওয়া; তার পরের পরিবর্তনগুলো মূল transaction-এর অংশ হয়ে যায়। এটি COMMIT নয় — পরে ROLLBACK হলে এগুলোও বাতিল হবে।

এই এডিটরে নতুন তিনটি টেবিল: products (name, stock; CHECK stock ≥ 0) — Rice 5kg ১০টি, Hilsa ১টি, Lentils 1kg ৫টি; orders (id, customer); আর order_items (order_id, product, qty).

BEGIN;
INSERT INTO orders VALUES (1, 'Rahim');
SAVEPOINT item_rice;
INSERT INTO order_items VALUES (1, 'Rice 5kg', 2);
UPDATE products SET stock = stock - 2 WHERE name = 'Rice 5kg' AND stock >= 2;
RELEASE item_rice;
SAVEPOINT item_hilsa;
INSERT INTO order_items VALUES (1, 'Hilsa', 3);
UPDATE products SET stock = stock - 3 WHERE name = 'Hilsa' AND stock >= 3;
ROLLBACK TO item_hilsa;   -- ০টি সারি বদলেছে, তাই এই আইটেম বাদ
RELEASE item_hilsa;
COMMIT;
SELECT * FROM order_items;
SELECT * FROM products;

Savepoint একটির ভেতরে আরেকটি বসানো যায় — তখন এগুলো একটি stack-এর মতো আচরণ করে। নিচের ডায়াগ্রামে তিন স্তরের চিহ্ন আর দুই রকম ROLLBACK TO দেখুন:

Savepoint-এর একটি বড় ব্যবহার আসে driver আর ORM থেকে (পর্ব ৪৯–৫০)। বহু ORM “nested transaction” বলতে আসলে savepoint বোঝায় — বাইরের transaction চলার সময় ভেতরের transaction() ব্লক একটি SAVEPOINT বসায়, ব্যর্থ হলে ROLLBACK TO. আর PostgreSQL-এ, যেখানে একটি ত্রুটি পুরো transaction নষ্ট করে, ঝুঁকির স্টেটমেন্টের আগে savepoint বসিয়ে ত্রুটির পর ROLLBACK TO দিলে transaction বাঁচানো যায়। SQLite-এ একটি বাড়তি নিয়ম: transaction-এর বাইরে SAVEPOINT দিলে সেটি নিজেই একটি transaction শুরু করে, আর সেই বাইরের savepoint-এর RELEASE মানে COMMIT.

৬ · Consistency — constraint DBMS-এর, invariant অ্যাপের

ACID-এর Consistency সবচেয়ে বেশি ভুল বোঝা অক্ষর। সংজ্ঞা: একটি transaction ডেটাবেসকে একটি সঠিক অবস্থা থেকে আরেকটি সঠিক অবস্থায় নেয়। কিন্তু “সঠিক” কে ঠিক করে? দুই স্তর আছে:

ধরুন একটি কোডে ভুল: Rahim থেকে ৭০০ কাটা হলো, কিন্তু Shapla-কে দেওয়া হলো ৭০ (একটি শূন্য বাদ পড়েছে)। কোনো constraint ভাঙে না:

SELECT SUM(balance) AS total_before FROM wallet;
BEGIN;
UPDATE wallet SET balance = balance - 700 WHERE id = 1;
UPDATE wallet SET balance = balance + 70 WHERE id = 3;  -- bug: 70, not 700
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 3, 700);
COMMIT;
SELECT SUM(balance) AS total_after,
       6500 - SUM(balance) AS missing FROM wallet;
SELECT id, owner, balance FROM wallet WHERE balance < 0;

Invariant রক্ষার কিছু কাজ DBMS-কে দেওয়া যায়: CHECK, trigger (পর্ব ২৯), বা নকশা বদলে। যেমন double-entry হিসাবে প্রতিটি লেনদেন দুটি সারি — একটি −৭০০, একটি +৭০০ — আর “প্রতিটি লেনদেনের সারিগুলোর যোগফল শূন্য” নিয়মটি একটি কোয়েরিতে যাচাই করা সহজ। তবু শেষ বিচারে অর্থের নিয়ম জানে ব্যবসা, DBMS নয়।

কেন A, I আর D মিলে C রক্ষা করে — একটি ছোট প্রমাণ

ধরুন I একটি invariant (যেমন মোট টাকা = ৬,৫০০), আর প্রতিটি transaction T-এর কোড এই অর্থে সঠিক: I মানা কোনো অবস্থা থেকে শুরু করে T একা শেষ পর্যন্ত চললে শেষের অবস্থাও I মানে। দাবি: T₁, T₂, …, Tₙ যেকোনো ক্রমে একটির পর একটি (serial) চললে প্রতিটি পাকা অবস্থাই I মানে।

প্রমাণ (n-এর ওপর induction): n = ০ হলে শুরুর অবস্থা, যা ধরে নেওয়া হয়েছে সঠিক। ধরুন প্রথম k টির পর অবস্থা S_k I মানে। T_k+1 S_k থেকে একা চলে — কারণ serial ক্রমে তার মাঝে অন্য কেউ নেই — তাই কোডের সঠিকতা অনুযায়ী S_k+1-ও I মানে। ∎

প্রমাণটি তিনটি গোপন অনুমানের ওপর দাঁড়িয়ে, আর প্রতিটি অনুমান ACID-এর একটি অক্ষর। “শেষ পর্যন্ত চললে” — মাঝপথে থেমে গেলে মধ্যবর্তী অবস্থা (৬,৫০০ − ২,০০০) I ভাঙে; atomicity নিশ্চিত করে যে অর্ধেক-চলা transaction-এর কোনো চিহ্ন থাকে না। “একা চলে” — আসলে হাজারো transaction একসাথে চলে; isolation নিশ্চিত করে যে ফল কোনো একটি serial ক্রমের সমান, তাই প্রমাণটি খাটে (পর্ব ৪৫-এ serializability ঠিক এই সংজ্ঞা)। “পাকা অবস্থা” — crash-এর পর যা ফিরে আসে, তা যেন শেষ committed অবস্থাই হয়; durability. তাই অনেকে বলেন, C আসলে লক্ষ্য, আর A, I, D তার উপায়।

৭ · Isolation আর Durability — একসাথে চলা আর crash-এ টিকে থাকা

Isolation: যেন একটির পর একটি

Isolation-এর আদর্শ রূপ: অনেক transaction একসাথে চললেও ফল এমন হবে যেন তারা কোনো এক ক্রমে একটির পর একটি চলেছে। কেন একটির পর একটি চালালেই হয় না? কারণ তাতে একটি ধীর transaction সবাইকে আটকে রাখত — মাসের শেষে বেতনের দিনে লাখো লেনদেন এক লাইনে দাঁড়াত। তাই DBMS কাজগুলো মিশিয়ে চালায় (concurrency control), আর নিশ্চিত করে যে মেশানো ফল নিরাপদ। নিরাপদ না হলে কী ঘটে, তার সবচেয়ে পরিচিত উদাহরণ lost update:

Isolation কতটা কঠোর হবে, তা ঠিক করে isolation level. SQL স্ট্যান্ডার্ড চারটি স্তর সংজ্ঞায়িত করে তিনটি anomaly দিয়ে; lost update চতুর্থ একটি পরিচিত সমস্যা। শুধু পরিচয় এখানে — সংজ্ঞা, উদাহরণ আর কোন স্তর কোনটি ঠেকায়, তা পর্ব ৪৭-এ:

ডিফল্ট স্তর DBMS ভেদে আলাদা: PostgreSQL, Oracle আর SQL Server-এ Read Committed, MySQL-এর InnoDB-তে Repeatable Read, আর SQLite কার্যত Serializable — কারণ একসাথে একজনই লিখতে পারে। এক ডেটাবেসে দুটি সংযোগ খুলে নিজেই দেখুন: A-র খসড়া B দেখে কি না, আর B লিখতে চাইলে কী হয়:

import sqlite3, tempfile, os
path = os.path.join(tempfile.mkdtemp(), "wallet.db")
a = sqlite3.connect(path, isolation_level=None)   # we send BEGIN/COMMIT ourselves
b = sqlite3.connect(path, isolation_level=None, timeout=0)
a.execute("CREATE TABLE wallet (id INTEGER PRIMARY KEY, balance INTEGER)")
a.execute("INSERT INTO wallet VALUES (1, 5000), (2, 1200)")
def show(con, who):
    rows = [r[0] for r in con.execute("SELECT balance FROM wallet ORDER BY id")]
    print(who, rows, "total", sum(rows))
a.execute("BEGIN")
a.execute("UPDATE wallet SET balance = balance - 2000 WHERE id = 1")
a.execute("UPDATE wallet SET balance = balance + 2000 WHERE id = 2")
show(a, "A inside its transaction:")
show(b, "B before A commits:      ")
try:
    b.execute("UPDATE wallet SET balance = balance + 1 WHERE id = 1")
except sqlite3.OperationalError as e:
    print("B tries to write:         ", e)
a.execute("COMMIT")
show(b, "B after A commits:       ")

Durability: COMMIT মানে প্রতিশ্রুতি

Durability: একবার “সফল” উত্তর দিলে সেই পরিবর্তন crash, বিদ্যুৎ চলে যাওয়া বা OS-এর হঠাৎ থেমে যাওয়াতেও টিকে থাকবে। মেমরি বিদ্যুৎ গেলে মুছে যায়, তাই commit-এর আগে কিছু একটা স্থায়ী স্টোরেজে পৌঁছাতেই হবে। প্রতিটি পরিবর্তিত data page ছড়ানো জায়গায় লিখতে গেলে ধীর হয়; তাই প্রায় সব DBMS-এর কৌশল write-ahead log (WAL): পরিবর্তনের ছোট বিবরণ একটি log ফাইলের শেষে পরপর লেখা, আর COMMIT-এর সময় শুধু সেই log ডিস্কে নিশ্চিত করা। data page পরে ধীরেসুস্থে লেখা হয়; crash হলে log থেকে redo.

“ডিস্কে নিশ্চিত করা” কথাটি সূক্ষ্ম। write() সাধারণত শুধু OS-এর cache-এ লেখে; ডিস্কে পৌঁছানো নিশ্চিত করে fsync. ডিস্কের নিজের cache থাকলে সেটিও যেন মিথ্যা না বলে — তাই সার্ভারের ডিস্কে প্রায়ই ব্যাটারি-ব্যাকড cache. fsync দামি, তাই DBMS-গুলো ইচ্ছামতো দাম কমানোর সুইচ দেয় — আর সেই সুইচ মানে durability-র একটু ছাড়:

প্রতিটি commit-এ একটি করে fsync হলে প্রতি সেকেন্ডে কত commit সম্ভব, তা ডিস্কের fsync-গতিতে বাঁধা। তাই DBMS-গুলো একসাথে আসা কয়েকটি commit-এর log একটি fsync-এ লেখে — group commit. WAL, fsync আর recovery-র পুরো যন্ত্র পর্ব ৪৮-এ। আর একটি সীমা মনে রাখুন: durability একটি যন্ত্রের ভেতরের প্রতিশ্রুতি। পুরো সার্ভার পুড়ে গেলে বাঁচায় backup আর replication (পর্ব ৫২)।

৮ · Autocommit, SQLite-এর BEGIN mode আর transactional DDL

BEGIN না লিখলে কী হয়? প্রায় সব DBMS তখন autocommit মোডে: প্রতিটি স্টেটমেন্ট নিজেই একটি পূর্ণ transaction — শুরু, চালানো, পাকা। এই পাতার রানারও তাই। দুটির তফাত দেখুন:

-- ক. স্পষ্ট transaction: ROLLBACK কাজ করে
BEGIN;
UPDATE wallet SET balance = balance - 1000 WHERE id = 1;
ROLLBACK;
SELECT balance FROM wallet WHERE id = 1;
-- খ. autocommit: UPDATE শেষ হতেই পাকা
UPDATE wallet SET balance = balance - 1000 WHERE id = 1;
SELECT balance FROM wallet WHERE id = 1;
ROLLBACK;

অ্যাপের কোডে ছবিটি আরও জটিল, কারণ driver (অ্যাপ আর DBMS-এর মাঝের লাইব্রেরি) নিজের ডিফল্ট ঠিক করে:

দুটি উল্টো বিপদ। Python-এ commit() ভুলে গেলে লেখা নীরবে হারায় — “কোড চলল, কিন্তু টেবিলে কিছু নেই”. Java বা PHP-তে transaction ভুলে গেলে প্রতিটি লেখা আলাদা পাকা, আর মাঝপথের ত্রুটিতে অর্ধেক কাজ থেকে যায়। Python 3.12 থেকে sqlite3-এ একটি স্পষ্ট autocommit অ্যাট্রিবিউট আছে, আর with con: ব্লক সফল হলে commit, exception-এ rollback করে। কোন ভাষায় কীভাবে, তা পর্ব ৪৯-এ।

SQLite-এর তিন রকম BEGIN

SQLite-এ পুরো ডেটাবেসে একজন লেখক, তাই transaction কখন lock নেবে তা গুরুত্বপূর্ণ:

• BEGIN DEFERRED (ডিফল্ট): শুরুতে কোনো lock নয়; প্রথম পড়ায় পড়ার lock, প্রথম লেখায় লেখার lock. ঝুঁকি: পড়ার পর লেখার lock নিতে গিয়ে অন্য লেখকের কারণে SQLITE_BUSY — মাঝপথে।
• BEGIN IMMEDIATE: শুরুতেই লেখার অধিকার নেয়। অন্য লেখক থাকলে BEGIN-এই অপেক্ষা বা BUSY — কাজ শুরুর আগে, যা সামলানো সহজ। লিখবে জানা থাকলে এটিই ভালো।
• BEGIN EXCLUSIVE: rollback journal মোডে পাঠকদেরও আটকায়; WAL মোডে IMMEDIATE-এর মতোই।

Transactional DDL — পর্ব ৩১-এর স্মরণ

CREATE, ALTER, DROP কি ROLLBACK হয়? Transactional DDL থাকলে হয়। SQLite আর PostgreSQL-এ আছে:

BEGIN IMMEDIATE;
CREATE TABLE audit_log (id INTEGER PRIMARY KEY, note TEXT);
INSERT INTO audit_log (note) VALUES ('fee rule v2');
ALTER TABLE wallet ADD COLUMN frozen INTEGER DEFAULT 0;
SELECT name FROM sqlite_master WHERE type = 'table';
ROLLBACK;
SELECT name FROM sqlite_master WHERE type = 'table';
SELECT * FROM wallet WHERE id = 1;

MySQL আর Oracle-এ ছবিটি উল্টো: বেশিরভাগ DDL-এর আগে আর পরে implicit commit হয়। ফলে BEGIN; INSERT …; CREATE TABLE …; ROLLBACK; লিখলে INSERT-টিও পাকা হয়ে যায় — CREATE TABLE চলার আগেই DBMS চুপচাপ COMMIT করেছে। MySQL 8.0-এর “atomic DDL” একটি একক DDL স্টেটমেন্টকে পূর্ণ বা শূন্য করে, কিন্তু কয়েকটি স্টেটমেন্ট মিলে একটি transaction করে না। SQL Server-এ বেশিরভাগ DDL transaction-এর ভেতরে ফেরানো যায়। PostgreSQL-এও ব্যতিক্রম আছে: CREATE DATABASE, VACUUM আর CREATE INDEX CONCURRENTLY transaction ব্লকের ভেতরে চলে না।

৯ · অ্যাপে transaction-এর নকশা — ছোট রাখা, retry আর idempotency

দীর্ঘ transaction কেন ক্ষতিকর

একটি transaction যতক্ষণ খোলা, ততক্ষণ সে কিছু না কিছু ধরে রাখে। দীর্ঘ transaction-এর তিনটি দাম:

• Lock ধরে রাখা: যে সারিগুলো সে লিখেছে, সেগুলোর lock commit পর্যন্ত থাকে (পর্ব ৪৬)। অন্য সবাই অপেক্ষায় — একটি জনপ্রিয় পণ্যের স্টক-সারি এক মিনিট আটকে থাকলে পুরো দোকান থমকে যায়। SQLite-এ তো পুরো ডেটাবেসই।
• পুরোনো সংস্করণ জমা: PostgreSQL আর InnoDB MVCC ব্যবহার করে (পর্ব ৪৭) — পাঠকদের জন্য সারির পুরোনো সংস্করণ রেখে দেয়। সবচেয়ে পুরোনো খোলা transaction-টি হয়তো সেগুলো দেখতে চাইবে, তাই পরিষ্কারের কাজ (PostgreSQL-এ VACUUM, InnoDB-তে purge) সেগুলো মুছতে পারে না। ঘণ্টার পর ঘণ্টা খোলা একটি transaction-এ টেবিল ফুলে ওঠে — bloat — আর সব কোয়েরি ধীর হয়।
• ব্যর্থতার দাম: দীর্ঘ transaction ব্যর্থ হলে ROLLBACK-ও দীর্ঘ, আর conflict-এ পড়ার সম্ভাবনা বেশি।

Retry: ব্যর্থতা স্বাভাবিক

একসাথে অনেক transaction চললে কিছু ব্যর্থ হবেই — দোষ ছাড়াই। দুটি transaction একে অপরের lock-এর অপেক্ষায় পড়লে deadlock; DBMS একটিকে বাতিল করে (PostgreSQL SQLSTATE 40P01, MySQL ত্রুটি 1213). Serializable স্তরে DBMS নিরাপদ ক্রম নিশ্চিত করতে না পারলে serialization failure দেয় (SQLSTATE 40001). এদের সঠিক প্রতিক্রিয়া: পুরো transaction শুরু থেকে আবার — শুধু শেষ স্টেটমেন্ট নয়, কারণ আগের পড়াগুলো হয়তো এখন পুরোনো।

for attempt in 1 .. 5:
    try:
        BEGIN
        ... সব read আর write ...
        COMMIT
        return success
    except (deadlock 40P01/1213, serialization failure 40001):
        ROLLBACK
        wait random(10ms .. 10ms × 2^attempt)   # সবাই একসাথে আবার না আসে
    except other error:
        ROLLBACK
        raise                                    # যুক্তির ত্রুটি: retry নয়
raise "অনেকবার ব্যর্থ — পরে চেষ্টা করুন"

একটি শর্ত: retry নিরাপদ শুধু তখনই, যখন transaction-এর ভেতরে ডেটাবেসের বাইরের কোনো কাজ নেই — SMS পাঠানো, অন্য API ডাকা। নইলে প্রতি retry-তে গ্রাহক আরেকটি SMS পাবেন। বাইরের কাজ COMMIT-এর পরে, বা পর্ব ৩৮-এর outbox pattern দিয়ে।

Idempotency key — একই অনুরোধ দুবার এলে

সবচেয়ে কঠিন ব্যর্থতা: অ্যাপ COMMIT পাঠাল, আর ঠিক তখন নেটওয়ার্ক কেটে গেল। Transaction কি হয়েছে? অ্যাপ জানে না। আবার পাঠালে টাকা দুবার কাটতে পারে; না পাঠালে হয়তো একবারও কাটেনি। সমাধান idempotency key: ক্লায়েন্ট প্রতিটি অনুরোধে একটি অনন্য id পাঠায়, আর সার্ভার সেটি লেনদেনের সাথে UNIQUE কলামে লেখে — একই transaction-এর ভেতরে। Stripe-এর মতো পেমেন্ট API এই কৌশলের জন্য Idempotency-Key নামের একটি header নেয়।

-- প্রথম অনুরোধ
BEGIN;
INSERT INTO transfers (from_id, to_id, amount, idem_key)
VALUES (1, 3, 500, 'req-7f3a');
UPDATE wallet SET balance = balance - 500 WHERE id = 1;
UPDATE wallet SET balance = balance + 500 WHERE id = 3;
COMMIT;
SELECT id, balance FROM wallet;
-- উত্তর হারিয়ে গেল; ক্লায়েন্ট একই key নিয়ে আবার পাঠাল
BEGIN;
INSERT INTO transfers (from_id, to_id, amount, idem_key)
VALUES (1, 3, 500, 'req-7f3a');

সামনে: ACID বনাম BASE

বহু সার্ভারে ছড়ানো ডেটাবেসে (পর্ব ৫৪) প্রতিটি transaction-এ সব কপির সম্মতি নেওয়া ধীর, আর নেটওয়ার্ক ভাগ হলে অসম্ভবও হতে পারে। তাই কিছু সিস্টেম ইচ্ছে করে ছাড় দেয় — BASE: Basically Available, Soft state, Eventually consistent. সব সময় উত্তর দেবে, কিন্তু কিছু সময় কপিগুলো ভিন্ন মান দেখাতে পারে। নামটি রসায়নের রসিকতা — অ্যাসিডের বিপরীতে ক্ষার। কোথায় কোনটি মানায়, CAP উপপাদ্যসহ, পর্ব ৫৪-এ।

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

“bKash-এর মতো টাকা পাঠানোর API লিখে দাও” — AI কয়েক সেকেন্ডে কোড দেয়, যা চলে, পরীক্ষায় পাস করে, আর প্রথম ব্যস্ত দিনে টাকা হারায়। Transaction-এর ভুল একা চালানোয় ধরা পড়ে না; ধরা পড়ে একসাথে অনেক অনুরোধ আর ব্যর্থতায়। AI-এর প্রচলিত ভুল:

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

• যে লেখাগুলো একসাথে হতে হবে, সেগুলো একটি BEGIN … COMMIT-এর ভেতরে — driver-এর autocommit ডিফল্ট জেনে নিয়েছি
• প্রতিটি exception-এর পথে ROLLBACK আছে, আর সংযোগ pool-এ খোলা transaction নিয়ে ফেরে না
• দুটি সংযোগে একই অ্যাকাউন্টে একসাথে অনুরোধ পাঠিয়ে মোট টাকা মিলিয়েছি
• মাঝপথে ইচ্ছে করে ত্রুটি বা প্রক্রিয়া বন্ধ করে দেখেছি, আধা-কাজ থাকে না
• retry পুরো transaction-এর, আর ভেতরে বাইরের কোনো কাজ নেই
• পেমেন্টের মতো কাজে idempotency key একই transaction-এ UNIQUE কলামে লেখা হয়