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_id | name | balance |
|---|---|---|
| 1 | Riya | 3500.00 |
| 2 | Arjun | 2700.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_id | name | balance |
|---|---|---|
| 1 | Riya | 1500.00 |
| 2 | Arjun | 2700.00 |
₹2,000 has vanished, but only inside this transaction. Roll back and check again:
ROLLBACK;
SELECT * FROM wallet;
| user_id | name | balance |
|---|---|---|
| 1 | Riya | 3500.00 |
| 2 | Arjun | 2700.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_id | name | balance |
|---|---|---|
| 1 | Riya | 3500.00 |
| 2 | Arjun | 3200.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_id | name | balance |
|---|---|---|
| 1 | Riya | 3500.00 |
| 2 | Arjun | 2700.00 |
| 3 | Kavya | 800.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_id | name | balance |
|---|---|---|
| 3 | Kavya | 0.00 |
Kavya's balance is gone for good. Keep schema changes out of data transactions.
Quick reference
| Statement | What it does |
|---|---|
START TRANSACTION / BEGIN | Opens a transaction; autocommit is paused until it ends |
COMMIT | Saves every change since the transaction started |
ROLLBACK | Discards every change since the transaction started |
SAVEPOINT name | Marks a point inside the transaction |
ROLLBACK TO SAVEPOINT name | Undoes changes after the mark; the transaction continues |
SET autocommit = 0 | Every 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.