Lesson 19 � Advanced

TRANSACTIONS &
ACID.

Transaction ek unit of work hai � ya toh sab changes hote hain, ya koi nahi. Banking mein paisa transfer hota hai, e-commerce mein order place hota hai � sab transactions se hota hai. ACID properties ensure karti hain ki data safe rahe, chahe system crash ho ya power jaaye.

? 20 min✓ Advanced✓ Prerequisite: Constraints

WHY: Transaction kyun zaroori hai?

Socho tum bank mein ho. Tum 1000 rupaye transfer kar rahe ho A se B ko. Internally do cheezein hoti hain � A ke account se 1000 kat-ta hai, B ke account mein 1000 add hota hai. Agar beech mein system crash ho jaaye aur sirf ek hi cheez hoti hai � toh paise gayab! Transaction ensure karta hai ki ya toh dono cheezein hoti hain, ya koi nahi.

BEGIN

Transaction start karo. Jab tum BEGIN likhte ho, database samajhta hai ki ab kuch changes aane wale hain jo ek saath commit ya rollback honge.

COMMIT

Sab changes save karo database mein. COMMIT ke baad changes permanent ho jaate hain � wapas nahi aa sakte.

ROLLBACK

Changes undo karo. Galat ho gaya✓ ROLLBACK dabao � sab wapas aa jaayega jaisa pehle tha. Transaction ke start se pehle ka state.

ACID

Four properties jo data safety ensure karti hain � Atomicity, Consistency, Isolation, Durability. Har transaction yeh four rules follow karta hai.

HOW: Transaction likhna aur use karna

Transaction banana bahut simple hai � BEGIN se shuru karo, apne queries likho, phir COMMIT karo ya ROLLBACK karo agar galti ho gayi.

1. Basic transaction � Bank transfer

Ek account se doosre account mein paisa transfer karo. Dono updates ek saath honi chahiyein:

-- Bank transfer: A se B ko 1000 rupaye
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;

ℹ

Mental model: BEGIN se transaction shuru hota hai. Dono UPDATE queries ek saath execute hoti hain. Agar dono ho gayi toh COMMIT se save ho jaati hain. Agar beech mein kuch gadbad ho toh ROLLBACK se sab undo ho jaata hai.

2. Rollback on error

Galati se DELETE kar diya✓ ROLLBACK se wapas lao:

-- Oh no! Wrong delete!
BEGIN;

DELETE FROM students WHERE id = 1;

-- Ruk jaao! Ye galat hai!
ROLLBACK;

ℹ

Key point: ROLLBACK karne pe woh data wapas aa jaata hai jo transaction start hone se pehle tha. Jaise tumne kuch kiya hi nahi. Lekin yaad rakho � COMMIT ke baad ROLLBACK kaam nahi karta. COMMIT = permanent.

3. SAVEPOINT � Partial rollback

Kabhi kabhi tumhe sirf kuch cheezein undo karni hoti hain, sab nahi. SAVEPOINT se partial rollback kar sakte ho:

-- Partial rollback with SAVEPOINT
BEGIN;

INSERT INTO students VALUES (1, 'Aman', 22);
SAVEPOINT sp1;

INSERT INTO students VALUES (2, 'Priya', 21);

-- Priya rollback karo, Aman save rakhna hai
ROLLBACK TO sp1;

-- Sirf Aman saved hai
COMMIT;

ℹ

SAVEPOINT: Transaction ke andar checkpoint banao. ROLLBACK TO sp1 se sirf sp1 ke baad ki cheezein undo hoti hain. Pehle ki cheezein safe rehti hain. Bahut useful hai jab tumhe multiple operations mein se kuch undo karne hon.

4. SAVEPOINT release karo

SAVEPOINT ki zaroorat khatam ho jaaye toh release karo:

-- SAVEPOINT release karo
BEGIN;

INSERT INTO students VALUES (1, 'Aman', 22);
SAVEPOINT sp1;

INSERT INTO students VALUES (2, 'Priya', 21);
SAVEPOINT sp2;

-- sp1 ki zaroorat nahi, release karo
RELEASE SAVEPOINT sp1;

-- ab sirf sp2 pe rollback kar sakte ho
ROLLBACK TO sp2;

COMMIT;

5. Transaction isolation levels

Multiple transactions ek saath chalte hain toh kaise handle hota hai:

-- Isolation level dekho
SHOW VARIABLES LIKE 'transaction_isolation';

-- READ UNCOMMITTED: dirty reads allowed
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';

-- READ COMMITTED: sirf committed data dikhta hai
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- REPEATABLE READ: same query same result deti hai
SET SESSION transaction_isolation = 'REPEATABLE-READ';

-- SERIALIZABLE: sabse strict � locks laga deta hai
SET SESSION transaction_isolation = 'SERIALIZABLE';

ℹ

Isolation levels: READ UNCOMMITTED sabse loose hai � dirty reads hoti hain. SERIALIZABLE sabse strict hai � ek transaction chalte waqt doosri wait karti hai. Default usually REPEATABLE READ hota hai MySQL mein.

