COMMIT and ROLLBACK are transaction control commands used to manage changes made inside a database transaction. COMMIT saves the changes, while ROLLBACK cancels uncommitted changes.
COMMIT permanently saves the changes made during the current transaction.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 1000
WHERE id = 1;
COMMIT;After COMMIT, the transaction is successfully completed.
ROLLBACK cancels changes made during the current transaction that have not been committed.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 1000
WHERE id = 1;
ROLLBACK;The uncommitted UPDATE is undone.
Before using COMMIT or ROLLBACK for a group of operations, an explicit transaction can be started.
START TRANSACTION;
INSERT INTO payments
(student_id, amount)
VALUES
(101, 2000);
COMMIT;The INSERT and COMMIT belong to the same transaction.
BEGIN can also be used to start a transaction in MySQL.
BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE id = 1;
COMMIT;The transaction can then be committed or rolled back.
Suppose a student pays ₹2,000.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 2000
WHERE id = 101;
COMMIT;COMMIT saves the update as part of the transaction.
If the update should not be saved, use ROLLBACK before the transaction is committed.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 2000
WHERE id = 101;
ROLLBACK;The uncommitted change is cancelled.
COMMIT can save INSERT operations.
START TRANSACTION;
INSERT INTO students
(name, city)
VALUES
('Rahul', 'Patna');
COMMIT;The new student record is committed.
An INSERT can be undone before it is committed.
START TRANSACTION;
INSERT INTO students
(name, city)
VALUES
('Rahul', 'Patna');
ROLLBACK;The uncommitted INSERT is cancelled.
COMMIT can permanently save an UPDATE.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 10;
COMMIT;The status change is committed.
If an UPDATE was made accidentally, it can be rolled back before COMMIT.
START TRANSACTION;
UPDATE students
SET status = 'Inactive'
WHERE id = 10;
ROLLBACK;The uncommitted update is cancelled.
DELETE operations can also be committed.
START TRANSACTION;
DELETE FROM students
WHERE id = 10;
COMMIT;The DELETE becomes committed according to the transaction rules.
A DELETE can be cancelled before COMMIT.
START TRANSACTION;
DELETE FROM students
WHERE id = 10;
ROLLBACK;The uncommitted DELETE is undone.
Multiple SQL statements can be committed together.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 1000
WHERE id = 1;
INSERT INTO payments
(student_id, amount)
VALUES
(1, 1000);
COMMIT;The statements are handled as one transaction.
If a transaction contains multiple uncommitted changes, ROLLBACK can cancel them.
START TRANSACTION;
UPDATE students
SET paid_fee = paid_fee + 1000
WHERE id = 1;
INSERT INTO payments
(student_id, amount)
VALUES
(1, 1000);
ROLLBACK;The uncommitted changes from the transaction are rolled back.
A transaction can use a SAVEPOINT and then be committed.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 1;
SAVEPOINT point1;
UPDATE students
SET status = 'Active'
WHERE id = 2;
COMMIT;COMMIT completes the transaction and releases its transaction state.
You can roll back to a specific savepoint without rolling back the entire transaction.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 1;
SAVEPOINT point1;
UPDATE students
SET status = 'Inactive'
WHERE id = 2;
ROLLBACK TO SAVEPOINT point1;
COMMIT;The changes made after the savepoint are rolled back, while earlier changes can remain in the transaction.
Once a savepoint is no longer needed, it can be released.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 1;
SAVEPOINT point1;
RELEASE SAVEPOINT point1;
COMMIT;Releasing a savepoint does not commit the transaction.
Applications can perform validation before committing a transaction.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;
-- If validation succeeds
COMMIT;The application should commit only when the complete business operation is valid.
If an important operation fails, the application can roll back the transaction.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;
-- Some required operation fails
ROLLBACK;The uncommitted changes are cancelled.
A payment system may need to insert a payment and update the student's paid amount together.
START TRANSACTION;
INSERT INTO payments
(student_id, amount)
VALUES
(101, 2000);
UPDATE students
SET paid_fee = paid_fee + 2000
WHERE id = 101;
COMMIT;If the operation cannot be completed correctly, the application can use ROLLBACK.
MySQL commonly uses autocommit by default. When autocommit is enabled, individual statements are automatically committed unless an explicit transaction is started.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 5;
COMMIT;Explicit transaction boundaries are useful when multiple statements must be handled together.
Once a transaction has been successfully committed, a later ROLLBACK does not undo that already committed transaction.
START TRANSACTION;
UPDATE students
SET status = 'Active'
WHERE id = 1;
COMMIT;
ROLLBACK;The ROLLBACK does not reverse the changes that were already committed.
A bank transfer can contain two related balance updates.
START TRANSACTION;
UPDATE accounts
SET balance = balance - 5000
WHERE id = 1;
UPDATE accounts
SET balance = balance + 5000
WHERE id = 2;
COMMIT;If the operation cannot be completed correctly before COMMIT, the application can roll back the transaction.
An online order can involve multiple operations.
START TRANSACTION;
INSERT INTO orders
(customer_id, total_amount)
VALUES
(10, 2500);
UPDATE products
SET quantity = quantity - 1
WHERE id = 20;
COMMIT;If a required operation fails before COMMIT, ROLLBACK can cancel the uncommitted changes.
Inventory updates can be grouped into a transaction.
START TRANSACTION;
UPDATE products
SET quantity = quantity - 5
WHERE id = 101;
UPDATE stock
SET available = available - 5
WHERE product_id = 101;
COMMIT;Both updates can be handled together by the application.
| COMMIT | ROLLBACK |
|---|---|
| Saves the transaction's changes | Cancels uncommitted transaction changes |
| Completes the transaction successfully | Abandons the uncommitted changes |
| Used when operations are successful | Used when operations should not be kept |
Suppose a student pays ₹3,000. The system needs to create a payment record and update the student's paid fee.
START TRANSACTION;
INSERT INTO payments
(student_id, amount)
VALUES
(101, 3000);
UPDATE students
SET paid_fee = paid_fee + 3000
WHERE id = 101;
COMMIT;If the application detects a problem before COMMIT:
ROLLBACK;The transaction can be cancelled instead of keeping the uncommitted changes.
Consider a student fee payment system where payment insertion and fee update must be handled together.
START TRANSACTION;
INSERT INTO payments
(student_id, amount)
VALUES
(101, 2000);
UPDATE students
SET paid_fee = paid_fee + 2000
WHERE id = 101;
-- Check whether all operations were successful
COMMIT;If an error occurs before COMMIT:
ROLLBACK;For a more detailed transaction, a savepoint can be used:
START TRANSACTION;
INSERT INTO payments
(student_id, amount)
VALUES
(101, 2000);
SAVEPOINT payment_point;
UPDATE students
SET paid_fee = paid_fee + 2000
WHERE id = 101;
COMMIT;This demonstrates the basic transaction flow: START TRANSACTION → perform operations → COMMIT if successful → ROLLBACK if the transaction must be cancelled.
Question: Which command cancels uncommitted changes in a transaction?