Learn › MySQL › Rahul makes operations atomic with transactions

MySQL Ch 6 / 18 Intermediate

🔒

Rahul makes operations atomic with transactions

START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT — all-or-nothing data changes

⏱ 12 min 6 commands 5 takeaways

🔒

In this chapter

Rahul

Backend developer, handling payments

The story

Rahul's app was processing payments. Placing an order needed two things: deduct inventory AND create the order record. If the app crashed between them, inventory would be wrong and the customer would have no order.

His senior said: transactions solve exactly this.

WHAT IS A TRANSACTION

A transaction is a group of SQL statements that either ALL succeed or ALL fail. No partial success.

Bank transfer example:

Step 1: Deduct from account 101.

Step 2: Add to account 202.

Without transaction: crash after Step 1 means the money vanishes.

With transaction: if anything fails, both steps are undone automatically.

USING TRANSACTIONS

START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE id = 101;
UPDATE accounts SET balance = balance + 5000 WHERE id = 202;

-- If everything looks good:
COMMIT;

-- If something went wrong:
ROLLBACK;

A REAL ORDER PLACEMENT TRANSACTION

START TRANSACTION;

-- Lock the product row so no other transaction can change it simultaneously:
SELECT stock FROM products WHERE id = 7 AND stock >= 1 FOR UPDATE;

-- Deduct inventory:
UPDATE products SET stock = stock - 1 WHERE id = 7;

-- Create the order:
INSERT INTO orders (user_id, product_id, amount) VALUES (42, 7, 29999.00);

-- Everything worked:
COMMIT;

-- In your application code, if any step throws an error:
-- ROLLBACK;

SAVEPOINTS -- PARTIAL ROLLBACK

START TRANSACTION;

INSERT INTO orders (user_id, product_id, amount) VALUES (42, 7, 29999.00);

SAVEPOINT after_order;

UPDATE coupons SET used = 1 WHERE code = 'SAVE100';

-- If the coupon was already used:
ROLLBACK TO SAVEPOINT after_order;
-- Only the coupon step is undone. The order insert is still pending.

COMMIT;

TRANSACTION ISOLATION LEVELS

Two transactions running simultaneously need rules about what each can see.

SELECT @@transaction_isolation;         -- see current level

READ UNCOMMITTED  -- can see uncommitted changes from others (dirty reads, avoid)
READ COMMITTED    -- only sees committed data (most apps use this)
REPEATABLE READ   -- MySQL default, reads within a transaction are consistent
SERIALIZABLE      -- strictest, transactions run one at a time

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

LOCKING

SELECT * FROM orders WHERE id = 100 FOR UPDATE;    -- lock for writing
SELECT * FROM orders WHERE id = 100 FOR SHARE;     -- lock for reading
SELECT * FROM performance_schema.data_locks;        -- see current locks

Rahul wrapped his order placement in a transaction. The next server crash rolled it back automatically. No inconsistent data. No confused customers.

Key takeaways

A transaction is all-or-nothing — if any step fails, ROLLBACK undoes everything since START TRANSACTION

COMMIT saves changes permanently — until you COMMIT, no other connection can see your changes

FOR UPDATE on a SELECT locks the row so other transactions must wait until you COMMIT or ROLLBACK

SAVEPOINT lets you rollback to a checkpoint inside a transaction without undoing everything before it

READ COMMITTED isolation means you only see data that other transactions have already committed — prevents dirty reads

Commands from this chapter

$ START TRANSACTION;

Begin a transaction — nothing is saved until COMMIT

$ COMMIT;

Save all changes in the current transaction permanently

$ ROLLBACK;

Undo all changes since START TRANSACTION

$ SELECT * FROM orders WHERE id = 100 FOR UPDATE;

Lock a row for update — other transactions must wait

$ SAVEPOINT my_savepoint;

Create a checkpoint to rollback to within a transaction

$ ROLLBACK TO SAVEPOINT my_savepoint;

Undo back to the savepoint without a full rollback