SQL

ROLLBACK in SQL

↩️ The ROLLBACK statement is used to undo all uncommitted changes made during the current transaction. If an error occurs or a transaction cannot be completed successfully, ROLLBACK restores the database to the state it was in before the transaction began.

📖 What is ROLLBACK?

A transaction groups multiple SQL statements into a single logical unit of work. If any statement within the transaction fails, the ROLLBACK statement cancels all changes made since the transaction started, ensuring the database remains consistent.

Information

ROLLBACK only reverses changes that have not yet been committed. Once a transaction has been committed using COMMIT, its changes become permanent and cannot be undone by ROLLBACK.

🎯 Why Use ROLLBACK?

ROLLBACK protects data integrity by preventing incomplete or incorrect updates from being permanently stored.

📌 Undo failed transactions.
📌 Prevent partial database updates.
📌 Recover safely from errors.
📌 Maintain database consistency.
📌 Support reliable business operations.

💡 Real-World Example

Consider transferring $500 between two bank accounts:

Withdraw $500 from Account A.
Deposit $500 into Account B.

If the deposit fails because of an unexpected error, the withdrawal must also be canceled. Executing ROLLBACK restores both accounts to their original balances.

📝 Basic Syntax

ROLLBACK Syntax

BEGIN TRANSACTION;

-- SQL statements

ROLLBACK;

➖ Example: Undo an UPDATE

Rollback an UPDATE

BEGIN TRANSACTION;

UPDATE Employees
SET Salary = Salary + 5000
WHERE EmployeeID = 101;

ROLLBACK;

Since the transaction is rolled back, the employee's salary remains unchanged.

➖ Example: Roll Back Multiple Statements

A rollback reverses every uncommitted statement within the transaction.

Rollback Multiple Operations

BEGIN TRANSACTION;

INSERT INTO Orders
(OrderID, CustomerID, Amount)
VALUES
(1001, 25, 750);

UPDATE Inventory
SET Quantity = Quantity - 1
WHERE ProductID = 50;

ROLLBACK;

Neither the new order nor the inventory update is saved because the entire transaction is canceled.

📍 Rolling Back to a Savepoint

Many database systems support SAVEPOINT, allowing you to undo only part of a transaction instead of canceling the entire transaction.

Rollback to a Savepoint

BEGIN TRANSACTION;

INSERT INTO Orders (...)
VALUES (...);

SAVEPOINT OrderCreated;

UPDATE Inventory
SET Quantity = Quantity - 1;

ROLLBACK TO SAVEPOINT OrderCreated;

COMMIT;

Important

Savepoint syntax differs among database systems. Some databases use SAVE TRANSACTION or other database-specific keywords.

📊 Transaction Flow

Step	Action
1	Begin the transaction.
2	Execute SQL statements.
3	An error or failure occurs.
4	Execute ROLLBACK.
5	The database returns to its previous state.

⚖️ ROLLBACK vs COMMIT

Feature	ROLLBACK	COMMIT
Purpose	Undo changes.	Save changes.
Database State	Restored.	Updated permanently.
Used After Failure	✅ Yes	❌ No
Used After Success	❌ No	✅ Yes

🛡️ ROLLBACK and ACID

ROLLBACK helps maintain the ACID properties of database transactions.

ACID Property	How ROLLBACK Contributes
Atomicity	Ensures all operations succeed together or none are applied.
Consistency	Restores the database to a valid state after failures.
Isolation	Prevents incomplete changes from becoming visible to other transactions.
Durability	Only committed data becomes permanent.

Remember

💡 ROLLBACK protects your data by ensuring incomplete transactions leave no permanent changes behind.

💼 Real-World Applications

🏦 Bank transfers.
🛒 Online shopping checkouts.
📦 Inventory management.
🎟️ Ticket reservation systems.
🏥 Healthcare record updates.
💳 Payment processing.

🗄️ Database Compatibility

Database System	ROLLBACK Support
MySQL	✅ Supported by transactional storage engines such as InnoDB.
PostgreSQL	✅ Full transaction support.
SQL Server	✅ Full transaction support.
Oracle	✅ Full transaction support.
SQLite	✅ Full transaction support.

⚠️ Common Mistakes

❌ Expecting ROLLBACK to undo committed transactions.
❌ Forgetting to handle errors that require a rollback.
❌ Assuming every SQL statement is automatically part of the same explicit transaction.
❌ Leaving transactions open after an error.

Warning

Once COMMIT has been executed, ROLLBACK cannot undo those changes. Any correction requires a new transaction.

⚠️ Best Practices

Best Practice

Always implement proper error handling, execute ROLLBACK whenever a transaction cannot be completed successfully, keep transactions short, consider savepoints for complex workflows, and thoroughly test failure scenarios to ensure the database remains consistent.

🚀 Key Points to Remember

📌 ROLLBACK cancels all uncommitted changes in a transaction.
📌 It restores the database to its previous consistent state.
📌 It is commonly used after errors or unexpected failures.
📌 Savepoints allow partial rollbacks in many database systems.
📌 Committed changes cannot be undone with ROLLBACK.
📌 ROLLBACK is essential for maintaining ACID transaction guarantees.

>>"ROLLBACK is the database's safety net—it ensures that failed operations leave no permanent impact."

Summary

✅ The ROLLBACK statement reverses all uncommitted changes made during a transaction, protecting the database from partial updates and maintaining data integrity. Together with BEGIN TRANSACTION, COMMIT, and optional savepoints, it forms the foundation of reliable transaction management in SQL.