Transactions और DB migrations की टेस्टिंग

टेस्टर की नज़र से transactions और ACID

Transaction कई statements को ऐसे समूह में रखता है कि वे सब साथ लागू हों या कोई भी नहीं। यह BEGIN से शुरू होता है (MySQL में START TRANSACTION समकक्ष है), बदलाव रखने के लिए COMMIT या छोड़ने के लिए ROLLBACK पर ख़त्म होता है, और commit होने तक इसके बदलाव दूसरे sessions को नहीं दिखते। चार ACID गुण बताते हैं कि डेटाबेस क्या वादा करता है:

• Atomicity — सारे statements या कोई नहीं: ऑर्डर, उसके items, stock reservation और payment record साथ लिखे जाते हैं, या कुछ भी नहीं।
• Consistency — हर committed transaction डेटाबेस को एक valid state से दूसरी में ले जाता है: constraints टिके रहते हैं और totals अब भी मेल खाते हैं।
• Isolation — एक साथ चलते transactions एक-दूसरे का अधूरा काम नहीं देखते, उस हद तक जितना चुना गया isolation level वादा करता है।
• Durability — COMMIT लौटते ही बदलाव crash या restart के बाद भी बचा रहता है।

Atomicity की टेस्टिंग: commit, rollback और failure injection

Database engine की गारंटियाँ दोबारा टेस्ट करने की ज़रूरत नहीं। आप यह टेस्ट करते हैं कि ऐप्लिकेशन transactions सही इस्तेमाल करता है या नहीं: साथ रहने वाले statements एक ही transaction में हैं या नहीं, हर error path rollback चलाता है या नहीं, और transaction के बाहर की कोई चीज़ — ईमेल, दूसरी सर्विस को message, फ़ाइल — डेटा के सचमुच commit होने से पहले तो नहीं भेजी जाती।

तरीका है failure injection: पहले के steps चलने के बाद किसी step को फ़ेल कराएँ, फिर बचा-खुचा खोजें। यहाँ engines का अंतर मायने रखता है। PostgreSQL में transaction के अंदर error पूरे transaction को abort कर देता है, और ROLLBACK तक हर अगला statement फ़ेल होता है। MySQL InnoDB में ज़्यादातर errors सिर्फ़ फ़ेल हुए statement को rollback करते हैं और transaction खुला रहता है — इसलिए जो ऐप्लिकेशन कोड error पकड़कर फिर commit करता है, वह उससे पहले लिखी rows रख लेता है। Error paths उसी engine पर टेस्ट करें जिस पर आप ship करते हैं।

Transaction डेटाबेस की सीमा पर ख़त्म भी हो जाता है। जब ऑर्डर सर्विस commit करके payment सर्विस को call करती है और वह फ़ेल होती है, तो कोई database rollback committed ऑर्डर को वापस नहीं ले सकता; डिज़ाइन को compensating action (जैसे ऑर्डर रद्द करने वाली saga) या ऐसा outbox चाहिए जो events सिर्फ़ commit के बाद publish करे। उस compensation को अपने आप में एक फ़ीचर की तरह टेस्ट करें।

• कई items में से आख़िरी में invalid डेटा — ऑर्डर और पहले के items बचे नहीं रहने चाहिए।
• ऑर्डर लिखे जाने के बाद payment provider timeout — ऑर्डर परिभाषित state (pending या cancelled) में ख़त्म होता है, बिना payment के कभी “paid” नहीं।
• दो steps के बीच ऐप्लिकेशन process kill करना (test environment में) — restart पर कुछ भी आधा लिखा नहीं, और retries duplicate नहीं बनाते।
• Transaction के बीच unique-key violation — यूज़र को साफ़ error दिखता है और कोई partial rows नहीं बचतीं।

-- PostgreSQL test database: the order must not survive a failed item
BEGIN;

INSERT INTO orders (order_id, customer_id, status, total_amount, created_at)
VALUES (9001, 317, 'PENDING', 150.00, '2026-09-28 10:00:00');

INSERT INTO order_items (order_item_id, order_id, product_sku, quantity, unit_price)
VALUES (90011, 9001, 'SKU-100', 0, 150.00);  -- violates CHECK (quantity > 0)

ROLLBACK;

SELECT COUNT(*) AS leftover FROM orders WHERE order_id = 9001;  -- expect 0

Concurrency anomalies जिन्हें आप टेस्ट कर सकते हैं

