MySQL + SQL · Lesson 86

MySQL में Transaction Isolation Levels

Transaction Isolation Level क्या है?

Isolation वह ACID property है जो control करती है कि concurrent transactions shared data को कैसे observe और affect करें। Isolation level reproducible reads, lock behavior, concurrency और overhead के बीच defined balance चुनता है। इसका simple अर्थ “safe versus unsafe” या “fast versus slow” नहीं है।

Faculty definition: Transaction isolation level specify करता है कि other concurrent transactions के कौन-से effects visible हो सकते हैं और कौन-से conflicting operations wait करेंगे।

InnoDB चारों SQL isolation levels support करता है: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ और SERIALIZABLE। Default REPEATABLE READ है।

Reproducible Two-Session Lab

Table once test transactions के बाहर बनाएँ। फिर Session A और Session B नाम से two MySQL connections खोलें।

DROP TABLE IF EXISTS isolation_demo;
CREATE TABLE isolation_demo (
  item_id INT PRIMARY KEY,
  item_name VARCHAR(40) NOT NULL,
  quantity INT NOT NULL,
  INDEX idx_quantity (quantity)
) ENGINE = InnoDB;

INSERT INTO isolation_demo VALUES
(1, 'Notebook', 100),
(2, 'Pen',       20);
COMMIT;

1 | Notebook | 100 2 | Pen | 20

Every independent experiment से पहले quantity 100 reset करके commit करें। Timing important है, इसलिए labelled statements shown session order में execute करें।

Dirty Read, Nonrepeatable Read और Phantom

Phenomenon	क्या बदलता है?	Simple example
Dirty read	Uncommitted value observe	B, A का 150 देखती है; A rollback
Nonrepeatable read	Same row different committed value देता है	B 100, A commits 110, B 110
Phantom	Same predicate different row set देता है	B searches के बीच A qualifying row insert
Lost update	One writer another decision overwrite	Two apps same old stock से calculate

Dirty, nonrepeatable और phantom reads visibility describe करती हैं। Lost updates correct update patterns, locking या optimistic version checks से रोकें; isolation label application design का replacement नहीं।

चार InnoDB Isolation Levels

Level	Plain consistent-read behavior	Important InnoDB point
READ UNCOMMITTED	Earlier या uncommitted version expose हो सकता है	Dirty reads possible; otherwise broadly READ COMMITTED जैसा
READ COMMITTED	Every consistent read fresh snapshot	Record locking reduced; gap locking mainly FK और duplicate-key checks
REPEATABLE READ	First read से established snapshot reuse	Default; range locking reads gap या next-key locks use कर सकती हैं
SERIALIZABLE	Autocommit disabled हो तो plain reads पर stricter locking	Plain SELECT उस case में implicitly SELECT FOR SHARE जैसी

Common chart correction: “SERIALIZABLE always slowest” usable engineering rule नहीं। Cost workload, contention, predicates, indexes और transaction length पर निर्भर है। Actual system measure करें।

दो Verified Snapshot Experiments

READ COMMITTED: second read को fresh snapshot

-- Session B
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT quantity FROM isolation_demo WHERE item_id = 1;
-- 100

-- B की reads के बीच Session A
START TRANSACTION;
UPDATE isolation_demo SET quantity = 110 WHERE item_id = 1;
COMMIT;

-- Session B
SELECT quantity FROM isolation_demo WHERE item_id = 1;
-- 110
COMMIT;

Session B: first read=100, second read=110

REPEATABLE READ: plain reads one snapshot reuse

-- पहले quantity=100 reset और commit करें।
-- Session B
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT quantity FROM isolation_demo WHERE item_id = 1;
-- 100; snapshot established।

-- Session A
START TRANSACTION;
UPDATE isolation_demo SET quantity = 110 WHERE item_id = 1;
COMMIT;

-- Session B
SELECT quantity FROM isolation_demo WHERE item_id = 1;
-- Consistent-read snapshot में अभी 100।
COMMIT;

Session B: first read=100, second plain read=100

SELECT FOR UPDATE जैसी locking read current version पढ़कर locks लेती है, old consistent-read snapshot नहीं। One REPEATABLE READ transaction में locking और nonlocking reads casually mix न करें क्योंकि वे different database states represent कर सकती हैं।

