ট্রানজ্যাকশন, ACID, আইসোলেশন আর লকিং

একটা বিকাশ ট্রান্সফার আসলে দুটো পরিবর্তন: টাকা এক ওয়ালেট থেকে বের হয়, আরেকটায় ঢোকে। দুটোর মাঝখানে সিস্টেম ক্র্যাশ করলে টাকা উধাও। একই মুহূর্তে দুটো ট্রান্সফার একই ওয়ালেট ছুঁলে শূন্য থেকে টাকা তৈরিও হয়ে যেতে পারে। দুটো সমস্যারই ডেটাবেসের উত্তর হলো ট্রানজ্যাকশন (transaction): কয়েকটা স্টেটমেন্টের এমন একটা দল, যা একসাথে সফল হয় বা একসাথে ব্যর্থ হয়, আর একই সময়ে কাজ করা অন্য সেশন থেকে সুরক্ষিত থাকে।

AI সিস্টেমও প্রতিনিয়ত এই সমস্যাগুলোতে পড়ে, শুধু চেহারাটা আলাদা: একটা ETL লোড, যা মাঝপথে থেমে অর্ধেক দিনের ডেটা রেখে যেতে পারবে না; একসাথে কয়েকটা ওয়ার্কার ইভ্যালুয়েশনের ফল লিখছে; একই ইউজারের কয়েকটা রিকোয়েস্ট একসাথে এসে তাঁর টোকেন বাজেট কমাচ্ছে। এই পাতায় প্রতিটা নিশ্চয়তা আসল ডেটাবেসে কীভাবে কাজ করে — আর কোথায় ভেঙে পড়ে — তা দেখবেন, PostgreSQL-এ একসাথে খোলা দুটো সেশন নিজে ধাপে ধাপে চালিয়ে।

যা শিখবেন

• BEGIN, COMMIT, ROLLBACK, সেভপয়েন্ট, আর ACID কী প্রতিশ্রুতি দেয়
• ফরেন কি-র ক্যাসকেডিং অ্যাকশন: CASCADE, SET NULL, RESTRICT
• আইসোলেশন লেভেল, আর কোন লেভেলে কোন অসংগতি ঘটতে পারে: ডার্টি, নন-রিপিটেবল আর ফ্যান্টম রিড, লস্ট আপডেট
• লক, SELECT … FOR UPDATE, ডেডলক, আর অপটিমিস্টিক বনাম পেসিমিস্টিক লকিং
• অ্যাটমিক আপডেট, আর ব্যর্থ ট্রানজ্যাকশন নিরাপদে আবার চেষ্টা করা

BEGIN, COMMIT, ROLLBACK

shop.db-তে দুটো ওয়ালেট বানান। CHECK ব্যালান্সকে ঋণাত্মক হতে দেয় না:

