Transactions and Views

IB Syllabus: A3.3.5: Describe different database views (virtual, materialised). A3.3.6: Describe how transactions maintain data integrity (ACID, TCL commands).

HL Only. This entire page covers content assessed at HL level only.

Part 1: Database Views (A3.3.5)

What a View Is

A view is a named, stored query that behaves like a virtual table. The query that defines the view is stored once; users SELECT from the view as if it were a real table, and the DBMS substitutes the underlying query at run time (or, for materialised views, returns the precomputed result).

A typical use:

CREATE VIEW PremiumMemberSummary AS
SELECT MemberID, FirstName, Surname, COUNT(*) AS BookingCount
FROM Member
INNER JOIN Booking ON Member.MemberID = Booking.MemberID
WHERE Member.Tier = 'Premium'
GROUP BY Member.MemberID, FirstName, Surname;

Once defined, users can write SELECT * FROM PremiumMemberSummary WHERE BookingCount > 10; without ever knowing about the underlying JOIN.

“View” in the 2027 syllabus means a stored database construct, not just a query result. This differs from the old syllabus where “view” sometimes referred to any ad-hoc query result. Mark schemes referencing the old usage may not match the new definition exactly.

Virtual Views

A virtual view stores only the query definition, not the data. When a user SELECTs from the view, the DBMS runs the underlying query against the current state of the source tables and returns fresh results.

Properties of virtual views:

Always current: the result reflects the data as it is right now.
No extra storage: only the query definition is saved.
Re-computed on every access: a slow underlying query stays slow every time the view is read.
No additional write overhead: updates to the source tables do not trigger view maintenance.

Use a virtual view when the underlying query is fast enough, or when consistency with live data is more important than read performance.

Materialised Views (Snapshot Views)

A materialised view (sometimes called a snapshot view) stores the precomputed result of the query. Reads against the view are as fast as reading any other table, the JOINs and aggregations have already been done.

Properties of materialised views:

Fast reads: no joins or computation at query time.
Uses storage: the result rows physically exist on disk.
Can be stale: the stored result reflects the source tables at the last refresh, not right now.
Needs refreshing: either on a schedule, on demand, or triggered by source-table changes.

Use a materialised view for expensive queries that are run far more often than they need to be updated, reporting dashboards, daily sales summaries, leaderboards updated every hour.

Virtual vs Materialised: Side-by-Side

Aspect	Virtual view	Materialised view
Storage	Definition only	Definition + precomputed result rows
Freshness	Always current	Stale between refreshes
Read speed	As slow as the underlying query	As fast as a regular table
Write impact on source	None	Refresh process must run after source changes
Refresh required	No	Yes, on demand, on schedule, or triggered
Best for	Lightweight queries, security/simplification, when freshness matters	Read-heavy expensive queries where staleness is tolerable

Why Use Views? Six Benefits

Several benefits apply to both kinds of view:

Hiding data complexity. Wrap a 5-table JOIN behind a single view; users SELECT * FROM it.
Query simplification. Common reports become one-line queries against the view.
Security. Grant users access to the view (which only exposes some columns or rows) instead of access to the underlying table. A view can show employees their own salary row but not anyone else’s.
Independence. When the underlying schema changes (a column is renamed, a table is split), the view can be updated to preserve the same external interface; the queries that use it stay unchanged.
Data consistency. Multiple users querying through the same view always apply the same logic; no two developers can write slightly different versions of the same “active members” query.
Performance (materialised only). Precomputed results turn slow joins and aggregates into fast point-lookups.

Read-Only vs Updatable Views

Some views support INSERT, UPDATE, and DELETE, changes propagate to the underlying tables. The DBMS allows this only if the view’s query is simple enough to map writes back unambiguously (typically: single source table, no aggregates, no DISTINCT, every primary-key column included).

Views over joins or aggregates are normally read-only; attempting to write to them triggers an error.

Part 2: Transactions and ACID (A3.3.6)

What a Transaction Is

A database transaction is a logical unit of work that is executed in full or not at all. A transaction may contain one SQL statement or hundreds; what matters is that they are treated as a single, indivisible operation.

The canonical example is a bank transfer:

BEGIN TRANSACTION;

UPDATE Account SET Balance = Balance - 250 WHERE AccountID = 1001;  -- debit
UPDATE Account SET Balance = Balance + 250 WHERE AccountID = 2002;  -- credit

COMMIT;