Isolation सही होने और throughput के बीच का समझौता है, इसलिए डेटाबेस transactions को दूसरों के कुछ असर देखने देते हैं। SQL मानक तीन phenomena के नाम देता है; PostgreSQL का documentation serialization anomalies जोड़ता है; और व्यवहार में टेस्टर जिससे सबसे ज़्यादा मिलते हैं, वह मानक की सूची में है ही नहीं: lost update।

• Dirty read — एक transaction ऐसा डेटा पढ़ता है जो दूसरे transaction ने लिखा है पर अभी commit नहीं किया (और जो rollback हो सकता है)।
• Non-repeatable read — एक transaction एक ही row दो बार पढ़ता है और अलग values पाता है, क्योंकि बीच में दूसरे transaction ने बदलाव commit कर दिया।
• Phantom read — एक transaction एक ही query दो बार चलाता है और rows का अलग सेट पाता है, क्योंकि दूसरे transaction ने मेल खाती rows insert या delete करके commit कर दीं।
• Serialization anomaly — committed concurrent transactions का नतीजा उन्हें एक-एक करके चलाने के किसी भी क्रम से मेल नहीं खाता (क्लासिक उदाहरण write skew: दो डॉक्टर on-call से हट जाते हैं क्योंकि हर एक ने दूसरे को अब भी on-call देखा)।
• Lost update — दो transactions एक ही value पढ़ते हैं, दोनों उससे नई value निकालते हैं, और दूसरी write चुपचाप पहली को मिटा देती है। नीचे की script इसे दो SQL sessions से दोहराती है।

-- Setup on a test database: one unit left
CREATE TABLE inventory (
  sku   VARCHAR(40) NOT NULL PRIMARY KEY,
  stock INTEGER     NOT NULL CHECK (stock >= 0)
);
INSERT INTO inventory (sku, stock) VALUES ('SKU-100', 1);

-- Step 1, session A
BEGIN;
SELECT stock FROM inventory WHERE sku = 'SKU-100';   -- reads 1

-- Step 2, session B
BEGIN;
SELECT stock FROM inventory WHERE sku = 'SKU-100';   -- reads 1
UPDATE inventory SET stock = 0 WHERE sku = 'SKU-100'; -- app computed 1 - 1
COMMIT;

-- Step 3, session A
UPDATE inventory SET stock = 0 WHERE sku = 'SKU-100'; -- app computed 1 - 1
COMMIT;

-- Two sales, one unit: B's update is lost.
-- PostgreSQL READ COMMITTED and MySQL InnoDB REPEATABLE READ: both commits succeed.
-- PostgreSQL REPEATABLE READ: step 3 fails with a serialization error; the app must retry.

Isolation levels: हर एक क्या रोकता है और engines कैसे अलग हैं

SQL मानक चार levels इस आधार पर परिभाषित करता है कि उन्हें तीन phenomena में से कौन-से रोकने हैं। ये न्यूनतम गारंटियाँ हैं, और हर engine इन्हें अलग तरह लागू करता है, इसलिए हमेशा अपने engine और वर्ज़न का documentation देखें।

PostgreSQL का डिफ़ॉल्ट READ COMMITTED है और वह कभी dirty read नहीं होने देता — उसका READ UNCOMMITTED, READ COMMITTED जैसा व्यवहार करता है। उसका REPEATABLE READ हर transaction को BEGIN जैसे transaction-control commands को छोड़कर उसके पहले statement की शुरुआत पर लिया गया snapshot देता है और phantom reads भी नहीं होने देता, जो मानक से ज़्यादा सख़्त है; अगर ऐसा transaction ऐसी row अपडेट करने की कोशिश करे जिसे किसी दूसरे transaction ने उसके snapshot के बाद बदलकर commit किया, तो वह serialization error से फ़ेल होता है, जिसे ऐप्लिकेशन को पकड़कर retry करना होता है। SERIALIZABLE इसके अलावा serialization anomalies पहचानता है और शामिल transactions में से एक को abort करता है।

MySQL के InnoDB engine का डिफ़ॉल्ट REPEATABLE READ है। Transaction में सामान्य SELECT पहली read पर बने consistent snapshot से पढ़ते हैं; locking reads (SELECT … FOR UPDATE या FOR SHARE), UPDATE और DELETE सबसे नई committed rows पर काम करते हैं और locks लेते हैं, जिनमें next-key (gap) locks भी हैं जो scan की गई range में inserts रोकते हैं। PostgreSQL के विपरीत, किसी दूसरे committed transaction से बदली row का update फ़ेल नहीं होता — वह सबसे नए वर्ज़न पर लागू होता है — इसलिए ऐप्लिकेशन कोड में read-then-write तब तक update खो सकता है जब तक वह locking read या atomic statement इस्तेमाल न करे। SQLite isolation अपने तरीक़े से लागू करता है, इसलिए PostgreSQL या MySQL जैसा व्यवहार मानने के बजाय उसका documentation देखें।

