COMMIT, ROLLBACK और SAVEPOINT
COMMIT, ROLLBACK और SAVEPOINT
ये transaction control statements तय करती हैं कि active work में कितना रखा जाए। COMMIT complete transaction accept करता है, ROLLBACK उसका all pending work reject करता है और savepoint transaction end किए बिना partial recovery के लिए named position बनाता है।
| Statement | Effect | Transaction active? |
|---|---|---|
| COMMIT | Current changes permanent | नहीं |
| ROLLBACK | All current uncommitted changes undo | नहीं |
| SAVEPOINT sp | Named marker sp create या replace | हाँ |
| ROLLBACK TO sp | sp के बाद के changes undo | हाँ |
| RELEASE SAVEPOINT sp | केवल marker sp delete | हाँ |
Verified Student-Result Lab
Transaction experiments से पहले DDL और seed rows run करें। Predictable rollback behavior के लिए InnoDB आवश्यक है।
DROP TABLE IF EXISTS student_results;
CREATE TABLE student_results (
student_id INT PRIMARY KEY,
student_name VARCHAR(60) NOT NULL,
marks DECIMAL(5,2) NOT NULL,
CONSTRAINT chk_marks CHECK (marks BETWEEN 0 AND 100)
) ENGINE = InnoDB;
INSERT INTO student_results VALUES
(1, 'Aarav', 80.00),
(2, 'Meera', 85.00),
(3, 'Sana', 90.00);Every independent example से पहले seed values reset करें, या examples displayed order में चलाकर each checkpoint observe करें।
COMMIT: Complete Unit Permanently Accept
START TRANSACTION;
UPDATE student_results
SET marks = marks + 5
WHERE student_id = 1;
SELECT student_id, marks
FROM student_results
WHERE student_id = 1;
-- Current session को 85.00 दिखता है।
COMMIT;
SELECT student_id, marks
FROM student_results
WHERE student_id = 1;
-- 85.00 अब committed है।COMMIT current transaction end करता है, savepoints delete और InnoDB locks release करता है। Successful commit के बाद later ROLLBACK उस completed transaction में वापस नहीं जा सकती।
Full ROLLBACK: Every Pending Change Reject
यह example temporarily two rows change करती है, session की provisional view check करती है, फिर दोनों changes cancel करती है।
START TRANSACTION;
UPDATE student_results
SET marks = 40.00
WHERE student_id = 2;
UPDATE student_results
SET marks = 45.00
WHERE student_id = 3;
SELECT student_id, marks
FROM student_results
WHERE student_id IN (2, 3)
ORDER BY student_id;
-- Current session: Meera=40.00, Sana=45.00.
ROLLBACK;
SELECT student_id, marks
FROM student_results
WHERE student_id IN (2, 3)
ORDER BY student_id;Single statement error का universal meaning यह नहीं कि MySQL ने entire transaction rollback कर दी। Application failure detect करके full ROLLBACK दे, जब तक complete transaction already ended होना known न हो।
SAVEPOINT: केवल Later Work Correct करें
मान लें Aarav और Meera updates correct हैं, लेकिन Sana accidentally 92 के बजाय 50 कर दी गई। Savepoints earlier valid work preserve करने देती हैं।
START TRANSACTION;
UPDATE student_results SET marks = 85.00
WHERE student_id = 1;
SAVEPOINT after_aarav;
UPDATE student_results SET marks = 95.00
WHERE student_id = 2;
SAVEPOINT after_meera;
UPDATE student_results SET marks = 50.00
WHERE student_id = 3; -- logical mistake
ROLLBACK TO SAVEPOINT after_meera;
-- Sana 90.00 पर; earlier updates pending remain।
UPDATE student_results SET marks = 92.00
WHERE student_id = 3;
RELEASE SAVEPOINT after_meera;
COMMIT;ROLLBACK TO ने केवल mistaken Sana update undo की। उसने Aarav या Meera commit नहीं किए और transaction end नहीं की। Final COMMIT ने तीनों corrected results accept किए।
MySQL में Exact SAVEPOINT Rules
- Savepoint केवल current transaction के अंदर exist करती है।
- Existing name से savepoint बनाने पर old marker delete और new set होता है।
- ROLLBACK TO named savepoint उस point को रखती है लेकिन उसके बाद created savepoints delete करती है।
- ROLLBACK TO marker के बाद row modifications undo करती है। InnoDB marker के बाद memory में stored every row lock आवश्यक रूप से release नहीं करता।
- RELEASE SAVEPOINT केवल marker remove करती है; table data नहीं बदलता।
- COMMIT या full ROLLBACK transaction के all savepoints delete करते हैं।
- Unknown savepoint name error 1305 देता है।
START TRANSACTION;
SAVEPOINT review_point;
UPDATE student_results SET marks = 86 WHERE student_id = 1;
SAVEPOINT review_point;
-- First review_point second से replace हो गया।
ROLLBACK TO review_point;
COMMIT;after_header या before_optional_items जैसे descriptive names लें। Savepoints recovery markers हैं, every statement validation का substitute नहीं।
Autocommit, DDL और Other Traps
Autocommit enabled और explicit transaction absent हो तो successful UPDATE immediately commit होती है। इसलिए savepoint को active transaction चाहिए।
SELECT @@autocommit; -- सामान्यतः 1
START TRANSACTION;
UPDATE student_results SET marks = 88 WHERE student_id = 1;
SAVEPOINT after_first_update;
-- Continue, marker तक rollback या explicitly finish करें।- Savepoint के बाद interactive transaction open न छोड़ें।
- Already committed work ROLLBACK undo कर सकती है, यह assume न करें।
- Recoverable work के लिए nontransactional tables न लें।
- RELEASE SAVEPOINT को COMMIT न समझें।
- Savepoint names untrusted user input से directly expose न करें।
कौन-सा Command कब लें?
| Situation | Command |
|---|---|
| All required statements और checks succeed | COMMIT |
| Critical step fail और pending work नहीं रखना | ROLLBACK |
| Earlier pending work valid, later optional work wrong | ROLLBACK TO SAVEPOINT, correct, फिर COMMIT decision |
| Marker अब required नहीं | RELEASE SAVEPOINT |
| Work already committed, business correction चाहिए | Auditable compensating transaction बनाएँ |
अभ्यास: savepoint के बाद fourth student add करके केवल insert rollback करें; two savepoints बनाएँ और earlier पर rollback करके later marker deletion prove करें; full ROLLBACK test करें; फिर second database session से final rows verify करें।
इन commands को reliability से जोड़ने के लिए broader transactions lesson और ACID properties lesson revise करें।
Official संदर्भ
- MySQL 8.4: SAVEPOINT, ROLLBACK TO and RELEASE SAVEPOINT
- MySQL 8.4: COMMIT and ROLLBACK Statements
- MySQL 8.4: Statements That Cause an Implicit Commit
Command behavior और savepoint lifecycle official MySQL 8.4 manual से check किए गए हैं।