Transactions

Placing an order in the shop is not one statement but several: insert the order, insert each order item, and reduce the stock of each product. Now imagine the server crashes after the stock was reduced but before the order was saved. Products have vanished from the shelf and nobody bought them.

A transaction solves this. It groups several statements into one unit of work that either happens completely or not at all. In this lesson you will learn what ACID means, how to start, commit and cancel a transaction in MySQL, and how to stop two customers from buying the last item at the same moment.

All or nothing

You start a transaction, run your statements, and then end it in one of two ways:

• COMMIT makes every change permanent and visible to other users.
• ROLLBACK cancels every change made since the transaction started, as if nothing happened.

In MySQL you start a transaction with START TRANSACTION. BEGIN means the same thing, and it is what the browser editor understands, so the runnable examples use BEGIN.

BEGIN;

UPDATE products SET stock = stock - 2 WHERE product_id = 1;
SELECT product_name, stock FROM products WHERE product_id = 1;

ROLLBACK;

SELECT product_name, stock FROM products WHERE product_id = 1;

product_name   | stock
---------------+------
Wireless Mouse | 118
(1 row)

product_name   | stock
---------------+------
Wireless Mouse | 120
(1 row)

Inside the transaction the stock is 118. After ROLLBACK, it is back to 120. Until you commit, your changes are a draft that you can still throw away.

ACID: what a transaction promises

Database people describe transactions with four properties, called ACID:

In MySQL these guarantees come from the InnoDB storage engine, which is the default for new tables. The older MyISAM engine does not support transactions at all: on a MyISAM table, ROLLBACK does nothing. Make sure your tables use InnoDB.

Placing an order in one transaction

Here Meera orders a 1TB SSD and two pen drives. All four changes sit between BEGIN and COMMIT:

BEGIN;

INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (116, 6, '2025-06-03', 'pending');

INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price)
VALUES (23, 116, 9, 1, 6499.00),
       (24, 116, 10, 2, 549.00);

UPDATE products SET stock = stock - 1 WHERE product_id = 9;
UPDATE products SET stock = stock - 2 WHERE product_id = 10;

COMMIT;

SELECT o.order_id, c.first_name, p.product_name, oi.quantity, p.stock
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_id = 116
ORDER BY oi.item_id;

order_id | first_name | product_name     | quantity | stock
---------+------------+------------------+----------+------
116      | Meera      | 1TB External SSD | 1        | 29
116      | Meera      | 64GB Pen Drive   | 2        | 198
(2 rows)

If anything goes wrong before COMMIT, such as a failed insert or a crash, none of the four changes is saved.

A money transfer

The classic transaction example is moving money between two accounts. Here the shop keeps wallet balances for customers, and Priya sends ₹500 to Rohan:

CREATE TABLE wallets (
  customer_id INTEGER PRIMARY KEY,
  balance DECIMAL(10,2) NOT NULL
);

BEGIN;
INSERT INTO wallets (customer_id, balance) VALUES (2, 1200.00), (3, 300.00);
COMMIT;

BEGIN;
UPDATE wallets SET balance = balance - 500 WHERE customer_id = 2;
UPDATE wallets SET balance = balance + 500 WHERE customer_id = 3;
COMMIT;

SELECT customer_id, balance FROM wallets;

customer_id | balance
------------+--------
2           | 700
3           | 800
(2 rows)

The first short transaction loads the opening balances; the second one is the transfer. Without a transaction, a crash between the two updates would take ₹500 from Priya and never give it to Rohan. With a transaction, the money is either in one wallet or the other, never lost in between.

Errors do not roll back by themselves

A common surprise: in MySQL, if one statement inside a transaction fails, only that statement is cancelled. The earlier statements are still waiting, and the transaction is still open. Your code must notice the error and send ROLLBACK. In PHP with PDO, the usual pattern is:

try {
    $pdo->beginTransaction();
    $pdo->prepare('UPDATE wallets SET balance = balance - ? WHERE customer_id = ?')->execute([500, 2]);
    $pdo->prepare('UPDATE wallets SET balance = balance + ? WHERE customer_id = ?')->execute([500, 3]);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

You will meet PDO properly in CRUD with Prepared Statements.

Autocommit and implicit commits

By default MySQL runs in autocommit mode: every statement outside START TRANSACTION is its own small transaction and is committed immediately. That is why a plain UPDATE cannot be undone. You can switch it off for your session with SET autocommit = 0;, after which nothing is saved until you COMMIT.

Some statements end a transaction for you with an implicit commit. Statements that change table structure, such as CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE TABLE, commit whatever was pending first, and they cannot be rolled back. Keep structure changes out of your transactions.

Savepoints

A savepoint is a bookmark inside a transaction. ROLLBACK TO SAVEPOINT name cancels only the work done after the bookmark, and the transaction continues:

BEGIN;
UPDATE products SET price = 699.00 WHERE product_id = 1;
SAVEPOINT before_books;
UPDATE products SET price = 0 WHERE category = 'Books';
ROLLBACK TO SAVEPOINT before_books;
COMMIT;

SELECT product_name, price FROM products WHERE product_id IN (1, 11);

product_name         | price
---------------------+------
Wireless Mouse       | 699
Python Basics (Book) | 450
(2 rows)

The mouse price change is kept; the accidental change to the book price is undone.

Two buyers, one webcam

Isolation matters when many people use the shop at once. Suppose one HD Webcam is left and Aarav and Isha click "Buy" at the same second. Both programs read stock = 1, both decide there is enough, and both sell it. To prevent this, lock the row while you check it:

START TRANSACTION;

SELECT stock FROM products WHERE product_id = 8 FOR UPDATE;
-- if stock >= 1, continue; otherwise ROLLBACK
UPDATE products SET stock = stock - 1 WHERE product_id = 8;

COMMIT;

SELECT ... FOR UPDATE locks the selected row until your transaction ends. The second buyer's FOR UPDATE waits, then reads the new stock of 0. InnoDB's default isolation level is REPEATABLE READ; you can change it per session with statements such as SET TRANSACTION ISOLATION LEVEL READ COMMITTED, but the default is right for most applications.

A simpler alternative for counters is a single conditional update, UPDATE products SET stock = stock - 1 WHERE product_id = 8 AND stock >= 1, and then checking whether one row changed.

Key points

• A transaction groups statements so they all succeed or all fail.
• Start with START TRANSACTION (or BEGIN), finish with COMMIT or cancel with ROLLBACK.
• ACID means atomic, consistent, isolated and durable; in MySQL, InnoDB provides it.
• A failed statement does not cancel the transaction; your code must call ROLLBACK.
• Autocommit saves each statement immediately; structure changes cause an implicit commit.
• SELECT ... FOR UPDATE locks rows so two users cannot sell the same last item.

BEGIN;
DELETE FROM order_items WHERE item_id = 4;
UPDATE products SET stock = stock + 1 WHERE product_id = 2;
COMMIT;

SELECT product_name, stock FROM products WHERE product_id = 2;
SELECT item_id, product_id, quantity FROM order_items WHERE order_id = 103;