• READ UNCOMMITTED — मानक इस level पर तीनों phenomena की अनुमति देता है: dirty read, non-repeatable read और phantom read।
• READ COMMITTED — dirty read रोकता है; non-repeatable read और phantom read संभव रहते हैं।
• REPEATABLE READ — dirty read और non-repeatable read रोकता है; मानक के अनुसार phantom read संभव है।
• SERIALIZABLE — तीनों phenomena रोकता है।
• ये SQL मानक की न्यूनतम गारंटियाँ हैं; engines ज़्यादा रोक सकते हैं: PostgreSQL कभी dirty read नहीं होने देता, और उसका REPEATABLE READ phantoms भी रोकता है। Lost update मानक की तालिका में नहीं है; ऊपर engine नोट्स देखें। नीचे की तालिका चारों levels का अंग्रेज़ी शब्दों में सारांश है (possible = संभव, prevented = रोका गया)।

Isolation level    Dirty read   Non-repeatable read   Phantom read
READ UNCOMMITTED   possible     possible              possible
READ COMMITTED     prevented    possible              possible
REPEATABLE READ    prevented    prevented             possible
SERIALIZABLE       prevented    prevented             prevented

Concurrency tests जो हाथ से चलें और automate हों

सबसे सरल concurrency test के लिए दो SQL sessions और steps का लिखित क्रम चाहिए, जैसा ऊपर की script में है। यह race को निश्चित रूप से दोहराता है, जो bug report के लिए बिलकुल सही है। फिर असली ऐप्लिकेशन टेस्ट करें: प्रतिस्पर्धी requests API से parallel भेजें, छोटी script से या k6 या JMeter जैसे load tool से कुछ virtual users के साथ, और run को इतनी बार दोहराएँ कि वह window पकड़ में आ जाए।

जहाँ सिर्फ़ एक सफल हो सकता है, वहाँ concurrency test का expected result कभी “दोनों सफल” नहीं होता। ठीक एक request जीतती है; दूसरी को परिभाषित response मिलता है (conflict, out-of-stock message, या पारदर्शी retry); और बाद में डेटा मेल खाता है — stock कभी ऋणात्मक नहीं, coupon अपनी सीमा से ज़्यादा इस्तेमाल नहीं, और transfer में balances का योग अपरिवर्तित। Engines deadlocks पहचानकर एक transaction abort करते हैं; ऐप्लिकेशन को उसे retry करना चाहिए, और यूज़र को कभी 500 नहीं दिखना चाहिए।

• स्टॉक का आख़िरी item दो ग्राहक एक ही पल में ख़रीदते हैं।
• Submit पर double-click, या timeout के बाद client retry — idempotency key के साथ दूसरा request दूसरा ऑर्डर बनाने की जगह पहले का नतीजा लौटाता है।
• दो admins एक ही record edit करते हैं — optimistic locking के साथ दूसरा save चुपचाप overwrite करने की जगह conflict से reject होता है।
• सीमित coupon जिसे बहुत-से यूज़र एक साथ redeem करते हैं।
• एक ही दो खातों के बीच उलटी दिशाओं में transfers — क्लासिक deadlock पैटर्न।

-- Option 1: lock the row while deciding (PostgreSQL, MySQL; not SQLite)
BEGIN;
SELECT stock FROM inventory WHERE sku = 'SKU-100' FOR UPDATE;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'SKU-100';
COMMIT;

-- Option 2: one conditional, atomic statement; the app checks rows affected = 1
UPDATE inventory
SET stock = stock - 1
WHERE sku = 'SKU-100' AND stock >= 1;

-- Option 3: optimistic locking with a version column; 0 rows affected = conflict
UPDATE orders
SET status = 'SHIPPED', version = version + 1
WHERE order_id = 1042 AND version = 7;

Schema और data migrations: क्या गड़बड़ होता है

Schema migration ढाँचा बदलता है — टेबल, कॉलम, index या constraint जोड़ता है, कुछ rename या drop करता है। Data migration सामग्री बदलता है — नए कॉलम को backfill करता है, नाम फ़ील्ड को बाँटता है, currencies बदलता है। Flyway, Liquibase या framework के अपने migrations जैसे tools क्रमांकित scripts क्रम से लागू करते हैं और दर्ज करते हैं कि कौन-सी चलीं। Tool कोई भी हो, जोखिम वही हैं:

