Intermediate LevelSQL & MySQL
How do SAVEPOINT and ROLLBACK TO SAVEPOINT handle nested transaction rollbacks?
❓ Interview Prompt: Explain `SAVEPOINT`, `ROLLBACK TO SAVEPOINT`, `RELEASE SAVEPOINT`, and partial transaction rollbacks.
Savepoints establish intermediate checkpoints inside a transaction, allowing specific operations to be rolled back without aborting the entire transaction:
Commands:
•
'SAVEPOINT point_name': Creates a savepoint marker.
•
'ROLLBACK TO SAVEPOINT point_name': Rolls back all statements executed after the marker.
•
'RELEASE SAVEPOINT point_name': Destroys a savepoint without rolling back.
Code Implementation Example
sql
START TRANSACTION;
INSERT INTO logs (message) VALUES ('Processing batch');
SAVEPOINT batch_start;
-- Attempt risky batch operation
UPDATE accounts SET balance = balance - 500 WHERE id = 99;
-- Rollback ONLY the risky update if an error occurs
ROLLBACK TO SAVEPOINT batch_start;
-- Commit the remaining transaction (logs insert succeeds!)
COMMIT;💡Executing partial transaction rollbacks with SAVEPOINT.
🎯 Interview Key Takeaway
Use SAVEPOINT to perform partial transaction rollbacks without aborting the main transaction.
#SQL#SAVEPOINT#ROLLBACK#Transactions#Intermediate
Back to all SQL & MySQL Questions→