Both updates must succeed, or neither must take effect. If the debit succeeds but the credit fails (network drops, application crashes, validation rejects the destination account), the 250 currency units have vanished from the system. A transaction prevents this: either both are saved together at COMMIT, or both are undone together at ROLLBACK.

Transactions are the database’s mechanism for “all of these changes, or none of them.” Without them, multi-step operations can leave the database in a half-finished state, exactly the failure mode that destroys trust in a system.

The ACID Properties

The four ACID properties together describe the guarantees a transaction provides. Each property has a specific job:

Property	What it guarantees
Atomicity	All operations in the transaction commit together, or none do. There is no partial commit.
Consistency	The transaction takes the database from one valid state to another valid state. Constraints (primary keys, foreign keys, types, CHECK rules) are enforced. Invalid states are rejected.
Isolation	Concurrent transactions appear to run one at a time. The intermediate state of one transaction is not visible to others.
Durability	Once a transaction has committed, its effects survive a system failure (power loss, crash). The DBMS uses logs and checkpoints to guarantee this.

A standard 4-mark “Explain ACID property X” question follows the pattern: state the property + give the mechanism + give the consequence. Mark schemes explicitly cap answers at one mark if students just name the four properties without elaboration.

Atomicity in Detail

Mechanism. Operations executed inside BEGIN TRANSACTION ... COMMIT are tracked by the DBMS. If anything fails before COMMIT, the DBMS undoes every change made since BEGIN TRANSACTION, returning the database to its prior state.

Consequence. Multi-step operations like a money transfer cannot leave the system in a half-finished state. If the credit step fails, the debit step is rolled back so the money never disappears.

Without atomicity: the lost-update / orphaned-debit scenarios that destroy trust in financial systems.

Consistency in Detail

Mechanism. Every transaction must respect all schema-level constraints: primary-key uniqueness, foreign-key referential integrity, declared data types, CHECK constraints, and any application-level invariants enforced by triggers.

Consequence. The database moves from one valid state to another. A transaction that tries to leave the database in an invalid state (a foreign-key reference to a row that does not exist, a negative bank balance violating a CHECK constraint) is rejected.

Without consistency: orphan rows, broken integrity, and the kind of corruption that takes manual cleanup to repair.

Isolation in Detail

Mechanism. When two transactions run concurrently, the DBMS coordinates them using locks (pessimistic) or versioning (optimistic / multi-version concurrency control) so that the result is the same as if they had run one after the other. Intermediate, uncommitted state from one transaction is invisible to other transactions.

Consequence. Concurrent users cannot read each other’s half-finished work and cannot accidentally overwrite each other’s updates. Specific issues that isolation prevents:

Dirty read: reading data that has been written by an uncommitted transaction and may yet be rolled back.
Lost update: two transactions read the same value, both modify it, and the second UPDATE overwrites the first.
Phantom read: a query returns different rows on a re-execution within the same transaction because another transaction inserted matching rows in between.

Without isolation: the classic “two customers book the last seat” race condition.

Durability in Detail

Mechanism. Before COMMIT returns, the DBMS writes the transaction’s changes to a transaction log on persistent storage. Even if the server loses power microseconds later, the log can be replayed during recovery to reconstruct the committed state.

Consequence. A user who sees the message “Transaction committed” can trust the change is permanent. Power cuts, kernel panics, and disk failures cannot make committed transactions disappear (within the limits of the underlying storage, a total disk failure still needs backups).

Without durability: the system answers “yes, your booking is confirmed” but the booking is lost when the power flickers.

Transaction Control Language (TCL)

The three SQL TCL commands you must know:

BEGIN TRANSACTION;     -- mark the start of a transaction
...
COMMIT;                -- save all changes since BEGIN TRANSACTION
-- or --
ROLLBACK;              -- undo all changes since BEGIN TRANSACTION

COMMIT makes the transaction’s changes permanent and visible to other transactions. ROLLBACK discards them as if they had never happened. Some DBMSs use START TRANSACTION or simply BEGIN; any of these forms is accepted.

Implicit transactions. Most DBMSs treat each individual DML statement as its own one-statement transaction by default (auto-commit). You only need to write BEGIN TRANSACTION when you want to group several statements into one logical unit.

Concurrency Control

Even with BEGIN TRANSACTION, the DBMS still has to coordinate concurrent transactions. Two main strategies:

Pessimistic concurrency control (locking).

Acquire locks before reading or modifying data.
Other transactions wait until the lock is released.
Simple to reason about; risk of slow throughput if locks are held too long, or deadlocks if two transactions hold locks each other wants.