• Locks और table rewrites — कुछ operations, जैसे कॉलम का type बदलना या engine के online विकल्प के बिना index बनाना, टेबल को lock या rewrite करते हैं। Test database पर सेकंड production वॉल्यूम पर मिनट या घंटे बन सकते हैं। कोई operation कैसे व्यवहार करेगा, यह engine और उसके वर्ज़न पर निर्भर है।
• Data loss — कॉलम drop या संकरा करना, decimal scale घटाना, text को numbers में बदलना, या character set बदलना बिना error के values नष्ट या ख़राब कर सकता है।
• अधूरे backfills — script NULLs, असामान्य values, या अपने चलते समय बनी rows छोड़ देती है।
• Rollout के दौरान compatibility — rolling deployment में पुराना और नया ऐप्लिकेशन वर्ज़न एक ही schema पर एक साथ चलते हैं, इसलिए कॉलम rename करना पुराने वर्ज़न को तुरंत तोड़ देता है।
• Partial failure — MySQL DDL statements से पहले और बाद में implicit commit करता है, इसलिए बीच में फ़ेल हुआ multi-step migration schema को आधा बदला छोड़ देता है। PostgreSQL ज़्यादातर DDL transaction के अंदर चला सकता है, पर CREATE INDEX CONCURRENTLY जैसे कुछ statements transaction block के अंदर नहीं चल सकते।
• One-way steps — down script drop हुआ कॉलम फिर बना सकती है, पर उसमें रहा डेटा नहीं।

Migration कैसे टेस्ट करें

हर अहम migration का rehearsal production जैसे वॉल्यूम और आकार वाले डेटाबेस पर करें — वही engine और वर्ज़न, मिलती-जुलती row counts, मिलते-जुलते value distributions। इसे generated सिंथेटिक डेटा से बनाएँ, या production डेटा से सिर्फ़ ऐसी anonymisation प्रक्रिया के ज़रिए जिसे आपके data protection officer ने मंज़ूरी दी हो; सऊदी PDPL test environments में personal data पर भी लागू होता है। Migration का समय नापें और चलते समय blocked queries देखें, बेहतर हो कि उसी समय test traffic भी ऐप्लिकेशन पर आ रहा हो।

पहले और बाद में चलने वाली queries से पुष्टि करें: row counts, पैसे वाले कॉलमों के sums, status के हिसाब से distributions, और बदलाव से जुड़ी ख़ास जाँचें — backfill हुए कॉलम में कोई NULL न बचे, सिर्फ़ अनुमत values हों, constraints valid हों। “पहले” के नतीजे सेव करें ताकि तुलना सटीक हो।

Compatibility की योजना expand-and-contract पैटर्न (जिसे parallel change भी कहते हैं) से बनाएँ: पहले पुराना ढाँचा रखते हुए नया जोड़ें, दोनों में लिखें, backfill करें, reads को नए ढाँचे पर ले जाएँ, और पुराना ढाँचा सिर्फ़ बाद की release में हटाएँ। तब हर step backward-compatible रहता है, और आप नए schema पर पुराना ऐप्लिकेशन वर्ज़न टेस्ट कर सकते हैं। आख़िर में वापसी का रास्ता टेस्ट करें: rehearsal database पर down migration या restore प्रक्रिया चलाएँ, उसका समय नापें, और लिखें कि कौन-से steps one-way हैं।

-- Fingerprint: run before and after the migration and compare the results
SELECT 'orders' AS table_name, COUNT(*) AS row_count, SUM(total_amount) AS amount_sum FROM orders
UNION ALL
SELECT 'order_items', COUNT(*), SUM(quantity * unit_price) FROM order_items
UNION ALL
SELECT 'payments', COUNT(*), SUM(amount) FROM payments;

-- A distribution that must not change
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY status;

-- Example: after adding orders.currency with a backfill to 'SAR'
SELECT COUNT(*) AS missing_currency FROM orders WHERE currency IS NULL;

SELECT currency, COUNT(*) AS order_count
FROM orders
GROUP BY currency
ORDER BY currency;

आज़माएँ: SQL डेटासेट जनरेटर आज़माएँ: SQL क्वेरी वैलिडेटर

Migration test checklist

यह checklist हर उस migration के ticket में कॉपी करें जो डेटा बदलता है या बड़ी टेबल को छूता है। यह जान-बूझकर छोटी है; हर लाइन एक सवाल है जिसका जवाब release से पहले किसी को “हाँ” में दे सकना चाहिए।

MIGRATION TEST CHECKLIST: <migration id>

