#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

COMMIT and ROLLBACK in MySQL: Transaction Control

Moving ₹1,500 from Riya's wallet to Arjun's takes two UPDATE statements. If the server crashes, or your code throws an error, after the first one and before the second, ₹1,500 has left Riya and reached nobody. Transactions exist to stop that: COMMIT saves a group of changes together, and ROLLBACK throws the whole group away. Either all of it happens or none of it does.

The sample table

CREATE TABLE wallet (
  user_id INT PRIMARY KEY,
  name    VARCHAR(20),
  balance DECIMAL(10,2) NOT NULL,
  CONSTRAINT chk_balance CHECK (balance >= 0)
) ENGINE=InnoDB;

INSERT INTO wallet VALUES (1,'Riya',5000.00), (2,'Arjun',1200.00);

Transactions need a transactional storage engine. InnoDB (the default since MySQL 5.5) supports them; MyISAM does not, and on a MyISAM table ROLLBACK silently undoes nothing. The CHECK constraint is enforced from MySQL 8.0.16; older versions parse it and ignore it.

Autocommit: what happens without a transaction

SELECT @@autocommit;
@@autocommit
1

By default every statement is its own transaction and is committed the moment it succeeds. There is nothing to roll back afterwards. To group statements, open a transaction explicitly with START TRANSACTION (or BEGIN), or turn autocommit off for the session with SET autocommit = 0.

COMMIT: make the transfer permanent

START TRANSACTION;
UPDATE wallet SET balance = balance - 1500 WHERE user_id = 1;
UPDATE wallet SET balance = balance + 1500 WHERE user_id = 2;
COMMIT;

SELECT * FROM wallet;
user_idnamebalance
1Riya3500.00
2Arjun2700.00

Until COMMIT runs, other sessions still see the old balances (under InnoDB's default REPEATABLE READ isolation), and the changed rows stay locked so no one else can update them. After COMMIT the changes are durable, even if the server restarts a second later.

ROLLBACK: undo a transfer that went wrong

Here the second update targets a user who does not exist. MySQL does not treat that as an error; it simply reports 0 rows affected:

START TRANSACTION;
UPDATE wallet SET balance = balance - 2000 WHERE user_id = 1;  -- 1 row affected
UPDATE wallet SET balance = balance + 2000 WHERE user_id = 3;  -- 0 rows affected!
SELECT * FROM wallet;
user_idnamebalance
1Riya1500.00
2Arjun2700.00

₹2,000 has vanished, but only inside this transaction. Roll back and check again:

ROLLBACK;
SELECT * FROM wallet;
user_idnamebalance
1Riya3500.00
2Arjun2700.00

This is why application code should check the affected-row count of each statement and roll back when it is not what was expected.

The trap: an error does not roll back the transaction

Many beginners expect MySQL to undo everything when one statement fails. It does not. Only the failing statement is undone; the transaction stays open with the earlier changes still in it:

START TRANSACTION;
UPDATE wallet SET balance = balance + 500  WHERE user_id = 2;   -- succeeds
UPDATE wallet SET balance = balance - 9000 WHERE user_id = 1;   -- fails
-- ERROR 3819 (HY000): Check constraint 'chk_balance' is violated.
SELECT * FROM wallet;
user_idnamebalance
1Riya3500.00
2Arjun3200.00

Arjun has received ₹500 that Riya never paid. If the next line were COMMIT, that half-transfer would be saved. The fix is to ROLLBACK on any error, which here restores Arjun to 2700.00. In application code this is the try / except / rollback pattern; in a stored procedure it is a DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; block (see the stored procedures tutorial). One exception: a deadlock or a lock-wait timeout with innodb_rollback_on_timeout enabled rolls back the whole transaction for you.

SAVEPOINT: a partial undo

START TRANSACTION;
INSERT INTO wallet VALUES (3,'Kavya',800.00);
SAVEPOINT after_kavya;
INSERT INTO wallet VALUES (4,'Dev',300.00);
ROLLBACK TO SAVEPOINT after_kavya;   -- undoes only Dev
COMMIT;

SELECT * FROM wallet;
user_idnamebalance
1Riya3500.00
2Arjun2700.00
3Kavya800.00

ROLLBACK TO SAVEPOINT keeps the transaction open; you still need COMMIT or a full ROLLBACK at the end.

Statements that commit for you

DDL statements such as CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE TABLE cause an implicit commit, and so do LOCK TABLES and starting a new START TRANSACTION. Watch what happens:

START TRANSACTION;
UPDATE wallet SET balance = 0 WHERE user_id = 3;
CREATE TABLE audit_note (id INT);   -- implicit COMMIT happens here
ROLLBACK;                           -- nothing left to roll back

SELECT * FROM wallet WHERE user_id = 3;
user_idnamebalance
3Kavya0.00

Kavya's balance is gone for good. Keep schema changes out of data transactions.

Quick reference

StatementWhat it does
START TRANSACTION / BEGINOpens a transaction; autocommit is paused until it ends
COMMITSaves every change since the transaction started
ROLLBACKDiscards every change since the transaction started
SAVEPOINT nameMarks a point inside the transaction
ROLLBACK TO SAVEPOINT nameUndoes changes after the mark; the transaction continues
SET autocommit = 0Every statement now waits for an explicit COMMIT (session only)

Keep transactions short: an open transaction holds row locks, and a forgotten START TRANSACTION in a Workbench tab can block other users for minutes. For the theory behind all this (atomicity, consistency, isolation levels, durability), read MySQL transactions and ACID properties. Where these commands sit among SQL's command groups is covered in DDL, DML, DCL and TCL commands, and the engine difference in InnoDB vs MyISAM.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →