Stage 8. Transactions and ACID with Real Examples

A transaction is a group of SQL operations that represent one business action. The database should apply the whole group or reject the whole group. This is what protects the system from half-finished changes: money debited but not credited, an order created without items, or stock reduced without an audit record.

In SQL, a transaction usually starts with BEGIN, finishes successfully with COMMIT, and is cancelled with ROLLBACK. COMMIT means “make these changes permanent”. ROLLBACK means “return the database to the state before this transaction started”.

Money transfer is the classic example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
INSERT INTO ledger(from_id, to_id, amount) VALUES (1, 2, 100);
COMMIT;

If the second UPDATE fails, the first update must not stay committed. Otherwise money disappears from one account and never reaches the other. A transaction tells the database that these statements belong together.

What ACID Means

ACID is a set of properties that describe reliable transaction behavior.

Atomicity means “all or nothing”. If a transaction contains ten SQL statements and the seventh statement fails, the database must roll back the previous six statements from that transaction. This is why transactions are useful for money transfers, order creation, and any operation where intermediate states are dangerous.

Consistency means a successful transaction moves the database from one valid state to another valid state. The database can enforce part of this with constraints: NOT NULL, CHECK, UNIQUE, and foreign keys. But consistency is also a responsibility of the application. The database can check that phone.client_id references an existing client if you define a foreign key. It cannot automatically know every business rule unless the schema and transaction logic express it.

Isolation means concurrent transactions should not corrupt each other's results. Real systems run many requests at the same time. Without isolation, two users can update the same balance or stock row and accidentally overwrite each other. Isolation is implemented with mechanisms such as locks and row versions. Later you will study isolation levels in more detail; for now, remember the purpose: parallel work must still produce correct data.

Durability means that once the database confirms COMMIT, the committed changes must survive a crash, restart, or power loss. In practice, databases use transaction logs and durable storage so a completed transaction can be recovered after a failure. Durability does not mean the value can never change again. It means a committed change is not silently lost because the server crashed right after success was reported.

Order Creation Scenario

Creating an order usually needs one transaction:

1. Create the order row.
2. Create order item rows.
3. Decrease stock.
4. Write an audit or ledger event.

If stock update fails, all previous steps should roll back. Otherwise the system may show an order that cannot actually be fulfilled.

BEGIN;

UPDATE products
SET stock = stock - 1
WHERE id = 10 AND stock > 0;

-- if 0 rows were affected, rollback

INSERT INTO orders(user_id, status) VALUES (42, 'NEW');

COMMIT;

Expected behavior:

• if stock > 0, the transaction can commit;
• if no stock is available, the transaction should roll back;
• after rollback, there should be no half-created order.

What Can Go Wrong Without Isolation

Concurrency bugs are often rare, which makes them expensive. They appear under load, not during a simple local test.

Common isolation problems:

• lost update: two transactions read the same value, both write a new value, and one change overwrites the other;
• dirty read: one transaction reads data that another transaction has not committed yet;
• non-repeatable read: the same row is read twice in one transaction, but another transaction changes it between reads;
• phantom read: the same query returns a different set of rows because another transaction inserted or deleted matching rows.

Databases fight these problems with locks and versioning. A lock temporarily protects data that is being changed. Versioning lets readers see a stable older version while another transaction prepares a new version. The exact behavior depends on the isolation level.

Common mistakes:

1. Very long transactions.
2. External API calls inside a database transaction.
3. Opening the transaction before validation is done.
4. No rollback-path tests.
5. Assuming local single-user behavior proves concurrent behavior.

Good practice:

• validate input before opening a transaction;
• keep the transaction short;
• keep only database work inside;
• check affected row counts for conditional updates;
• log rollback reasons;
• monitor rollback rate and deadlocks in production.

Transactions protect business invariants under failures and load. Think of one transaction as one safe boundary for one business operation.

Practice Before the Next Lesson

Re-run the examples from this article on a tiny dataset of 5-10 rows. This step matters because SQL becomes much easier once you execute queries yourself and inspect real tabular output. Start with a plain version of the query, then change one thing at a time and compare behavior or execution plan. If results look wrong, validate each part: filters, selected columns, join conditions, and expected NULL handling.

Quick self-check:

1. I can explain the lesson in one simple sentence.
2. I can write a basic SQL example from memory.
3. I can read query output and explain each column.
4. I can name one common mistake and how to avoid it.

In production, monitor rollback rate and deadlocks. These two signals quickly show whether transaction boundaries are too wide or concurrency handling needs revision.

Extra Scenario: Inventory Reservation

In e-commerce, you must not sell more items than available stock. A transaction plus conditional update handles this safely.

BEGIN;
UPDATE products
SET stock = stock - 1
WHERE id = 10 AND stock > 0;
-- if 0 rows affected -> ROLLBACK
COMMIT;

If two buyers attempt the last item concurrently, one succeeds and one gets zero updated rows. This keeps stock consistent.