CREATE TABLE wallets (
    wallet_id INTEGER PRIMARY KEY,
    owner     TEXT NOT NULL,
    balance   INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO wallets VALUES (1, 'Nadia', 5000), (2, 'Tanvir', 3000);

BEGIN;
UPDATE wallets SET balance = balance - 1500 WHERE wallet_id = 1;
UPDATE wallets SET balance = balance + 1500 WHERE wallet_id = 2;
COMMIT;

SELECT * FROM wallets ORDER BY wallet_id;

+-----------+--------+---------+
| wallet_id | owner  | balance |
+-----------+--------+---------+
|         1 | Nadia  |    3500 |
|         2 | Tanvir |    4500 |
+-----------+--------+---------+

BEGIN আর COMMIT-এর মাঝে পরিবর্তনগুলো শুধু এই সেশনই দেখতে পায়। COMMIT দুটোকে একসাথে স্থায়ী করে। এবার এমন একটা ট্রান্সফার, যা সফল হতে পারে না — Nadia পাঠাচ্ছেন ৫০০০, কিন্তু তাঁর আছে মাত্র ৩৫০০। এবার ক্রেডিট আগে চলছে:

BEGIN;
UPDATE wallets SET balance = balance + 5000 WHERE wallet_id = 2;
UPDATE wallets SET balance = balance - 5000 WHERE wallet_id = 1;

Error: CHECK constraint failed: balance >= 0

ডেবিট ব্যর্থ হয়েছে, কিন্তু খোলা ট্রানজ্যাকশনের ভেতরে ক্রেডিট আগেই হয়ে গেছে। এখন কমিট করলে শূন্য থেকে ৫০০০ টাকা তৈরি হতো। তার বদলে রোলব্যাক করুন:

ROLLBACK;
SELECT * FROM wallets ORDER BY wallet_id;

+-----------+--------+---------+
| wallet_id | owner  | balance |
+-----------+--------+---------+
|         1 | Nadia  |    3500 |
|         2 | Tanvir |    4500 |
+-----------+--------+---------+

দুটো পরিবর্তনই নেই; ওয়ালেটগুলো ঠিক আগের মতো। BEGIN না দিলে প্রতিটা স্টেটমেন্ট নিজেই একটা ট্রানজ্যাকশন (অটোকমিট), আর ক্রেডিটটা থেকে যেত। অ্যাপ্লিকেশন কোডে এটা লেখা হয় এভাবে: "পুরো কাজটা চালানো; যেকোনো এরর হলে রোলব্যাক"। Python-এর sqlite3 মডিউল নিজেও আপনার হয়ে ট্রানজ্যাকশন খোলে: ডিফল্ট (পুরোনো isolation_level) মোডে সে INSERT, UPDATE, DELETE বা REPLACE-এর আগে চুপচাপ BEGIN পাঠায়, তাই con.commit() না ডাকা পর্যন্ত কিছুই সেভ হয় না। Python 3.12-এ এসেছে autocommit অ্যাট্রিবিউট (autocommit=False দিলে DB-API-র নিয়মমতো সবসময় একটা ট্রানজ্যাকশন খোলা থাকে)। with con: ব্লক সফল হলে কমিট করে, এক্সেপশন হলে রোলব্যাক করে, কিন্তু কানেকশন বন্ধ করে না। বিস্তারিত আছে Python ও ডেটাবেস: কানেকশন, প্যারামিটার আর ট্রানজ্যাকশন পাতায়।

ACID

সেভপয়েন্ট: ট্রানজ্যাকশনের একটা অংশ ফিরিয়ে নেওয়া

সেভপয়েন্ট (savepoint) হলো ট্রানজ্যাকশনের ভেতরে একটা বুকমার্ক। ROLLBACK TO শুধু তার পরের কাজটুকু ফিরিয়ে নেয়, আর ট্রানজ্যাকশন চলতে থাকে। ক্লাসিক ব্যবহার: একটা ব্যাচ লোড, যেখানে একটা খারাপ সারি পুরো ব্যাচ ডোবাবে না, শুধু বাদ পড়বে:

BEGIN;
INSERT INTO wallets VALUES (3, 'Mitu', 1000);
SAVEPOINT before_bad_row;
INSERT INTO wallets VALUES (4, 'Rafiq', -50);

Error: CHECK constraint failed: balance >= 0

ROLLBACK TO before_bad_row;
INSERT INTO wallets VALUES (5, 'Sadia', 700);
COMMIT;
SELECT * FROM wallets ORDER BY wallet_id;

+-----------+--------+---------+
| wallet_id | owner  | balance |
+-----------+--------+---------+
|         1 | Nadia  |    3500 |
|         2 | Tanvir |    4500 |
|         3 | Mitu   |    1000 |
|         5 | Sadia  |     700 |
+-----------+--------+---------+

Mitu আর Sadia ঢুকেছেন, Rafiq-এর অবৈধ সারি ঢোকেনি। আসল লোডার বাতিল সারিটা পরে ঠিক করার জন্য একটা কোয়ারেন্টাইন টেবিলেও লিখে রাখত। PostgreSQL আর MySQL-এও একই কমান্ড চলে (যে সেভপয়েন্ট আর দরকার নেই, RELEASE SAVEPOINT name দিয়ে সেটা সরিয়ে ফেলা যায়)।

ক্যাসকেডিং অ্যাকশন

ফরেন কি যে সারিকে নির্দেশ করে, সেটা মুছলে কী হবে — তাও ফরেন কি-ই ঠিক করে। ডিফল্ট (NO ACTION, বা প্রায় একই রকম RESTRICT) মুছতে দেয় না; CASCADE নির্ভরশীল সারিগুলোও মুছে দেয়; SET NULL সেগুলো রাখে কিন্তু রেফারেন্সটা খালি করে দেয়। SQLite-এ এসব ঘটে শুধু PRAGMA foreign_keys = ON থাকলে, যা sqlhelp.py চালু করে দেয়। প্রোডাক্ট-রিভিউয়ের একটা টেবিলে দুটো পছন্দই দেখা যাক:

CREATE TABLE reviews (
    review_id   INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL,
    customer_id INTEGER,
    stars       INTEGER NOT NULL CHECK (stars BETWEEN 1 AND 5),
    FOREIGN KEY (product_id)  REFERENCES products (product_id)   ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE SET NULL
);
INSERT INTO reviews VALUES (1, 10, 8, 4), (2, 10, 1, 5), (3, 4, 8, 3);

DELETE FROM products WHERE product_id = 10;     -- কখনো বিক্রি হয়নি: রিভিউগুলোও সাথে যায়
DELETE FROM customers WHERE customer_id = 8;    -- কখনো অর্ডার করেননি: রিভিউ থাকে, নামহীন হয়ে
SELECT * FROM reviews ORDER BY review_id;

DELETE FROM customers WHERE customer_id = 1;    -- অর্ডার আছে: মুছতে দেওয়া হয় না

+-----------+------------+-------------+-------+
| review_id | product_id | customer_id | stars |
+-----------+------------+-------------+-------+
|         3 |          4 | NULL        |     3 |
+-----------+------------+-------------+-------+
Error: FOREIGN KEY constraint failed

পুরো ক্যাসকেড DELETE-এর ট্রানজ্যাকশনের ভেতরেই চলে, তাই এটাও সব-নয়তো-কিছুই-না। CASCADE ব্যবহার করুন সেই সারিগুলোর জন্য, যারা প্যারেন্ট ছাড়া থাকতে পারে না (অর্ডার লাইন, মুছে ফেলা প্রোডাক্টের রিভিউ); SET NULL যেখানে প্যারেন্ট মুছে গেলেও চাইল্ড সারি থেকে যাওয়া উচিত (মডেলের জন্য রিভিউয়ের লেখা রেখে ব্যক্তিগত যোগসূত্র মুছে ফেলা); আর মুছতে না দেওয়ার ডিফল্টটা ব্যবসায়িক বা আইনি মূল্য আছে এমন যেকোনো কিছুর জন্য, যেমন অর্ডার। customers → orders-এ অসাবধান CASCADE থাকলে একটা DELETE বিক্রির পুরো ইতিহাস মুছে দিত।

একসাথে দুটো সেশন: PostgreSQL-এ আইসোলেশন

আইসোলেশনের সমস্যা দেখা দেয় কেবল তখনই, যখন দুটো সেশন একই সময়ে কাজ করে, তাই দুটো কানেকশন লাগবে। Python থেকে psycopg2 দিয়ে সেগুলো খোলা হবে PostgreSQL-এ, যেখানে শপ ডেটাবেস লোড করা আছে। কোনো আর্গুমেন্ট ছাড়া psycopg2.connect() স্ট্যান্ডার্ড এনভায়রনমেন্ট ভ্যারিয়েবলগুলো পড়ে — PGHOST, PGPORT, PGUSER, PGPASSWORD আর PGDATABASE — তাই আগে এগুলো সেট করুন (লোকাল Docker সার্ভারের জন্য: PGHOST=localhost, আপনার পোর্ট, ইউজার আর পাসওয়ার্ড, আর যে ডেটাবেসে shop.sql লোড করেছেন)। থ্রেড লাগবে না: প্রথমে A, তারপর B, তারপর আবার A — এক এক ধাপ করে চালানো হবে, যাতে প্রতিটা সেশন ঠিক কী দেখছে তা স্পষ্ট দেখতে পান।

import psycopg2
from psycopg2 import errors

a = psycopg2.connect()          # সেশন A
b = psycopg2.connect()          # সেশন B

def run(conn, sql, params=None):
    """conn-এর চলতি ট্রানজ্যাকশনে একটা স্টেটমেন্ট চালায়; একক মান বা সারির সংখ্যা ফেরত দেয়।"""
    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchone()[0] if cur.description else cur.rowcount

run(a, "DROP TABLE IF EXISTS accounts")
run(a, """CREATE TABLE accounts (
              account_id INTEGER PRIMARY KEY,
              owner      TEXT NOT NULL,
              balance    INTEGER NOT NULL CHECK (balance >= 0))""")
run(a, "INSERT INTO accounts VALUES (1, 'Nadia', 5000), (2, 'Tanvir', 3000)")
a.commit()
print("A sees", run(a, "SELECT COUNT(*) FROM accounts"), "accounts")
a.commit()

A sees 2 accounts

psycopg2 প্রথম স্টেটমেন্টেই নিজে থেকে একটা ট্রানজ্যাকশন শুরু করে, আর commit() বা rollback() না হওয়া পর্যন্ত খোলা রাখে। এজন্যই A-র ট্রানজ্যাকশন খোলা রেখে B দিয়ে ডেটা দেখা যায়।

আইসোলেশন লেভেল আর অসংগতি

SQL স্ট্যান্ডার্ডে চারটা আইসোলেশন লেভেল আছে; প্রতিটা আগেরটার চেয়ে বেশি অসংগতি আটকায়। লেভেল যত কড়া, তত নিরাপদ — তবে দাম দিতে হয় বেশি অপেক্ষা বা বেশি রিট্রাই দিয়ে:

ডার্টি রিড

ডার্টি রিড (dirty read) মানে অন্য সেশনের কমিট-না-হওয়া পরিবর্তন দেখে ফেলা — এমন ডেটা, যা রোলব্যাক হয়ে যেতে পারে, অর্থাৎ কখনো ছিলই না। PostgreSQL এটা কখনো হতে দেয় না; READ UNCOMMITTED চাইলেও আপনি পান READ COMMITTED:

b.set_session(isolation_level="READ UNCOMMITTED")

run(a, "UPDATE accounts SET balance = 0 WHERE account_id = 2")      # A: কমিট হয়নি
print("B sees Tanvir's balance:", run(b, "SELECT balance FROM accounts WHERE account_id = 2"))
a.rollback()
b.rollback()

B sees Tanvir's balance: 3000

MySQL কিন্তু হতে দেয়। একই পরীক্ষা দুটো PyMySQL কানেকশনে (এরা আপনার সেট করা MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD আর MYSQL_DATABASE ভ্যারিয়েবল পড়ে), শপের products টেবিলে:

import os
import pymysql

def mysql_connect():
    return pymysql.connect(host=os.environ["MYSQL_HOST"], port=int(os.environ["MYSQL_PORT"]),
                           user=os.environ["MYSQL_USER"], password=os.environ["MYSQL_PASSWORD"],
                           database=os.environ["MYSQL_DATABASE"])

ma, mb = mysql_connect(), mysql_connect()
run(mb, "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

run(ma, "UPDATE products SET stock = 0 WHERE product_id = 3")       # A: কমিট হয়নি
print("B sees keyboard stock:", run(mb, "SELECT stock FROM products WHERE product_id = 3"))
ma.rollback()
print("after A's rollback:   ", run(mb, "SELECT stock FROM products WHERE product_id = 3"))
mb.rollback()
ma.close()
mb.close()

B sees keyboard stock: 0
after A's rollback:    12

B এমন একটা ০ স্টক পড়ল, যা আসলে কখনো ছিলই না। READ UNCOMMITTED-এ পড়া একটা ড্যাশবোর্ড বা ফিচার পাইপলাইন পরে বাতিল হওয়া ট্রানজ্যাকশনের সংখ্যাও লিখে রাখতে পারে। এটা ব্যবহারের ভালো কারণ প্রায় কখনোই থাকে না।

নন-রিপিটেবল আর ফ্যান্টম রিড

নন-রিপিটেবল রিড: B এক ট্রানজ্যাকশনে একটা সারি দুবার পড়ে আর দুই রকম মান পায়, কারণ মাঝখানে A একটা পরিবর্তন কমিট করেছে। ফ্যান্টম রিড: B একই ফিল্টারের কোয়েরি দুবার চালায় আর আলাদা সারির সেট পায়, কারণ A একটা সারি ইনসার্ট করেছে। নিচের ফাংশনটা হুবহু একই ক্রমের ধাপগুলো দুটো আলাদা লেভেলে চালায়:

def read_twice(level):
    b.set_session(isolation_level=level)
    sql_balance = "SELECT balance FROM accounts WHERE account_id = 1"
    sql_count = "SELECT COUNT(*) FROM accounts WHERE balance >= 3000"
    balance1, count1 = run(b, sql_balance), run(b, sql_count)      # B: প্রথমবার দেখা

    run(a, "UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1")
    run(a, "INSERT INTO accounts VALUES (3, 'Mitu', 4000)")
    a.commit()                                                       # A: পরিবর্তন + ইনসার্ট, কমিট করা

    balance2, count2 = run(b, sql_balance), run(b, sql_count)      # B: একই কোয়েরি আবার
    b.commit()
    print(f"{level:16} balance {balance1} -> {balance2}   accounts >= 3000: {count1} -> {count2}")

    run(a, "DELETE FROM accounts WHERE account_id = 3")              # সব আগের মতো ফিরিয়ে দেওয়া
    run(a, "UPDATE accounts SET balance = balance + 1000 WHERE account_id = 1")
    a.commit()

read_twice("READ COMMITTED")
read_twice("REPEATABLE READ")

READ COMMITTED   balance 5000 -> 4000   accounts >= 3000: 2 -> 3
REPEATABLE READ  balance 5000 -> 5000   accounts >= 3000: 2 -> 2

READ COMMITTED-এ প্রতিটা স্টেটমেন্ট দেখে সেই স্টেটমেন্ট শুরু হওয়ার আগে কমিট হওয়া সবকিছু, তাই B-এর দ্বিতীয়বারের দেখা আলাদা। REPEATABLE READ-এ পুরো ট্রানজ্যাকশন দেখে প্রথম স্টেটমেন্টের সময় নেওয়া একটাই স্ন্যাপশট। কয়েকটা কোয়েরি দিয়ে বানানো রিপোর্ট বা ট্রেনিং-ডেটা এক্সপোর্টের জন্য আপনি ঠিক এটাই চান: সবগুলোকে একই মুহূর্তের বর্ণনা দিতে হবে, নইলে যোগফল মিলবে না।

লস্ট আপডেট, আর তা ঠেকানোর চারটা উপায়

বাস্তবের সবচেয়ে সাধারণ বাগ: দুটো সেশনই একটা মান পড়ে, অ্যাপ্লিকেশনে নতুন মান হিসাব করে, তারপর লেখে। দ্বিতীয় লেখাটা নিঃশব্দে প্রথমটাকে মুছে দিয়ে তার জায়গা নেয়। দুজন কাস্টমার শেষ কয়েকটা কিবোর্ড কিনছেন:

b.set_session(isolation_level="READ COMMITTED")
stock_a = run(a, "SELECT stock FROM products WHERE product_id = 3")   # A পড়ে 12
stock_b = run(b, "SELECT stock FROM products WHERE product_id = 3")   # B পড়ে 12
run(a, "UPDATE products SET stock = %s WHERE product_id = 3", (stock_a - 1,))
a.commit()
run(b, "UPDATE products SET stock = %s WHERE product_id = 3", (stock_b - 1,))
b.commit()
print("two sold, stock is now", run(a, "SELECT stock FROM products WHERE product_id = 3"))
a.commit()

two sold, stock is now 11

দুটো কিবোর্ড বিক্রি হলো, কিন্তু স্টক কমল একটা। কোনো এরর নেই, কোনো সতর্কবার্তা নেই। নিচে সমাধানগুলো, সবচেয়ে সহজটা থেকে সবচেয়ে ব্যাপকভাবে খাটে এমনটা পর্যন্ত।

১. অ্যাটমিক আপডেট: হিসাবটা ডেটাবেসকেই করতে দিন

পড়া, যাচাই আর লেখা — তিনটাই একটা স্টেটমেন্টে করুন। আপডেটের সময় সারিটা লক থাকে, তাই একসাথে আসা কমানোর আপডেটগুলো একটা আরেকটাকে মুছে না দিয়ে লাইনে দাঁড়ায়, আর WHERE স্টকের বেশি বিক্রি হতে দেয় না:

for product_id in (3, 9):
    sold = run(a, "UPDATE products SET stock = stock - 1 WHERE product_id = %s AND stock > 0",
               (product_id,))
    print(f"product {product_id}:", "reserved" if sold == 1 else "sold out")
a.commit()
print("keyboard stock:", run(a, "SELECT stock FROM products WHERE product_id = 3"))
a.commit()

product 3: reserved
product 9: sold out
keyboard stock: 10

প্রোডাক্ট ৯-এর স্টক ০, তাই আপডেট কোনো সারির সাথে মেলেনি: rowcount ০ মানে "স্টক শেষ" — যাচাই আর লেখার মাঝে কোনো রেস কন্ডিশন (race condition) নেই। A-র স্টেটমেন্ট কমিট হওয়ার আগে B একই স্টেটমেন্ট চালালে B, A-র সারি-লকের জন্য অপেক্ষা করত, তারপর A-র কমিট করা মানের ওপর আবার stock > 0 যাচাই করত। পরিবর্তনটা যখনই একটা UPDATE … SET x = x + … হিসেবে লেখা যায়, এভাবেই লিখুন।

২. পেসিমিস্টিক লকিং: SELECT … FOR UPDATE

অ্যাপ্লিকেশনকে যখন পড়তে, ভাবতে, তারপর লিখতে হয় (ক্রেডিট লিমিট যাচাই, প্রাইসিং সার্ভিস কল), তখন পড়ার সময়ই সারিটা লক করুন। অন্য যে সেশন একই সারি আপডেট, ডিলিট বা লক করতে চায়, তাকে আপনার কমিট বা রোলব্যাক পর্যন্ত অপেক্ষা করতে হবে; সাধারণ SELECT আটকায় না, শেষ কমিট হওয়া মানটাই পড়ে। আমাদের একটাই Python প্রসেস যাতে আটকে না যায়, তাই লকটা দেখাতে B ব্যবহার করে NOWAIT, যা অপেক্ষা না করে এরর দেয়:

stock = run(a, "SELECT stock FROM products WHERE product_id = 3 FOR UPDATE")   # A সারিটা লক করে
try:
    run(b, "SELECT stock FROM products WHERE product_id = 3 FOR UPDATE NOWAIT")
except errors.LockNotAvailable as exc:
    print("B:", type(exc).__name__, "-", str(exc).splitlines()[0])
    b.rollback()

run(a, "UPDATE products SET stock = %s WHERE product_id = 3", (stock - 1,))
a.commit()                                                                      # লক ছেড়ে দেওয়া হলো
print("B now reads", run(b, "SELECT stock FROM products WHERE product_id = 3 FOR UPDATE"))
b.commit()

B: LockNotAvailable - could not obtain lock on row in relation "products"
B now reads 9

NOWAIT না থাকলে B শুধু অপেক্ষা করত, তারপর পড়ত ৯ — সঠিক, হালনাগাদ মান। প্রোডাকশনে সাধারণত অপেক্ষাই করা হয়, একটা সীমা দিয়ে: SET lock_timeout = '2s' (PostgreSQL) বা innodb_lock_wait_timeout (MySQL)। MySQL 8-এ FOR UPDATE একইভাবে চলে। কাছাকাছি আরেকটা অপশন FOR UPDATE SKIP LOCKED (PostgreSQL আর MySQL 8): অন্য কেউ লক করে রাখা সারির জন্য অপেক্ষা না করে সেগুলো বাদ দিয়ে যায় — কয়েকটা ওয়ার্কার যাতে একটা কিউ টেবিল থেকে একই কাজ দুবার না তোলে, তার প্রচলিত উপায় এটাই। SQLite-এ সারি-লক বলে কিছুই নেই (নিচে দেখুন)।

৩. অপটিমিস্টিক লকিং: একটা ভার্সন কলাম

কেউ একটা ফর্ম এডিট করার সময়, বা একটা LLM কল দশ সেকেন্ড নেওয়ার সময় লক ধরে রাখলে বাকি সবাই আটকে থাকে। অপটিমিস্টিক লকিং কোনো লক ধরে না: প্রতিটা সারির একটা version থাকে; যে ভার্সন পড়েছিলেন সেটা মনে রাখুন, আর সেটা না বদলালে তবেই লিখুন:

run(a, "ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0")
a.commit()

def save_stock(conn, product_id, new_stock, seen_version):
    changed = run(conn, """UPDATE products SET stock = %s, version = version + 1
                           WHERE product_id = %s AND version = %s""",
                  (new_stock, product_id, seen_version))
    conn.commit()
    return changed == 1

read = "SELECT stock || ' v' || version FROM products WHERE product_id = 4"
print("A read", run(a, read), "| B read", run(b, read))
a.commit()
b.commit()
print("A saves:", save_stock(a, 4, 59, 0))
print("B saves:", save_stock(b, 4, 59, 0))
print("now", run(a, read))
a.commit()

A read 60 v0 | B read 60 v0
A saves: True
B saves: False
now 59 v1

B-এর সেভ কোনো সারির সাথে মেলেনি, কারণ ভার্সন এগিয়ে গেছে — তাই B জানে তার ডেটা পুরোনো: আবার লোড করুন, আবার হিসাব করুন, আবার চেষ্টা করুন (বা ইউজারকে দেখান "আইটেমটা বদলে গেছে")। ORM-গুলো এটা সরাসরি সমর্থন করে (SQLAlchemy-র version_id_col, Django-র জন্য আলাদা প্যাকেজ, JPA-র @Version)।

৪. আরও কড়া আইসোলেশন লেভেল

শুরুর সেই পড়া-তারপর-লেখার কোডটা REPEATABLE READ-এ চালালে PostgreSQL নিজেই সংঘাতটা ধরে ফেলে:

b.set_session(isolation_level="REPEATABLE READ")
stock_b = run(b, "SELECT stock FROM products WHERE product_id = 3")   # B-এর স্ন্যাপশট
run(a, "UPDATE products SET stock = stock - 1 WHERE product_id = 3")
a.commit()                                                              # এর মধ্যে A এটা বদলায়
try:
    run(b, "UPDATE products SET stock = %s WHERE product_id = 3", (stock_b - 1,))
except errors.SerializationFailure as exc:
    print("B:", type(exc).__name__, "-", str(exc).splitlines()[0])
    b.rollback()

B: SerializationFailure - could not serialize access due to concurrent update

আপডেটটা হারিয়ে না গিয়ে ফিরিয়ে দেওয়া হলো। বিনিময়ে আপনার কোডকে পুরো ট্রানজ্যাকশন আবার চেষ্টা (retry) করার জন্য প্রস্তুত থাকতে হবে। (MySQL-এর REPEATABLE READ সাধারণ পড়া আর তারপর আপডেটের ক্ষেত্রে এটা করে না — সেখানে লাগবে FOR UPDATE বা অ্যাটমিক আপডেট।) PostgreSQL-এর SERIALIZABLE আরও এগিয়ে, ভিন্ন ভিন্ন সারি ছোঁয়া ট্রানজ্যাকশনের সূক্ষ্ম সংঘাতও ধরে।

ডেডলক

ডেডলক (deadlock) হলো দুটো ট্রানজ্যাকশন, যারা প্রত্যেকে অন্যজনের ধরে রাখা লকের জন্য অপেক্ষা করছে: A অ্যাকাউন্ট ১ লক করে ২ চায়; B ২ লক করে ১ চায়। কেউই এগোতে পারে না, তাই ডেটাবেস একজনকে বেছে নিয়ে বাতিল করে। থ্রেড ছাড়া এটা দেখাতে সেশন C একটা অ্যাসিঙ্ক্রোনাস psycopg2 কানেকশন: সে এমন একটা স্টেটমেন্ট পাঠাতে পারে যা আটকে থাকবে, আর এর মধ্যে B নিয়ে কাজ এগোনো যায়:

import select
import psycopg2.extensions as ext

def wait(conn):
    """অ্যাসিঙ্ক্রোনাস কানেকশনকে তার চলতি স্টেটমেন্ট শেষ করতে দেয়।"""
    while True:
        state = conn.poll()
        if state == ext.POLL_OK:
            return
        select.select([conn.fileno()] if state == ext.POLL_READ else [],
                      [conn.fileno()] if state == ext.POLL_WRITE else [], [])

c = psycopg2.connect(async_=1)
wait(c)
cur_c = c.cursor()

cur_c.execute("BEGIN; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1")
wait(c)                                                                  # C অ্যাকাউন্ট 1 ধরে রেখেছে
run(b, "UPDATE accounts SET balance = balance - 100 WHERE account_id = 2")     # B অ্যাকাউন্ট 2 ধরে রেখেছে
cur_c.execute("UPDATE accounts SET balance = balance + 100 WHERE account_id = 2")   # C, B-এর জন্য অপেক্ষা করে
while not run(a, "SELECT cardinality(pg_blocking_pids(%s)) > 0", (c.get_backend_pid(),)):
    a.commit()                                                           # যতক্ষণ না C সত্যিই অপেক্ষায় পড়ে
a.commit()

run(b, "UPDATE accounts SET balance = balance + 100 WHERE account_id = 1")     # B, C-এর জন্য অপেক্ষা করে: ডেডলক
try:
    wait(c)
except errors.DeadlockDetected as exc:
    print("C:", type(exc).__name__, "-", str(exc).splitlines()[0])
cur_c.execute("ROLLBACK")
wait(c)
b.commit()
print("B's transfer went through:",
      run(a, "SELECT string_agg(owner || ' ' || balance, ', ' ORDER BY account_id) FROM accounts"))
a.commit()

C: DeadlockDetected - deadlock detected
B's transfer went through: Nadia 5100, Tanvir 2900

কোনো সেশন deadlock_timeout (ডিফল্ট ১ সেকেন্ড) সময় অপেক্ষা করলে PostgreSQL ডেডলক খোঁজে। C সবচেয়ে বেশিক্ষণ অপেক্ষা করছিল, তাই তার খোঁজাই আগে চলল, চক্রটা পেল আর C-কে বাতিল করল; তারপর B-এর স্টেটমেন্ট চলে গেল। (MySQL ডেডলক সাথে সাথেই ধরে, আর ছোট ট্রানজ্যাকশনটা রোলব্যাক করে।) ডেডলক ডেটাবেসের বাগ নয়; লকিংয়ের স্বাভাবিক একটা ফল। এগুলো কমান সবসময় একই ক্রমে সারি লক করে (যেমন, প্রতিটা ট্রান্সফারে আগে ছোট account_id), ট্রানজ্যাকশন ছোট রেখে — আর বাকিগুলো সামলান আবার চেষ্টা করে।

ব্যর্থ ট্রানজ্যাকশন আবার চেষ্টা করা

সিরিয়ালাইজেশন ব্যর্থতা (SQLSTATE 40001) আর ডেডলক (40P01) মানে "কিছুই বদলায়নি, আবার চেষ্টা করুন"। পুরো ট্রানজ্যাকশন আবার চালান — ডেটা আবার পড়ে — ছোট কিন্তু বাড়তে থাকা বিরতি দিয়ে, বড়জোর কয়েকবার; অন্য এরর কখনো রিট্রাই করবেন না, যেমন CHECK ভাঙা, যা প্রতিবার একইভাবে ব্যর্থ হবে। প্রথম চেষ্টা ইচ্ছে করে ব্যর্থ করাতে B তার মাঝখানে Nadia-র ব্যালান্স বদলে দেয়:

import time

def transfer(conn, src, dst, amount, attempts=3, interfere=None):
    conn.set_session(isolation_level="REPEATABLE READ")
    for attempt in range(1, attempts + 1):
        try:
            balance = run(conn, "SELECT balance FROM accounts WHERE account_id = %s", (src,))
            if attempt == 1 and interfere:
                interfere()                                  # আরেকটা সেশন ফাঁকে ঢুকে পড়ে
            if balance < amount:
                raise ValueError("insufficient funds")
            run(conn, "UPDATE accounts SET balance = balance - %s WHERE account_id = %s", (amount, src))
            run(conn, "UPDATE accounts SET balance = balance + %s WHERE account_id = %s", (amount, dst))
            conn.commit()
            print(f"attempt {attempt}: committed")
            return
        except (errors.SerializationFailure, errors.DeadlockDetected) as exc:
            conn.rollback()
            print(f"attempt {attempt}: {type(exc).__name__}, retrying")
            time.sleep(0.05 * 2 ** attempt)
    raise RuntimeError("transfer failed after retries")

def b_pays_bill():
    run(b, "UPDATE accounts SET balance = balance - 200 WHERE account_id = 1")
    b.commit()

transfer(a, 1, 2, 1000, interfere=b_pays_bill)
print(run(a, "SELECT string_agg(owner || ' ' || balance, ', ' ORDER BY account_id) FROM accounts"))
a.commit()
for conn in (a, b, c):
    conn.close()

attempt 1: SerializationFailure, retrying
attempt 2: committed
Nadia 3900, Tanvir 3900

চেষ্টা ১ Nadia-র ব্যালান্স পড়েছিল এমন একটা স্ন্যাপশট থেকে, যা B পরে পুরোনো করে দিয়েছে, তাই তার আপডেট ফিরিয়ে দেওয়া হলো। চেষ্টা ২ বর্তমান ব্যালান্স আবার পড়ে কমিট করল। দুটো পরিবর্তনই — B-এর ২০০ আর ১০০০-এর ট্রান্সফার — ফলাফলে আছে; কিছুই হারায়নি। লাইব্রেরি আর ORM-এ রিট্রাই ডেকোরেটর পাবেন, কিন্তু নিয়ম একই: পুরো কাজটা আবার চালান, শুধু রিট্রাই-যোগ্য এররে, আর একটা সীমা রেখে।

SQLite, MySQL আর PostgreSQL পাশাপাশি

SQLite-এর এক-রাইটার মডেলে একটা ফাঁদ আছে। সাধারণ BEGIN আসলে DEFERRED: প্রথম পড়ার আগে কোনো লক নেয় না, আর রাইট-লক নেয় কেবল প্রথম লেখার সময়। এর মাঝে অন্য কোনো কানেকশন লিখে ফেললে লক বাড়ানো সাথে সাথেই database is locked দিয়ে ব্যর্থ হতে পারে — আপনার ট্রানজ্যাকশনের মাঝপথে। BEGIN IMMEDIATE শুরুতেই রাইট-লক নেয়, তাই পড়া-তারপর-লেখার ট্রানজ্যাকশন মাঝপথে ব্যর্থ না হয়ে BEGIN-এই অপেক্ষা করে (busy timeout পর্যন্ত)। এই এক-রাইটার মডেলের কারণেই SQLite নোটবুক বা এক-প্রসেসের অ্যাপের জন্য নিখুঁত, কিন্তু যে ওয়েব অ্যাপে অনেক ইউজার একসাথে লেখে, তার জন্য ভুল পছন্দ।

সাধারণ ভুল

1. অ্যাপ্লিকেশনে পড়া, বদলানো, তারপর লেখা (read-modify-write)। stock = SELECT …; UPDATE … SET stock = stock_from_python - 1 আপডেট হারিয়ে ফেলে। ব্যবহার করুন UPDATE … SET stock = stock - 1 WHERE stock > 0, FOR UPDATE, বা ভার্সন কলাম।
2. ট্রানজ্যাকশন খোলা ফেলে রাখা। ট্রানজ্যাকশন খুলে কিছু না করে বসে থাকা (idle) কানেকশন তার লক আর স্ন্যাপশট ধরে রাখে; অন্যরা অপেক্ষা করে, আর PostgreSQL পুরোনো সারি-সংস্করণ পরিষ্কার করতে পারে না। দ্রুত কমিট বা রোলব্যাক করুন — ইউজারের কোনো কাজ বা ধীর API/LLM কলের পুরো সময় জুড়ে কখনো ট্রানজ্যাকশন খোলা রাখবেন না।
3. এররের পরে কমিট করা। কোনো স্টেটমেন্ট ব্যর্থ হলে রোলব্যাক করুন (বা একটা সেভপয়েন্টে ফিরুন)। PostgreSQL ব্যর্থ ট্রানজ্যাকশনে আর কোনো স্টেটমেন্ট নেয় না (সেখানে COMMIT দিলে আসলে রোলব্যাক হয়); SQLite আর MySQL যেটুকু কাজ হয়েছে সেটুকু কমিট করতে দেয়।
4. ভিন্ন ভিন্ন ক্রমে সারি লক করা। A→B ট্রান্সফার আগে A লক করে আর B→A আগে B লক করলে চাপের সময় ডেডলক হবেই। একটা নির্দিষ্ট ক্রমে লক করুন (আগে ছোট id)।
5. সব এরর রিট্রাই করা, বা অনন্তকাল ধরে করা। শুধু সিরিয়ালাইজেশন ব্যর্থতা আর ডেডলক রিট্রাই করুন, কয়েকবার, ব্যাকঅফসহ, আর পুরো ট্রানজ্যাকশন — শুধু ব্যর্থ স্টেটমেন্টটা নয়।

নিজে চেষ্টা করুন

1. সহজ: SQLite-এ একটা ট্রানজ্যাকশনের ভেতরে Tanvir (ওয়ালেট ২) থেকে Mitu-কে (ওয়ালেট ৩) ৫০০ পাঠান, ব্যালান্সগুলো দেখুন, তারপর রোলব্যাক করে দেখান যে কিছুই বদলায়নি।
2. মাঝারি: Python-এ sqlhelp-এর SQLite কানেকশন দিয়ে reserve(product_id, qty) লিখুন, যা একটা অ্যাটমিক UPDATE দিয়ে স্টক রিজার্ভ করে আর True/False ফেরত দেয়। প্রোডাক্ট ৮ (Laptop Stand, স্টক ২৫) থেকে ৫টা করে তিনবার চেষ্টা করুন, তারপর আরেকবার ১৫টা।
3. কঠিন: SQLite-এর জন্য update_with_version(con, product_id, change) লিখুন, যা স্টক আর ভার্সন পড়ে, ভার্সন যাচাইসহ লেখে, আর সংঘাত হলে ৩ বার পর্যন্ত আবার চেষ্টা করে। আগে version কলামটা যোগ করুন।

উত্তর

-- সহজ
BEGIN;
UPDATE wallets SET balance = balance - 500 WHERE wallet_id = 2;
UPDATE wallets SET balance = balance + 500 WHERE wallet_id = 3;
SELECT * FROM wallets ORDER BY wallet_id;
ROLLBACK;
SELECT * FROM wallets ORDER BY wallet_id;

# মাঝারি
from sqlhelp import con

def reserve(product_id, qty):
    with con:                                   # কমিট করে, এরর হলে রোলব্যাক (con বন্ধ করে না)
        cur = con.execute("UPDATE products SET stock = stock - ? "
                          "WHERE product_id = ? AND stock >= ?", (qty, product_id, qty))
    return cur.rowcount == 1

print([reserve(8, 5) for _ in range(3)], reserve(8, 15))
print(con.execute("SELECT stock FROM products WHERE product_id = 8").fetchone()[0])

# কঠিন
con.execute("ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0")
con.commit()

def update_with_version(con, product_id, change, attempts=3):
    for attempt in range(1, attempts + 1):
        stock, version = con.execute("SELECT stock, version FROM products WHERE product_id = ?",
                                     (product_id,)).fetchone()
        with con:
            cur = con.execute("UPDATE products SET stock = ?, version = version + 1 "
                              "WHERE product_id = ? AND version = ?",
                              (stock + change, product_id, version))
        if cur.rowcount == 1:
            return attempt
    raise RuntimeError("too many conflicts")

print(update_with_version(con, 4, -2))
print(con.execute("SELECT stock, version FROM products WHERE product_id = 4").fetchone())

সারসংক্ষেপ

• ট্রানজ্যাকশন সব-নয়তো-কিছুই-না: BEGIN … COMMIT, বা যেকোনো এররে ROLLBACK; সেভপয়েন্ট দিয়ে একটা অংশ ফিরিয়ে নেওয়া যায়।
• ACID: অ্যাটমিক, কনসিস্টেন্ট (কনস্ট্রেইন্ট মানা হয়), আইসোলেটেড, ডিউরেবল। ফরেন কি-র অ্যাকশনগুলো (CASCADE, SET NULL, মুছতে না দেওয়া) একই ট্রানজ্যাকশনের ভেতরে চলে।
• আইসোলেশন লেভেল মানে নিরাপত্তা আর একসাথে কাজ চলার সুযোগের মধ্যে একটা ভারসাম্য: READ COMMITTED নন-রিপিটেবল আর ফ্যান্টম রিড হতে দেয়; PostgreSQL-এর REPEATABLE READ একটা স্থির স্ন্যাপশট দেয়; এখানকার তিনটা ডেটাবেসের মধ্যে ডার্টি রিড হতে দেয় শুধু MySQL, READ UNCOMMITTED-এ।
• লস্ট আপডেট ঠেকান আগে অ্যাটমিক আপডেট দিয়ে, তারপর FOR UPDATE, ভার্সন কলাম বা কড়া আইসোলেশন দিয়ে।
• ডেডলক আর সিরিয়ালাইজেশন ব্যর্থতা স্বাভাবিক: একই ক্রমে লক করুন, ট্রানজ্যাকশন ছোট রাখুন, আর পুরো ট্রানজ্যাকশন কয়েকবার আবার চেষ্টা করুন।

এরপর: ইনডেক্স: কীভাবে কাজ করে আর কখন কাজে লাগে পাতায় প্রসঙ্গ শুদ্ধতা থেকে গতিতে যায়: ইনডেক্স কীভাবে পুরো টেবিল না পড়েই ডেটাবেসকে সারি খুঁজে দেয়, আর প্রতিটা লেখায় তার দাম কত।