DBMS और MySQL में ACID Properties

ACID का क्या अर्थ है?

ACID database transactions के लिए reliability model है: Atomicity, Consistency, Isolation और Durability। यह समझाती है कि statement fail होने, two users द्वारा same time data update करने, application disconnect या server crash में database का behavior क्या होना चाहिए।

ACID SQL पर magic label नहीं है। Reliable behavior InnoDB जैसे transactional engine, correct schema constraints, complete application transaction, suitable isolation और durable server configuration से आता है।

चार ACID Properties एक नज़र में

Memorable sequence: all-or-nothing, valid-to-valid, concurrent-but-controlled, committed-and-recoverable।

Verified Bank-Transfer Case Study

-- Opening balances
101 Asha  = 5000.00
102 Ravi  = 3000.00
103 Meera = 1200.00
Total     = 9200.00

START TRANSACTION;
SELECT account_id, balance
FROM bank_accounts
WHERE account_id IN (101, 102)
ORDER BY account_id
FOR UPDATE;

UPDATE bank_accounts
SET balance = balance - 1000.00
WHERE account_id = 101 AND balance >= 1000.00;

UPDATE bank_accounts
SET balance = balance + 1000.00
WHERE account_id = 102;

INSERT INTO transfer_ledger
VALUES (9001, 101, 102, 1000.00, 'COMMITTED', CURRENT_TIMESTAMP);

COMMIT;

Example तभी ACID बनती है जब application every affected-row count check करे और any failure पर rollback करे। Decision logic के लिए full safe transfer workflow पढ़ें।

A — Atomicity: All or Nothing

Atomicity transaction को one indivisible logical unit मानती है। Debit के बाद credit fail हो तो ROLLBACK को uncommitted debit भी remove करनी चाहिए। InnoDB transaction और undo information से uncommitted changes reverse कर सकता है।

START TRANSACTION;
UPDATE bank_accounts SET balance = balance - 500
WHERE account_id = 101;
-- Next required operation fail मान लें।
ROLLBACK;

-- Account 101 pre-transaction value पर वापस।

• Atomicity यह नहीं: every business task one enormous SQL statement में लिखना।
• Atomicity requires: correct boundaries और full rollback path।
• Important limitation: nontransactional table changes fully roll back नहीं होते।
• DDL caution: अनेक schema statements implicit commit कराती हैं; business DML से बाहर रखें।

C — Consistency: Valid State से Valid State

Consistency का अर्थ successful transaction database integrity rules और application business invariants respect करे। DBMS primary keys, foreign keys, unique rules, NOT NULL और CHECK constraints enforce कर सकता है। जो rules declared नहीं या complex process में फैले हों, application उन्हें enforce करे।

balance DECIMAL(12,2) NOT NULL
  CHECK (balance >= 0)

amount DECIMAL(12,2) NOT NULL
  CHECK (amount > 0)

FOREIGN KEY (from_account)
  REFERENCES bank_accounts(account_id)

Common misconception: ACID incorrect business logic को correct नहीं बनाती। Program wrong rule दे तो perfectly atomic transaction wrong amount commit कर सकती है।

I — Isolation: Controlled Concurrency

Isolation control करती है कि other transactions active होने पर one transaction क्या observe करती है। InnoDB multi-version concurrency control, isolation levels और locks combine करता है। MySQL InnoDB READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ और SERIALIZABLE support करता है; REPEATABLE READ default InnoDB level है।

START TRANSACTION;
SELECT account_id, balance
FROM bank_accounts
WHERE account_id IN (101, 102)
ORDER BY account_id
FOR UPDATE;
-- Matching records conflicting changes के लिए
-- COMMIT या ROLLBACK तक locked।

Isolation का अर्थ “no concurrency” नहीं। इसका अर्थ concurrent effects defined rules follow करें। Workload के लिए isolation level select करें और value read करके later update decision लेना हो तो locking reads use करें।

D — Durability: Committed Data Recovery में Survive

COMMIT succeed होने के बाद durability का अर्थ committed result server failure के बाद recoverable रहे। InnoDB redo log, doublewrite mechanism और crash recovery इस property में योगदान देते हैं। Replicated या point-in-time recovery के लिए binary-log और storage configuration भी matter करती है।

• innodb_flush_log_at_trx_commit redo-log flush behavior affect करता है।
• sync_binlog binary-log synchronization affect करता है।
• Storage write caches, OS guarantees और power protection failure behavior influence करते हैं।
• Backups और tested restores one transaction से आगे deletion, corruption, operational mistakes और disaster से protect करते हैं।

Exam Answer, Misconceptions और अभ्यास

Five-mark model answer: ACID properties DBMS transactions reliable बनाती हैं। Atomicity transaction को all-or-nothing बनाती है। Consistency database को integrity rules satisfy करने वाली states के बीच move करती है। Isolation isolation level के अनुसार concurrent transactions का interference control करती है। Durability crash recovery में committed results preserve करती है। MySQL में InnoDB locking, MVCC, undo, redo और recovery जैसे transactional mechanisms देता है, जबकि schema और application को correct constraints तथा transaction boundaries define करनी होती हैं।

अभ्यास: ACID को school fee payment, online order और seat booking पर map करें। हर case में one invariant, one concurrent race, one rollback point और one durability requirement पहचानें।

Official संदर्भ

• MySQL 8.4: InnoDB and the ACID Model
• MySQL 8.4: InnoDB Transaction Isolation Levels
• MySQL 8.4: The InnoDB Storage Engine

Definitions और MySQL implementation notes official MySQL 8.4 manual से check किए गए हैं।