Optimistic concurrency control (versioning).

Read freely; check at commit time whether anyone else changed the data.
If yes, abort and retry; if no, commit.
Faster under low contention; can do extra work that gets thrown away under high contention.

Most production systems use a hybrid, with the choice configurable at the isolation level (Read Uncommitted, Read Committed, Repeatable Read, Serializable, listed from weakest to strongest). These specific levels are useful background but go beyond what most syllabi cover.

Why This All Matters

Transactions and ACID are why a relational database can be the trusted system of record for banks, hospitals, governments, and any other organisation where the integrity of every row matters. NoSQL databases sometimes trade some of these guarantees away (covered in Alternative Databases and Distributed Databases), a deliberate choice with consequences.

Worked Examples

Example 1: Defining a Virtual View

The sports club’s coaches want a summary of active members and their booking counts. Wrap the multi-table query as a view:

CREATE VIEW ActiveMemberSummary AS
SELECT m.MemberID,
       m.FirstName,
       m.Surname,
       m.Tier,
       COUNT(b.BookingID) AS BookingCount
FROM   Member m
LEFT JOIN Booking b ON m.MemberID = b.MemberID
                    AND b.BookedOn >= '2026-01-01'
GROUP BY m.MemberID, m.FirstName, m.Surname, m.Tier;

Coaches can now run simple queries against the view:

SELECT FirstName, Surname, BookingCount
FROM   ActiveMemberSummary
WHERE  Tier = 'Premium' AND BookingCount > 10;

Every read against the view re-runs the underlying JOIN, always current, no extra storage. If the JOIN gets too slow as data grows, convert to a materialised view:

CREATE MATERIALIZED VIEW ActiveMemberSummary AS ...
-- and refresh on demand:
REFRESH MATERIALIZED VIEW ActiveMemberSummary;

(Exact syntax varies by DBMS, PostgreSQL uses the form above; Oracle and SQL Server have their own.)

Example 2: An Atomic Bank Transfer

A customer at Coral Bank transfers 250 dollars from their current account (AccountID = 1001) to their savings account (AccountID = 2002). The transaction must be all-or-nothing:

BEGIN TRANSACTION;

UPDATE Account
SET    Balance = Balance - 250
WHERE  AccountID = 1001;

-- Check the source account did not go below zero
-- (handled by the application or a CHECK constraint)

UPDATE Account
SET    Balance = Balance + 250
WHERE  AccountID = 2002;

INSERT INTO Transfer (FromAccountID, ToAccountID, Amount, TransferredAt)
VALUES (1001, 2002, 250, '2026-05-22 09:14:00');

COMMIT;

If any statement fails, the source account does not exist, the destination account is closed, the constraint check fails, the application calls ROLLBACK instead of COMMIT and none of the three statements take effect.

Example 3: ACID in Action, the Bookable Seat

Two friends try to book the last seat at the same outdoor cinema, milliseconds apart:

Time	Transaction A (Aroha)	Transaction B (Marlon)
t1	`BEGIN TRANSACTION`
t2	`SELECT AvailableSeats FROM Show WHERE ShowID = 'S1'` -> returns 1
t3		`BEGIN TRANSACTION`
t4		`SELECT AvailableSeats FROM Show WHERE ShowID = 'S1'` -> returns 1
t5	`UPDATE Show SET AvailableSeats = 0 WHERE ShowID = 'S1'`	(waits on row lock)
t6	`INSERT INTO Booking ...`	(still waiting)
t7	`COMMIT`	(lock released)
t8		`UPDATE Show SET AvailableSeats = 0 WHERE ShowID = 'S1'`, re-reads, sees 0, application logic detects no seats, calls `ROLLBACK`

Without isolation, both transactions could read AvailableSeats = 1 at t2 and t4, both think they got the seat, both COMMIT, and the cinema oversells. With proper isolation, only Aroha gets the seat and Marlon’s transaction rolls back cleanly.

Quick Check

Q1. Which best describes a virtual view?

Q2. Which situation most strongly favours a materialised view over a virtual view?

Q3. A bank transfer debits one account and credits another. Which ACID property ensures that either both updates take effect or neither does?

Q4. Which ACID property guarantees that a transaction's committed changes survive a sudden power failure?

Q5. A transaction has executed three UPDATE statements but has not yet committed. The application detects an error and calls ROLLBACK. What is the effect?

