Sample Code: Running Transactions

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.

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 extra SQL tricks to make a 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.

-- Step 1: Begin the transaction
BEGIN TRANSACTION;

-- Step 2: Select the available inventory
SELECT available_tickets
FROM Concerts
WHERE concert_id = 'taylor_swift_123';

-- Step 3: Reserve the tickets
UPDATE Concerts
SET available_tickets = available_tickets - 2
WHERE concert_id = 'taylor_swift_123';

-- Step 4: Insert 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');

-- Step 5: Commit every change
COMMIT;

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.

-- Accounts.balance carries a rule declared once in CREATE TABLE: CHECK (balance >= 0).
-- The database enforces it on every write itself: no app code, no separate process.

-- Step 1: Begin the transaction
BEGIN TRANSACTION;

-- Step 2: Debit Alice
UPDATE Accounts
SET balance = balance - 100.00
WHERE account_id = 'alice_account';

-- Step 3: Credit Bob
UPDATE Accounts
SET balance = balance + 100.00
WHERE account_id = 'bob_account';

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

-- Step 5: Commit every change
COMMIT;

Now suppose Alice has only $50. Same transaction, but step 2 would break the rule:

-- The debit runs first and would push her below zero:
UPDATE Accounts SET balance = balance - 100.00   -- 50.00 → -50.00
WHERE account_id = 'alice_account';
-- ERROR: violates CHECK (balance >= 0). The transaction ABORTS;
-- Bob's credit and the Transfers row never run; every change rolls back.

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.

-- Step 1: Begin the transaction
BEGIN TRANSACTION;

-- Step 2: Select the quantity
SELECT quantity FROM Products WHERE product_id = 'labubu';

-- Step 3: Decrement to reserve one unit (else ROLLBACK)
UPDATE Products SET quantity = quantity - 1 WHERE product_id = 'labubu';

-- Step 4: Insert the order
INSERT INTO Orders (customer_id, product_id, quantity, status)
VALUES ('customer_alice', 'labubu', 1, 'confirmed');

-- Step 5: Commit every change
COMMIT;

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).

-- Step 1: Begin the transaction
BEGIN TRANSACTION;

-- Step 2: Insert the like row
-- 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());

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

-- Step 4: Commit every change
COMMIT;

Pattern Summary

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

Graph Refresher → The keywords above are the user-facing surface. The rest of Module 4 digs into what the database does underneath to deliver on the COMMIT promise. The next section, Concurrency Control, opens with a quick graph refresher (cycles and topological sort), then uses it to define exactly when the database's reordering stays correct.