पहले
[ ] बदलाव का वर्गीकरण: schema, data, या दोनों
[ ] विनाशकारी steps पहचाने गए (DROP, संकरा type या length, data rewrite)
[ ] production जैसे वॉल्यूम वाला rehearsal database (सिंथेटिक या मंज़ूर anonymised डेटा)
[ ] fingerprint queries चलाईं और नतीजे सेव किए (counts, sums, distributions)
[ ] backup या point-in-time restore की पुष्टि; restore का समय ज्ञात

Rehearsal के दौरान
[ ] समय नापा; locks और blocked queries देखीं
[ ] migration चलते समय ऐप्लिकेशन traffic चालू
[ ] बीच में failure simulate किया; उसके बाद की state समझी

बाद में
[ ] fingerprint queries मेल खाती हैं, या ठीक उतनी अलग हैं जितना इरादा था
[ ] नए कॉलम भरे हुए; कोई अनपेक्षित NULL नहीं; constraints valid
[ ] पुराना ऐप्लिकेशन वर्ज़न नए schema पर काम करता है
[ ] नया ऐप्लिकेशन वर्ज़न smoke और integrity checks पास करता है
[ ] down migration या restore टेस्ट किया; one-way steps दर्ज
[ ] मुख्य queries के plans अपरिवर्तित या बेहतर

CI में transaction और migration tests का automation

डेटाबेस टेस्ट production वाले ही engine और major वर्ज़न पर चलाएँ, pipeline में नए सिरे से शुरू किए हुए — आम तौर पर container में। PostgreSQL या MySQL की जगह SQLite या in-memory डेटाबेस लगाना ठीक वही व्यवहार छिपा देता है जिसके बारे में यह पेज है: isolation, locking, transactions में DDL और constraint enforcement सब अलग हैं।

Pipeline को सारे migrations ख़ाली डेटाबेस पर लागू करने चाहिए, नए migrations पिछली released वर्ज़न पर seed किए डेटाबेस पर लागू करने चाहिए, concurrency cases सहित integration tests चलाने चाहिए, और read-only integrity queries के साथ ख़त्म होना चाहिए। बड़े वॉल्यूम वाले rehearsals ज़्यादा समय लेते हैं और आम तौर पर हर commit पर नहीं, बल्कि तय समय पर या release से पहले चलते हैं।

• शुरू से और पिछली release से, दोनों तरह के migrations सफल होते हैं।
• Down migrations, जहाँ टीम उन्हें सपोर्ट करती है, seed किए डेटा पर साफ़ चलते हैं।
• Concurrency tests सिर्फ़ response codes पर नहीं, अंतिम डेटा पर assert करते हैं।
• Suite के बाद integrity queries (orphans, duplicates, reconciliation) शून्य rows लौटाती हैं।
• Rehearsal में तय सीमा से ज़्यादा देर टेबल lock करने वाला migration release रोक देता है।

आज़माएँ: SQL INSERT जनरेटर आज़माएँ: बाउंड्री नंबर डेटासेट जनरेटर

अक्सर पूछे जाने वाले सवाल

संदर्भ

• Transactions — PostgreSQL DocumentationPostgreSQL Global Development Grouptransaction क्या है: कई क़दम जो BEGIN, COMMIT और ROLLBACK से सब-या-कुछ-नहीं लागू होते हैं, commit होने तक दूसरों को नहीं दिखते।
• Transaction Isolation — PostgreSQL DocumentationPostgreSQL Global Development GroupSQL isolation levels और वे किन घटनाओं की अनुमति देते हैं — dirty read, non-repeatable read, phantom, serialization anomaly — PostgreSQL में जैसे लागू हैं।
• Transaction Isolation Levels — MySQL 8.4 Reference ManualOracle (MySQL)चार SQL isolation levels जैसे InnoDB उन्हें लागू करता है, जिसमें REPEATABLE READ डिफ़ॉल्ट है।
• Transaction — SQLiteSQLiteSQLite transactions कैसे शुरू, commit और rollback करता है, deferred, immediate और exclusive मोड सहित।
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development Groupcheck, not-null, unique, primary key, foreign key और exclusion constraints — वे नियम जो डेटाबेस लागू करता है और टेस्टर जाँचता है।
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023अंतरराष्ट्रीय SQL मानक का framework भाग, जिसे PostgreSQL, MySQL, SQLite आदि की बोलियाँ अलग-अलग हद तक लागू करती हैं।
• Personal Data Protection Law (English translation)सऊदी डेटा एवं AI प्राधिकरण (SDAIA) · 2023व्यक्तिगत डेटा पर सऊदी कानून — टेस्ट वातावरण में असली ग्राहक रिकॉर्ड की प्रतियों के बजाय सिंथेटिक डेटा क्यों इस्तेमाल करना चाहिए।