START TRANSACTION, COMMIT and ROLLBACK
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A money transfer needs two updates: take from one account, add to another. If the server crashes between them, money vanishes. How do you make both steps happen together, or not at all?
A transaction groups statements
A transaction is a group of SQL statements that the database treats as one unit of work: either every change in it is saved, or none is.
Saving an online order is three changes: the order, its item and the lower stock. A half-saved order is worse than none.
Example 1 · A UPI transfer as one transaction
DROP TABLE IF EXISTS upi_accounts;CREATE TABLE upi_accounts ( upi_id VARCHAR(30) PRIMARY KEY, holder VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL);INSERT INTO upi_accounts (upi_id, holder, balance) VALUES ('asha@okaxis', 'Asha Rao', 5000.00), ('ravi@ybl', 'Ravi Kumar', 1200.00);
-- Asha pays Ravi Rs. 750: two changes, one unit of workSTART TRANSACTION;UPDATE upi_accounts SET balance = balance - 750 WHERE upi_id = 'asha@okaxis';UPDATE upi_accounts SET balance = balance + 750 WHERE upi_id = 'ravi@ybl';COMMIT;
SELECT upi_id, holder, balance FROM upi_accounts ORDER BY upi_id;+-------------+------------+---------+ | upi_id | holder | balance | +-------------+------------+---------+ | asha@okaxis | Asha Rao | 4250.00 | | ravi@ybl | Ravi Kumar | 1950.00 | +-------------+------------+---------+
What this code does
- The two UPDATEs sit between
START TRANSACTIONandCOMMIT, so MySQL saves them together. The total money, Rs. 6,200, is the same before and after. - The interactive client also prints a line such as
Query OK, 1 row affected (0.01 sec)after each UPDATE and COMMIT. Lessons show only the result tables.
START TRANSACTION begins a transaction, and COMMIT saves every change made since then, all together. Until the commit, the changes are pending: only your own session can see them.
Why you need it: a UPI payment of Rs. 750 is two UPDATEs, one that takes the money from Asha and one that gives it to Ravi. If the server stops between them, Rs. 750 simply vanishes. Inside a transaction, the two UPDATEs are saved together or not at all. BEGIN is another name for START TRANSACTION in MySQL. This course writes START TRANSACTION, because its meaning is clear to every reader.
Order of execution
Save a ShopLite order
Put the steps of saving one order in the order the code runs them.
Autocommit in MySQL
Autocommit is a session setting that makes MySQL commit every statement on its own, the moment it finishes. It is on by default in MySQL.
Every UPDATE you ran in earlier modules was saved the moment it ended. One query shows the setting.
SELECT @@autocommit;+--------------+ | @@autocommit | +--------------+ | 1 | +--------------+
With autocommit on, each statement is its own tiny transaction. START TRANSACTION pauses autocommit until the next COMMIT or ROLLBACK; after that it is back on by itself.
SET autocommit = 0 switches it off for the whole session. Then the first statement quietly starts a transaction, every statement after it joins that transaction, and nothing is saved until you type COMMIT.
Example 2 · Autocommit off: nothing is saved until COMMIT
DROP TABLE IF EXISTS hostel_fees;CREATE TABLE hostel_fees ( roll_no VARCHAR(8) PRIMARY KEY, name VARCHAR(40) NOT NULL, paid DECIMAL(10,2) NOT NULL);INSERT INTO hostel_fees (roll_no, name, paid) VALUES ('CS21', 'Aditya Joshi', 20000.00), ('EC07', 'Fathima Begum', 35000.00);
SET autocommit = 0; -- from here on, nothing is saved until COMMIT
UPDATE hostel_fees SET paid = paid + 15000 WHERE roll_no = 'CS21';SELECT roll_no, paid FROM hostel_fees ORDER BY roll_no;
ROLLBACK; -- the clerk typed the wrong roll numberSELECT roll_no, paid FROM hostel_fees ORDER BY roll_no;
SET autocommit = 1; -- back to the default+---------+----------+ | roll_no | paid | +---------+----------+ | CS21 | 35000.00 | | EC07 | 35000.00 | +---------+----------+ +---------+----------+ | roll_no | paid | +---------+----------+ | CS21 | 20000.00 | | EC07 | 35000.00 | +---------+----------+
What this code does
- After
SET autocommit = 0, the UPDATE starts a transaction by itself. The first SELECT shows 35000.00 because a session always sees its own pending changes. ROLLBACKthrows the pending change away, so CS21 is back to 20000.00. The last line turns autocommit on again for the rest of the session.
COMMIT with autocommit off is a common bug: your window shows the new data, nobody else sees it, and it disappears when you close the window.ROLLBACK undoes everything since START TRANSACTION
ROLLBACK cancels the open transaction: every insert, update and delete since START TRANSACTION is undone, and the rows look exactly as they did before it began.
A payment fails after the order row is already inserted. ROLLBACK removes the half-saved order.
Example 3 · A ShopLite order rolled back
START TRANSACTION;INSERT INTO orders (customer_id, order_date) VALUES (10, '2025-06-05');INSERT INTO order_items (order_id, product_id, quantity, unit_price)VALUES (LAST_INSERT_ID(), 7, 1, 2199.00);UPDATE products SET stock = stock - 1 WHERE product_id = 7;
-- Inside the transaction, this session sees its own changesSELECT order_id, customer_id, status FROM orders WHERE order_id > 15;SELECT product_id, name, stock FROM products WHERE product_id = 7;
-- Payment failed: undo all three changesROLLBACK;
SELECT COUNT(*) AS orders_after_15 FROM orders WHERE order_id > 15;SELECT product_id, name, stock FROM products WHERE product_id = 7;
-- The next order does not get id 16 againINSERT INTO orders (customer_id, order_date) VALUES (10, '2025-06-05');SELECT LAST_INSERT_ID() AS next_order_id;+----------+-------------+--------+ | order_id | customer_id | status | +----------+-------------+--------+ | 16 | 10 | PLACED | +----------+-------------+--------+ +------------+------------------------+-------+ | product_id | name | stock | +------------+------------------------+-------+ | 7 | Mi Power Bank 20000mAh | 59 | +------------+------------------------+-------+ +-----------------+ | orders_after_15 | +-----------------+ | 0 | +-----------------+ +------------+------------------------+-------+ | product_id | name | stock | +------------+------------------------+-------+ | 7 | Mi Power Bank 20000mAh | 60 | +------------+------------------------+-------+ +---------------+ | next_order_id | +---------------+ | 17 | +---------------+
What this code does
- Saving an order is three changes: the order row, its item and the lower stock.
ROLLBACKremoves all three, so no order is left after 15 and the power bank stock is back to 60. - The id 16 was used inside the rolled-back transaction and is not given back, so the next order gets 17. An application must never assume ids have no gaps.
Use it when something goes wrong halfway, such as a failed payment while an order is being saved. Closing the session or losing the connection without a COMMIT has the same effect: MySQL rolls the open transaction back.
Predict the output
Predict the stock after ROLLBACK
Product 7 starts with stock 60. What does the last SELECT return?
Round 1 of 2
ENGINE=MyISAM ignores them: its changes are saved at once and ROLLBACK cannot undo them.SAVEPOINT and ROLLBACK TO
A savepoint is a named marker inside a transaction, set with SAVEPOINT name. ROLLBACK TO SAVEPOINT name undoes only the work done after that marker; the work before it stays and the transaction stays open.
A shopper removes one item from a half-built order. You need to undo one step, not the whole order.
Example 4 · SAVEPOINT: remove the extras, keep the rest
DROP TABLE IF EXISTS cart_items;CREATE TABLE cart_items ( item_no INT AUTO_INCREMENT PRIMARY KEY, item VARCHAR(30) NOT NULL, price DECIMAL(7,2) NOT NULL);
START TRANSACTION;INSERT INTO cart_items (item, price) VALUES ('Clean Code', 499.00), ('Hands-On Python', 650.00);SAVEPOINT before_extras;INSERT INTO cart_items (item, price) VALUES ('Rubik''s Cube', 299.00), ('boAt Airdopes 141', 1299.00);SELECT item, price FROM cart_items ORDER BY item_no;
-- The customer removes both extras and picks another book insteadROLLBACK TO SAVEPOINT before_extras;INSERT INTO cart_items (item, price) VALUES ('Atomic Habits', 399.00);COMMIT;
SELECT item, price FROM cart_items ORDER BY item;SELECT SUM(price) AS bill FROM cart_items;+-------------------+---------+ | item | price | +-------------------+---------+ | Clean Code | 499.00 | | Hands-On Python | 650.00 | | Rubik's Cube | 299.00 | | boAt Airdopes 141 | 1299.00 | +-------------------+---------+ +-----------------+--------+ | item | price | +-----------------+--------+ | Atomic Habits | 399.00 | | Clean Code | 499.00 | | Hands-On Python | 650.00 | +-----------------+--------+ +---------+ | bill | +---------+ | 1548.00 | +---------+
What this code does
ROLLBACK TO SAVEPOINT before_extrasundoes only the two rows added after the marker. The two books stay, and the transaction is still open.- Atomic Habits is added in the same transaction, and
COMMITsaves the three items: a bill of Rs. 1,548.
Example 5 · A savepoint that no longer exists
START TRANSACTION;UPDATE products SET stock = stock - 1 WHERE product_id = 9;SAVEPOINT step1;UPDATE products SET stock = stock - 1 WHERE product_id = 10;SAVEPOINT step2;
ROLLBACK TO SAVEPOINT step1; -- undoes product 10, deletes step2SELECT product_id, stock FROM products WHERE product_id IN (9, 10) ORDER BY product_id;
ROLLBACK TO SAVEPOINT step2; -- step2 no longer exists+------------+-------+ | product_id | stock | +------------+-------+ | 9 | 29 | | 10 | 22 | +------------+-------+ ERROR 1305 (42000): SAVEPOINT step2 does not exist
What this code does
- Rolling back to
step1undoes the stock change for product 10 and also deletesstep2, because it was set afterstep1. - So the last line fails with error 1305. The client stops at the error; the open transaction is rolled back when the session ends.
Use it to undo one step of a long transaction without starting over: an order where the customer removes the extras, or a group booking where one passenger's details fail a check.
COMMIT and a full ROLLBACK delete every savepoint. RELEASE SAVEPOINT name deletes one marker without undoing anything. Using a savepoint that does not exist gives error 1305.
Statements that commit on their own
An implicit commit is a commit that MySQL does by itself before certain statements run, such as CREATE TABLE. Pending changes are saved first, and a later ROLLBACK cannot undo them.
A ROLLBACK that follows a CREATE TABLE finds nothing left to undo. Some statements save the open transaction on their own.
Example 6 · CREATE TABLE commits the open transaction
START TRANSACTION;UPDATE products SET price = price * 0.90 WHERE category_id = 5; -- 10% off books
CREATE TABLE price_log (note VARCHAR(50)); -- DDL: MySQL commits first
ROLLBACK; -- too late: there is no open transaction left to undo
SELECT product_id, name, price FROM products WHERE category_id = 5 ORDER BY product_id;+------------+-----------------+--------+ | product_id | name | price | +------------+-----------------+--------+ | 9 | Clean Code | 449.10 | | 10 | Hands-On Python | 585.00 | | 11 | Atomic Habits | 359.10 | +------------+-----------------+--------+
What this code does
- CREATE TABLE is DDL, so MySQL commits the price cut before creating the table. The ROLLBACK then has no transaction to undo.
- The books stay 10% cheaper: Rs. 449.10, 585.00 and 359.10. Creating
price_logbeforeSTART TRANSACTIONwould have kept the price cut undoable.
The main ones are DDL statements, the statements that change the structure of the database: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE and CREATE INDEX. A second START TRANSACTION also commits the open transaction, and so do SET autocommit = 1 and LOCK TABLES. CREATE TEMPORARY TABLE is an exception: it does not commit.
The rule for scripts: keep structure changes out of transactions. Create or alter the tables first, then run the data changes between START TRANSACTION and COMMIT.
Next step
Check your understanding
12 quick questions