READ COMMITTED & REPEATABLE READ

SQL Server में Transactions और Error Handling

Miriam Antona

Software Engineer

READ COMMITTED

डिफॉल्ट isolation level
दूसरी transaction द्वारा बदले गए पर अनकमीटेड डेटा नहीं पढ़ सकते

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

READ COMMITTED - isolation level तुलना

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes

READ COMMITTED - dirty reads रोकना

मूल बैलेंस अकाउंट 5 = $35,000

Transaction1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

Transaction2

...

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;

इंतज़ार करना पड़ेगा!

READ COMMITTED - dirty reads रोकना

मूल बैलेंस अकाउंट 5 = $35,000

Transaction1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

COMMIT TRAN;

Transaction2

...

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;

| current_balance |
|-----------------|
| 30000,00        |

READ COMMITTED - बिना रुके select करना

Transaction1

BEGIN TRAN
  SELECT current_balance
  FROM accounts WHERE account_id = 5;

Transaction2

...

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;

| current_balance |
|-----------------|
| 35000,00        |

READ COMMITTED - सारांश

Pros:

Dirty reads रोकता है

Cons:

Non-repeatable और phantom reads होने देता है
दूसरी transaction आपको ब्लॉक कर सकती है

कब उपयोग करें?:

आप सिर्फ committed डेटा पढ़ना चाहते हैं; non-repeatable और phantom reads स्वीकार हैं

REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

दूसरी transactions का uncommitted डेटा नहीं पढ़ सकते
एक बार डेटा पढ़ लिया तो REPEATABLE READ खत्म होने तक अन्य transactions उसे संशोधित नहीं कर सकतीं

REPEATABLE READ - isolation level तुलना

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes
REPEATABLE READ	no	no	yes

REPEATABLE READ - non-repeatable reads रोकना

Transaction1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
    SELECT current_balance FROM accounts
    WHERE account_id = 5;

| current_balance |
|-----------------|
| 35000,00        |

Transaction2

...

UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;

इंतज़ार करना पड़ेगा!

REPEATABLE READ - non-repeatable reads रोकना

Transaction1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
    SELECT current_balance FROM accounts
    WHERE account_id = 5;

    SELECT current_balance FROM accounts
    WHERE account_id = 5;

| current_balance |
|-----------------|
| 35000,00        |

COMMIT TRAN

Transaction2

...

UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;

इंतज़ार करना पड़ेगा!

REPEATABLE READ - non-repeatable reads रोकना

Transaction1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
    SELECT current_balance FROM accounts
    WHERE account_id = 5;

    SELECT current_balance FROM accounts
    WHERE account_id = 5;

COMMIT TRAN

Transaction2

...

UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;

(1 rows affected)

REPEATABLE READ - सारांश

Pros:

जिस डेटा को आप पढ़ रहे हैं, उसे दूसरी transactions बदल नहीं सकतीं; non-repeatable reads रुकते हैं
Dirty reads रोकता है

Cons:

Phantom reads होने देता है
REPEATABLE READ transaction आपको ब्लॉक कर सकती है।

कब उपयोग करें?:

आप सिर्फ committed डेटा पढ़ना चाहते हैं और नहीं चाहते कि अन्य transactions उसे बदलें। Phantom reads स्वीकार हैं

अभ्यास करते हैं!

SQL Server में Transactions और Error Handling