Transactions, Normalization, and Performance

Transactions and Multi-Step Operations

---

A transaction is one of the most important ideas in database systems. It is the tool that lets several related changes behave as one unit.

So far in this course, every write has been a single statement. An INSERT adds a row, an UPDATE modifies one or more rows, and a DELETE removes them. The database guarantees that each statement either succeeds completely or fails completely; there are no half-finished statements.

Transactions extend that same guarantee from one statement to a group of statements.

An Example With Bank Accounts

Consider an example where we want to move money from one account to another. This requires two statements: one to subtract the amount from the source account, and one to add it to the destination account.

Assuming that we have an accounts table with a balance column, the SQL might look like this:

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

That is two statements. If the first succeeds and the second fails (because of a network glitch, an application crash, a constraint violation that surfaces late), the database is left in an inconsistent state. Money has been removed from the first account, but the second account has not received the funds.

The fix is to group the two statements into a transaction. Either both succeed and the database moves to the new consistent state, or both fail and the database stays in the old consistent state. The half-finished case is no longer possible.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If anything goes wrong between BEGIN and COMMIT, the application can issue ROLLBACK instead, and neither change becomes permanent.

Loading Exercise...

BEGIN, COMMIT, ROLLBACK

Every transaction has the same shape:

• BEGIN starts the transaction. From this point, all statements are part of the same group.
• The actual work — INSERT, UPDATE, DELETE, even SELECT — runs within the transaction.
• COMMIT makes all the changes permanent at once.
• ROLLBACK discards all the changes, returning the database to the state it was in before BEGIN.

Statements outside an explicit BEGIN-COMMIT block are still treated as transactions in PostgreSQL — each statement is implicitly wrapped in its own. That is why a single INSERT is atomic without anyone writing BEGIN. The new thing transactions add is the ability to extend that wrapping across multiple statements.

Loading Exercise...

ACID, One Letter at a Time

The classic shorthand for what transactions provide is ACID: atomicity, consistency, isolation, and durability. Each letter represents a different concern.

Atomicity. A transaction either commits all of its changes or none of them. Atomicity extends the all-or-nothing guarantee from individual statements to groups of statements.

Consistency. A transaction takes the database from one valid state to another valid state. The validity rules come from the schema: foreign keys, CHECK constraints, NOT NULL, UNIQUE. Consistency is the property that says transactions cannot leave the database in a state that violates these rules.

Isolation. When several transactions run at the same time, they should not interfere in unsafe ways. Isolation is the property that says each transaction should behave as if it were the only one running, even when others are active at the same time. This is a more subtle property than atomicity or consistency — we’ll look at this later in this part.

Durability. Once a transaction has committed, the changes survive even if the database crashes immediately afterward. This depends on the DBMS writing the changes to durable storage, which it does as part of COMMIT.

Loading Exercise...

When to Use a Transaction

Not every group of statements needs an explicit transaction.

A useful test is to ask: if one statement succeeded but the next failed, would the database be in an inconsistent state that the application or its users would notice?

For the bank transfer flow above, the answer is yes: an account whose balance has changed without the corresponding change in another account is a state where the source and destination disagree about what just happened. So the two writes belong in one transaction.

For unrelated writes — say, withdrawing money from a bank account and changing the label of the account — the answer is usually no. Each write is independent; there is no inconsistent intermediate state. They can run as separate transactions.

The clearest signal that a transaction is needed is when the writes share a logical purpose and depend on each other to produce a coherent result.

Loading Exercise...

Transactions Work With Constraints, Not Around Them

Constraints reject invalid states at the row level. Transactions group changes so that the database moves between valid states. The two are complementary.

Consider the bank transfer again: subtract 100 from one account, add 100 to another. Assume that both accounts have a CHECK (balance >= 0) constraint to prevent going into debt.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If the first UPDATE would put account 1 below zero, the constraint rejects it, and the transaction can be rolled back.

Loading Exercise...

Common Mistakes

Three patterns come up regularly in early transaction work.

Forgetting to commit. Without a COMMIT, the changes do not persist. They are visible inside the transaction but invisible to other connections, and they vanish when the connection closes. A common version of this is opening psql, running an INSERT within a transaction that has been started with BEGIN, and being confused that another tool does not see the row — because the transaction has not been committed.

Holding the transaction open too long. A transaction that stays open for many seconds (because the application is doing slow work between statements) holds locks the whole time, which can block other transactions. Transactions should be as short as possible: open, do the writes, commit.

Mixing reads and writes that should not interleave. Reading a value from the database, computing a new value in application code, and writing it back to the database can be problematic. This can lead to problems when multiple transactions read the same value, compute new values based on it, and write them back — the last one to write wins, and the others are lost.

For the case, think of a scenario where two applications update the same account balance at the same time. Both read the current balance, compute a new balance by adding or subtracting an amount, and write it back. If they do not use transactions properly, one of the updates can overwrite the other, leading to an incorrect final balance.

Loading Exercise...

SQL Programming Exercise

The following SQL sequence practices the raw transaction shape: start a transaction, make related writes, commit, and verify the resulting state. The tasks begin with direct updates and then add the small amount of judgment needed to keep only related writes inside the same boundary.

Note that the grader does not check that you use BEGIN, COMMIT, and ROLLBACK correctly. It only checks the final state of the database. As the point of the exercise is to practice the transaction shape, do use BEGIN and COMMIT appropriately.

Loading Exercise...

Check Your Understanding

1. What is a transaction, and how does it relate to database statements?
2. What does atomicity guarantee about a multi-statement transaction?
3. Why is the “consistency” property in ACID closely related to schema constraints?