What's changed: Initial version
3.3SQL and Transactions (SELECT/JOIN/Subqueries/GROUP BY, Views/Indexes, ACID, Concurrency Control, Failure Recovery, Distributed DB/NoSQL)
Learn SQL for manipulating tables (SELECT, JOIN, subqueries, GROUP BY, aggregate functions); the view, a virtual table, and the index, which speeds up searches; the ACID properties of a transaction, a unit of multiple operations; concurrency control (locking, two-phase locking, deadlock) and isolation levels that prevent interference between transactions; log-based failure recovery (rollback, roll-forward); and distributed databases, replication, and NoSQL for spreading data across multiple sites — at Applied Information Technology Engineer depth.
This section is one of the most heavily tested areas within the technology domain. Where FE focused on basic SQL syntax and understanding transaction terminology, AP goes further: reading complex SQL that includes subqueries, judging schedule serializability via two-phase locking, and distinguishing which anomalies can occur under which isolation level. Understanding the three anomalies — dirty read, non-repeatable read, and phantom read — together with which isolation level prevents each is key to scoring well.
3.3.1SQL, views, and indexes
- SELECT, JOIN (inner/outer), and subqueries — nesting one SELECT statement inside another SQL statement (e.g., used to narrow a condition, as in
WHERE dept_id IN (SELECT id FROM depts WHERE region = 'east')). GROUP BY aggregates rows in combination with aggregate functions (count, sum, avg, etc.). To further filter the aggregated result, useHAVING, notWHERE. - A view is a virtual table storing a saved SELECT statement, with no data of its own. It lets frequently used joins/aggregations be referenced concisely and can also serve access control by exposing only a subset of a base table's columns (the view's results track the base table as it is updated). An index is a lookup structure that speeds up searches on a given column (usually a B-tree). Searches get faster, but there is a tradeoff: every update (INSERT/UPDATE/DELETE) must also update the index, adding overhead.
3.3.2Transactions, concurrency control, and isolation levels
- The ACID properties: atomicity (all-or-nothing execution), consistency (integrity is preserved), isolation (unaffected by other transactions), durability (a completed result survives even a later failure). Two-phase locking (2PL) splits a transaction's execution into a growing phase, which only acquires locks, and a shrinking phase, which only releases locks, with the rule that once a lock is released, no new lock may be acquired. A schedule following 2PL is guaranteed serializability (the property that the result is the same as if the transactions had run one at a time, in some order).
- Isolation levels are stages of how strong isolation is; loosening them raises concurrency but increases the risk of anomalies. A dirty read is reading another transaction's uncommitted update (possible at the loosest level). A non-repeatable read is when reading the same row twice within one transaction yields different values because another transaction committed in between. A phantom read is when running the same search twice returns a different row count because another transaction inserted rows in between. Raising the level prevents more anomalies but lowers concurrency (throughput) — a tradeoff.
- Failure recovery is performed using logs (before-images and after-images). Rollback undoes an abnormally terminated transaction's updates using before-images (for a transaction failure). Roll-forward reapplies after-images recorded since the last backup (for backup recovery from a disk failure, etc.). Taking a checkpoint periodically narrows the range of log that must be traced back on failure, shortening recovery time.
- A distributed database places data across multiple sites (servers). Replication duplicates the same data across multiple sites, used to improve availability and spread read load (there are synchronous and asynchronous styles, depending on when updates are propagated). NoSQL covers databases not bound to the relational model (key-value, document, graph, etc.), chosen where large-scale horizontal distribution or a flexible schema is needed.
The staples: two-phase locking guarantees serializability via an acquire-only phase followed by a release-only phase; raising the isolation level prevents more anomalies but lowers concurrency; preventing dirty read, then non-repeatable read, then phantom read requires progressively higher isolation levels; rollback uses before-images, roll-forward uses after-images. SQL questions center on reading statements that include subqueries, and isolation-level questions center on judging which anomaly can occur in a given scenario.
Consider an inventory-management requirement: "List every product with a backorder quantity that has had zero shipments in the last 30 days." A natural way to build this is SELECT p.id, p.name FROM products p WHERE p.backorder_qty > 0 AND p.id NOT IN (SELECT s.product_id FROM shipments s WHERE s.shipped_at >= CURRENT_DATE - 30), using a subquery to fetch "product IDs shipped in the last 30 days" and using it as an exclusion condition in the outer SELECT. If this list also needs to be shown to the sales department but the inventory's cost data should stay hidden, defining a SELECT statement that omits the cost column as a view lets you provide it without granting direct access to the base table. Next, consider two staff members updating this inventory concurrently. If staff member A is mid-update on product X's stock row (lock acquired, not yet committed) and staff member B reads that same row, a dirty read occurs, and if A's update is later rolled back, B has seen a value that never really existed. Preventing this requires an isolation level that keeps uncommitted data hidden from other transactions (read committed or stricter, the default for many DBMSs). Furthermore, if another staff member inserts a new order row matching the same condition between the initial stock check and the order being finalized, the row count counted the first time can differ from the count taken later — a phantom read — so for operations that need the aggregated result to stay strictly fixed (such as a batch inventory-allocation run), the strictest isolation level, serializable, is chosen, sacrificing concurrency in favor of correctness. If this inventory system is replicated to another region in preparation for a data-center failure, synchronous replication is chosen when the latest state must always be guaranteed, but it trades off reduced write performance, since every write must wait for the remote site to confirm it; if some propagation delay is acceptable, asynchronous replication is chosen instead to prioritize throughput — a judgment made routinely in practice.
| Anomaly | What happens | Isolation level roughly needed to prevent it |
|---|---|---|
| Dirty read | Reads another transaction's uncommitted update | Read committed or stricter |
| Non-repeatable read | The same row's value changes within one transaction | Repeatable read or stricter |
| Phantom read | The row count changes for the same search condition | Serializable |
Trap: "A higher isolation level is always better" is wrong — raising the level increases lock contention and lowers concurrency (throughput), so in practice the minimum level that satisfies the business requirement is chosen. "A view holds real data, so updating it hurts performance" is also wrong: a view is just a saved SELECT definition and holds no data of its own (it is evaluated against the base table each time it runs). "More indexes always mean faster searches" is wrong too: every update triggers rebuilding of all the affected indexes, adding overhead, so indexes are designed by balancing search frequency against update frequency.
3.3.3Section summary
- A subquery uses the inner SELECT's result as the outer condition. A view = a saved SELECT definition (no real data); an index speeds up search but adds update overhead
- Two-phase locking guarantees serializability. Raising the isolation level prevents, in order, dirty read -> non-repeatable read -> phantom read, but lowers concurrency
- Rollback = before-images; roll-forward = after-images. Choose synchronous (consistency-first) or asynchronous (performance-first) replication for a distributed DB
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. While staff member A is mid-update (uncommitted) on product X's stock row, staff member B reads the same row, and A's update is later rolled back. B ended up reading a value that never actually existed. Which anomaly is this?
Q2. For a batch inventory-allocation process, you must absolutely avoid a discrepancy between the row count taken at the initial search and a later count, caused by another staff member inserting a new order row in between. Which isolation level is most appropriate to choose?
Q3. You want to expose the inventory list to the sales department too, but without showing the cost column, and without granting direct access to the base table. What is the most appropriate way to achieve this?