Q6. Which problem is prevented by the Isolation property of ACID?

Trace the Transaction

For each scenario, type the final balance of the account or the result of the transaction.

Bank transfer scenarios. Account A starts with 1000, Account B starts with 200. Each scenario runs a transaction that tries to transfer 300 from A to B. Type the final balance of Account A after the scenario completes.

Scenario	Final balance of A
Both UPDATEs succeed; `COMMIT` is called.
First UPDATE succeeds; the second UPDATE fails; `ROLLBACK` is called.
Both UPDATEs succeed; the application crashes before `COMMIT`, the DBMS auto-rolls-back.
Both UPDATEs succeed; `COMMIT` is called; the server then loses power. (Durability applies.)

Fill in the Blanks

Complete the TCL commands and ACID terms.

TRANSACTIONS AND ACID
=====================
A transaction begins with the command
.

To save its changes permanently, the transaction ends with
.

To undo every change since the transaction began, use
.

THE ACID PROPERTIES
-------------------
 = all or nothing
 = valid state to valid state
 = concurrent transactions do not interfere
 = committed changes survive failure

Spot the Error

A developer wrote a money-transfer code block. One line shows the mistake. Click the line with the error, then choose the correct fix.

1-- Transfer 250 from account 1001 to account 2002 2UPDATE Account SET Balance = Balance - 250 WHERE AccountID = 1001; 3UPDATE Account SET Balance = Balance + 250 WHERE AccountID = 2002; 4-- (no COMMIT, no BEGIN TRANSACTION anywhere in the file)

Pick the correct fix:

Identify the Property

Transaction A has written a new value but has not yet committed. Transaction B reads that value, makes a decision based on it, and commits. Transaction A then rolls back. Which ACID property would have prevented this situation if it had been enforced strictly?

Type the property name (e.g. Atomicity).

A user receives the message "Transaction committed" and shuts down. Seconds later the database server loses power. After it restarts, the user's transaction has been lost. Which ACID property has been violated?

Type the property name.

Practice Exercises

Core (HL)

[Core] Views (HL) [4 marks] Define the terms virtual view and materialised view, and state one key difference between them.
[Core] Transactions (HL) [2 marks] Define the term database transaction.
[Core] Transactions (HL) [4 marks] State the four ACID properties and give a one-sentence summary of each.

Extension (HL)

[Extension] Transactions (HL) [3 marks] Explain why atomicity is essential when a bank transfer is implemented as two UPDATE statements.
[Extension] Transactions (HL) [3 marks] Explain how transaction durability is achieved, even when the server may crash at any moment.
[Extension] Transactions (HL) [3 marks] Describe the role of the three TCL commands BEGIN TRANSACTION, COMMIT, and ROLLBACK in implementing the ACID properties.
[Extension] Transactions (HL) [4 marks] Explain how the database prevents two customers from booking the same final seat at the cinema at the same time. Refer to the Isolation property and to row locking.

Challenge (HL)

[Challenge] Views (HL) [6 marks] Evaluate whether a reporting team should use a virtual view or a materialised view for a “top 100 customers this month” report that is opened roughly 5000 times per day. Refer to read performance, freshness, storage, and refresh strategy.
[Challenge] Transactions (HL) [8 marks] Discuss the trade-offs an engineering team faces when choosing between a strongly ACID-compliant relational database and a NoSQL database that offers weaker consistency guarantees in exchange for higher write throughput. Refer to specific properties and to scenarios where each choice is appropriate.

Note for IB CS learners: Transactions and ACID (A3.3.6) move to HL only in 2027 (they were SL+HL in the old syllabus). SL students under the new syllabus will not see these. For A3.3.5 (virtual vs materialised views), the new syllabus uses “view” in a specific stored-database-construct sense that differs from older usage, watch out for that terminology trap. For “Explain ACID property X” questions, mark schemes cap at one mark if you only name the property without expansion, always give the mechanism and the consequence.

Connections

Previous: SQL Updates. The INSERT, UPDATE, and DELETE statements that transactions wrap into atomic units.
Previous: SQL Queries. Views are stored SELECT queries; the same query syntax defines both.
Related: Data Types and Keys. The constraints that the Consistency property enforces.
Related (HL): Distributed Databases, ACID becomes much harder to provide across multiple machines; distributed systems sometimes trade some guarantees for partition tolerance (CAP theorem).
Related (HL): Alternative Databases. Many NoSQL stores offer weaker consistency models (BASE rather than ACID) in exchange for scalability.