Global, Session या Next-Transaction Scope

-- Current session value देखें।
SELECT @@SESSION.transaction_isolation;

-- इस session की every subsequent transaction।
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- केवल next transaction; active transaction के बाहर।
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT COUNT(*) FROM isolation_demo;
COMMIT;

-- Future sessions का server default; admin privilege required।
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

GLOBAL change subsequent connections को affect करता है, existing sessions को नहीं।
SESSION change उस connection की subsequent transactions को affect करता है, active transaction को नहीं।
Unqualified SET TRANSACTION केवल next transaction पर लागू।
Next-transaction scope START TRANSACTION से पहले set करें।
Connection pool checkout पर session settings deliberately establish करें।

Isolation Level कैसे चुनें?

Workload need	Starting consideration
General InnoDB OLTP	Evidence के बिना REPEATABLE READ रखें
Each report statement पर fresh committed view	READ COMMITTED evaluate करें
Value read करके modify करना	Suitable locking read या atomic conditional update
Queue workers	Queue-like data पर ही SKIP LOCKED consider करें
Specialized unit के लिए strict serial behavior	SERIALIZABLE evaluate और contention measure
Approximate report जहाँ dirty values सच में acceptable	Explicit risk review के बाद READ UNCOMMITTED

Business rule violate करने वाली anomaly define करें।
Isolation, locks और constraints का minimum correct combination चुनें।
Transactions small और predicates indexed रखें।
Single-user data नहीं, two concurrent sessions से test करें।
Deadlocks और lock timeouts explicitly handle करें।
Defaults बदलने से पहले representative production volume benchmark करें।

Exam Answer और Practical Tasks

Model answer: Isolation level concurrent transaction changes की visibility तय करता है। InnoDB READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ और SERIALIZABLE support करता है। READ UNCOMMITTED dirty reads permit कर सकता है। READ COMMITTED per statement fresh consistent-read snapshot बनाता है। REPEATABLE READ, InnoDB default, consistent reads के लिए first-read snapshot reuse करता है। SERIALIZABLE stricter locking behavior apply करता है। Correct choice required anomalies, locking और workload पर निर्भर है।

अभ्यास: one dirty read reproduce करके rollback करें; READ COMMITTED और REPEATABLE READ में two reads compare करें; two count queries के बीच range-matching row insert करें; फिर locking range read से repeat करके observe करें कि insert wait करती है या नहीं।

अब concurrency control और locks तथा deadlocks पढ़ें।

Official संदर्भ

Snapshot, locking और scope behavior official MySQL 8.4 manual से check किया गया है।

अक्सर पूछे जाने वाले प्रश्न (FAQ)

MySQL 8.4 में default InnoDB isolation level क्या है?

REPEATABLE READ default है। One transaction की plain consistent reads first consistent read से established snapshot use करती हैं।

READ COMMITTED और REPEATABLE READ में क्या अंतर है?

READ COMMITTED में each consistent read fresh snapshot लेती है। REPEATABLE READ में transaction की consistent reads सामान्यतः first-read snapshot reuse करती हैं।

क्या READ UNCOMMITTED ऐसा data दिखा सकती है जो बाद में rollback हो जाए?

हाँ। Dirty read दूसरे transaction का uncommitted version देख सकती है, इसलिए rollback के बाद वह value disappear हो सकती है।

क्या higher isolation level हर application को automatically correct बनाता है?

नहीं। Isolation concurrency visibility और locking control करता है। Correct boundaries, constraints, affected-row checks और application rules फिर भी जरूरी हैं।

SET TRANSACTION ISOLATION LEVEL कब execute करना चाहिए?

SESSION या GLOBAL के बिना यह केवल next transaction configure करता है और active transaction के बाहर देना होता है। SET SESSION TRANSACTION subsequent session transactions पर लागू होता है।

💻 लाइव कोड एडिटर

इस पेज के प्रोग्राम यहीं तैयार हैं — चलाएँ, बदलें और सीखें। कुछ भी इंस्टॉल किए बिना।

OneCompiler द्वारा संचालित। कोड एडिटर में अपने आप आ जाता है — Run दबाकर आउटपुट देखें। अगर एडिटर न खुले तो नए टैब में खोलें.