Sample Code: Running Transactions

Concept. Every multi-step database operation that needs ACID guarantees is wrapped in BEGIN and COMMIT. The database treats everything between those two keywords as one atomic unit: every write succeeds together, or none of them do, and no other transaction sees the in-between state.

Intuition. The Stripe case study showed why ACID matters. This page shows how to write transactions in SQL. Four familiar scenarios (concert tickets, bank transfer, inventory, social-media likes), each a race between concurrent users on shared data, each one made safe by wrapping the right statements in BEGIN ... COMMIT.

The Three Keywords

  • BEGIN TRANSACTION (or BEGIN / START TRANSACTION): marks the start.

  • COMMIT: makes every change since BEGIN permanent and visible to other transactions.

  • ROLLBACK: undoes every change since BEGIN, as if nothing had happened.

Anything outside a BEGIN ... COMMIT block runs in autocommit mode: each statement is its own tiny one-statement transaction.

How to Read These Examples

Each example shows the happy path: the SQL that runs when the purchase succeeds. If a SELECT reveals that a business check fails (not enough inventory, insufficient balance, duplicate like), the application issues ROLLBACK instead of COMMIT and the database automatically undoes every write since BEGIN. Schema-level constraints (CHECK, UNIQUE, FOREIGN KEY) also trigger automatic rollback when violated.

The key mental model: inside BEGIN ... COMMIT, your reads and writes see one consistent view of the database. Concurrent transactions queue up on the rows you touch, serialized by the database so that each one looks as if it ran alone. You don't need clever SQL tricks to make a simple transaction safe; the block itself is the safety.

Example 1: Ticket Purchase (Atomicity)

Scenario. Taylor Swift tickets go on sale at 5:00 PM. Alice clicks "Purchase 2 tickets." The purchase touches two tables (inventory and orders), and both must update together.

BEGIN TRANSACTION;

-- 1. Check available inventory
SELECT available_tickets
FROM Concerts
WHERE concert_id = 'taylor_swift_123';

-- 2. Reserve the tickets
UPDATE Concerts
SET available_tickets = available_tickets - 2
WHERE concert_id = 'taylor_swift_123';

-- 3. Create the customer order
INSERT INTO Orders (user_id, concert_id, num_tickets, total_amount, status)
VALUES ('user_789', 'taylor_swift_123', 2, 150.00, 'confirmed');

COMMIT;

+ ACID Focus: Atomicity

What breaks without it: the UPDATE Concerts succeeds but INSERT INTO Orders fails halfway (network drop, disk full, server crash). Inventory is decremented by 2 but no order exists. Alice's tickets are "reserved" for nobody.

What the transaction buys: any failure between BEGIN and COMMIT triggers an automatic ROLLBACK. Every change since BEGIN is undone and the database is back to its pre-purchase state. The purchase either happens end-to-end or it doesn't happen at all.

Example 2: Bank Transfer (Consistency)

Scenario. Alice transfers $100 to Bob. Two balances move: Alice's down by $100, Bob's up by $100. Stripe's Accounts table has one schema-enforced rule: CHECK (balance >= 0). No account can ever commit in the red.

BEGIN TRANSACTION;

-- 1. Debit Alice
UPDATE Accounts
SET balance = balance - 100.00
WHERE account_id = 'alice_account';

-- 2. Credit Bob
UPDATE Accounts
SET balance = balance + 100.00
WHERE account_id = 'bob_account';

-- 3. Record the transfer
INSERT INTO Transfers (from_account, to_account, amount, type)
VALUES ('alice_account', 'bob_account', 100.00, 'transfer');

COMMIT;

+ ACID Focus: Consistency

What breaks without it: Alice has $50 but the application tries to transfer $100. Without the CHECK constraint, step 1 quietly sets her balance to -$50 and the rest of the transaction commits. The books now show money that never existed.

What the transaction + constraint buys: step 1's UPDATE violates CHECK (balance >= 0). The database rejects the statement; the transaction fails; all three writes (including Bob's credit and the Transfers row) are rolled back together. The balance invariant holds no matter what the application tries to commit.

Example 3: Inventory Reservation (Isolation)

