COMMIT and ROLLBACK behavior in SQL

Introduction

COMMIT saves your changes to the database permanently. ROLLBACK undoes changes you made since the last save.

When you finish adding or changing data and want to keep it safe.

If you make a mistake and want to undo recent changes.

When you want to group several changes together and save them all at once.

During a bank transfer to make sure money is moved correctly or not at all.

When testing changes and you want to cancel them if something goes wrong.

Syntax

SQL

COMMIT;
ROLLBACK;

COMMIT makes all changes since the last COMMIT or ROLLBACK permanent.

ROLLBACK undoes all changes since the last COMMIT or ROLLBACK.

Examples

Adds a new account for Alice and saves it permanently.

SQL

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000);
COMMIT;

Tries to subtract 100 from Alice's balance but then undoes the change.

SQL

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
ROLLBACK;

Moves 100 from Alice to Bob and saves both changes together.

SQL

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

Sample Program

This example tries to move 200 from Alice to Bob but then cancels the changes with ROLLBACK. The final SELECT shows original balances.

SQL

CREATE TABLE accounts (name VARCHAR(20), balance INT);

INSERT INTO accounts VALUES ('Alice', 1000);
INSERT INTO accounts VALUES ('Bob', 500);

-- Start a transaction
UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob';

-- Undo the changes
ROLLBACK;

SELECT * FROM accounts ORDER BY name;

Important Notes

Some databases auto-commit changes unless you start a transaction explicitly.

Always COMMIT after a group of related changes to save them.

Use ROLLBACK to avoid partial or wrong data updates.

Summary

COMMIT saves all recent changes permanently.

ROLLBACK undoes all recent changes since last save.

Use them to control when your data updates become final.