6. Transaction autocommit

MySQL mein har query automatically commit hoti hai. Isko band karo:

-- Autocommit dekho
SHOW VARIABLES LIKE 'autocommit';

-- Autocommit band karo
SET autocommit = 0;

-- Ab tumhe manually COMMIT karna padega
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;

-- Autocommit wapas on karo
SET autocommit = 1;

ACID Properties � Data ki security guard

ACID chaar properties hain jo ensure karti hain ki transaction safe ho. Banking, e-commerce � har jagah yeh rules follow hote hain.

-- ACID Properties samjho

-- A = Atomicity: All or Nothing
-- Ya toh saari queries hoti hain, ya koi nahi
-- Example: Bank transfer mein dono updates ek saath
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
-- Agar beech mein crash ho ✓ ROLLBACK ✓ dono nahi hoti
-- Agar sab theek hai ✓ COMMIT ✓ dono hoti hain
COMMIT;

-- C = Consistency: Valid state after transaction
-- Transaction ke baad database consistent hona chahiye
-- Example: Total balance same rehna chahiye
-- Pehle: A=5000, B=3000 ✓ Total = 8000
-- Baad: A=4000, B=4000 ✓ Total = 8000 ?
-- Agar Total = 7000 ya 9000 hota ✓ CONSISTENCY BREAK

-- I = Isolation: Concurrent transactions don't interfere
-- Do transactions ek saath chalein toh ek doosre ko affect na karein
-- Transaction 1: A se 1000 kat raha hai
-- Transaction 2: B se 500 kat raha hai
-- Dono alag-alag handle honi chahiyein � ek doosre ko block na karein

-- D = Durability: Committed data survives crashes
-- COMMIT ke baad data permanent hai
-- System crash ho, power jaaye � data safe hai
-- Database log files mein save hota hai jo crash ke baad bhi recover hota hai

Real-world example: E-commerce order

E-commerce mein order place karte waqt kya hota hai � multiple tables update hoti hain:

-- E-commerce order transaction
BEGIN;

-- Step 1: Order create karo
INSERT INTO orders (customer_id, total, status)
VALUES (101, 2500, 'PLACED');

-- Step 2: Order items add karo
INSERT INTO order_items (order_id, product_id, qty, price)
VALUES (LAST_INSERT_ID(), 5, 2, 1250);

-- Step 3: Stock update karo
UPDATE products SET stock = stock - 2 WHERE id = 5;

-- Step 4: Payment record karo
INSERT INTO payments (order_id, amount, method)
VALUES (LAST_INSERT_ID(), 2500, 'UPI');

-- Sab ho gaya✓ COMMIT!
COMMIT;

-- Agar kisi step mein error aaya?
-- ROLLBACK karo � sab undo
-- Order nahi bana, stock wapas aaya, payment nahi hui

Try it: Bank transfer transaction banao

Neeche ke editor mein transaction practice karo. Bank transfer example likho aur dekho kaise COMMIT aur ROLLBACK kaam karta hai.

SQL playgroundTransactions practice karo � bank transfer example

query.sql

Run Query dabayein

Quick check

Ek transaction likho jo 2 updates ek saath kare � table t mein id 1 ke liye val=1 aur id 2 ke liye val=2 set karo. Transaction mein ROLLBACK option bhi hona chahiye agar galti ho jaaye.

BEGIN se shuru karo, phir dono UPDATE queries likho � UPDATE t SET val=1 WHERE id=1 aur UPDATE t SET val=2 WHERE id=2. Phir COMMIT karo. Agar galti ho toh ROLLBACK se sab undo ho jaayega.

Common transaction mistakes

COMMIT bhool jaana: Transaction start karke queries likh di lekin COMMIT nahi kiya. Abhi tak changes database mein save nahi hain � sirf tumhare session mein hain. Dusre users ko dikhenge nahi jab tak COMMIT na ho.
ROLLBACK ke baad kaam karna: ROLLBACK ke baad woh changes gayab ho gayi hain. Agar tumne ROLLBACK kiya aur phir wohi queries dobara likhi toh naya transaction shuru hoga � pehla wala khatam ho chuka hai.
Autocommit na samajhna: MySQL mein default autocommit ON hai � har query automatically commit hoti hai. Agar tumne SET autocommit = 0 nahi kiya toh tumhara har UPDATE turant save ho raha hai bina transaction ke.
Isolation level na samajhna: Agar do transactions ek saath chal rahi hain aur tum READ UNCOMMITTED pe ho toh dirty reads ho sakti hain � tumhein doosre transaction ka uncommitted data dikh sakta hai jo baad mein rollback ho sakta hai.
Large transactions mein locks: Bahut badi transaction mein bahut der tak locks lagte hain � doosre users wait karte hain. Transactions chhoti rakho, zyada fast.

Transactions clear hai?

Ab Practical Projects par chalo � real-world SQL problems solve karo aur apne skills apply karo.

TRANSACTIONS &ACID.