Scenario. Black Friday at 12:00:00 AM. Limited edition Labubu. Only 1 unit remaining. 1,000 customers click "Buy Now" simultaneously. Without isolation, all 1,000 transactions would read quantity = 1 at the same instant and all 1,000 would "buy" the last unit.

BEGIN TRANSACTION;

-- 1. Check inventory
SELECT quantity FROM Products WHERE product_id = 'labubu';

-- 2. If quantity >= 1, reserve one unit. Otherwise the app ROLLBACKs.
UPDATE Products SET quantity = quantity - 1 WHERE product_id = 'labubu';

-- 3. Create the order
INSERT INTO Orders (customer_id, product_id, quantity, status)
VALUES ('customer_alice', 'labubu', 1, 'confirmed');

COMMIT;

+ ACID Focus: Isolation

What breaks without it: all 1,000 transactions SELECT quantity = 1 at the same instant, all 1,000 UPDATE to 0, all 1,000 INSERT orders. 1,000 customers each "buy" the last unit.

What the transaction buys: the database serializes access to the Labubu row using row-level locking (covered in Two-Phase Locking). The 1,000 transactions queue up one at a time. The first runs end-to-end: SELECT sees quantity = 1, UPDATE decrements to 0, INSERT records the order, COMMIT. Each of the remaining 999 gets its turn only after the lock releases, by which time the row shows quantity = 0; the application sees no inventory and ROLLBACKs. Exactly one customer walks away with a Labubu.

Example 4: Viral Like Counter (Isolation)

Scenario. A celebrity posts breaking news at 3:00 PM. The post goes viral. 10,000 users hit "Like" within five seconds. Two tables must stay in sync: Likes (one row per like) and Posts.like_count (the cached total).

BEGIN TRANSACTION;

-- 1. Record the like.
-- The Likes table has a UNIQUE (user_id, post_id) constraint; a duplicate
-- INSERT fails and the whole transaction rolls back.
INSERT INTO Likes (user_id, post_id, timestamp)
VALUES ('user_12345', 'viral_post_456', NOW());

-- 2. Increment the cached counter
UPDATE Posts
SET like_count = like_count + 1,
    last_activity = NOW()
WHERE post_id = 'viral_post_456';

COMMIT;

+ ACID Focus: Isolation

What breaks without it: the classic "lost update" problem. The counter starts at 500. Two concurrent transactions both read like_count = 500, both compute 500 + 1, both write back 501, and two likes produce one increment. At 10,000 concurrent likes the final counter is nondeterministic: it can land anywhere between 501 (if every increment collides with at least one other) and 10,500 (if they happen to serialize by pure luck). Meanwhile the Likes table correctly receives all 10,000 new rows. The counter and the source of truth have diverged, and by an unpredictable amount.

What the transaction buys: the UPDATE on Posts takes a row-level lock on the post's counter row. Concurrent likes queue up behind that lock. Each transaction reads the current counter, adds one, and writes it back before releasing. After all 10,000 commit, like_count = 10,500 deterministically, and the invariant Posts.like_count = COUNT(*) FROM Likes holds.

Pattern Summary

Scenario Core pattern Primary ACID property
Ticket purchase Multi-table check-reserve-confirm Atomicity
Bank transfer Debit-one-credit-another under CHECK Consistency
Inventory reservation Read-then-decrement under row lock Isolation
Viral counter Insert-detail + update-summary Isolation

Three takeaways worth remembering:

  1. Wrap in BEGIN ... COMMIT whenever you touch more than one row or table. A single UPDATE is already atomic; multiple statements are not, unless you say so.

  2. Inside the block, your reads and writes see a consistent view. The database serializes concurrent transactions so each one looks as if it ran alone. You don't need explicit locks or clever conditional updates for the common cases; the transaction block is the safety.

  3. ROLLBACK is mostly automatic. Any constraint violation, deadlock, crash, or unhandled application exception rolls back the whole transaction. You rarely write ROLLBACK by hand; you let failures propagate.

Next

Building Transactions → The keywords above are the user-facing surface. The next page and the rest of Module 4 dig into what the database does underneath to deliver on the COMMIT promise: locks, schedules, and conflict detection.