A query that is logically correct can still be too slow, and a sequence of individually valid statements can still corrupt business state when concurrent transactions interleave. Indexes address access paths; transactions address atomicity and isolation. Both introduce tradeoffs rather than free performance or safety.
Indexes maintain additional ordered or specialized structures that make some lookups cheaper at the cost of storage and write work. Transactions group changes into a unit whose commit, rollback, and isolation behavior determines what concurrent work is allowed to observe.
What you should be able to do
- Design clear relational tables and keys
- Write correct queries with filtering, joins, grouping, and aggregation
- Reason about indexes, transactions, and data integrity tradeoffs
Start with the problem, not the terminology
Picture two shoppers trying to buy the final unit of the same product. Both screens show “1 left.” If each request reads quantity = 1 and then subtracts one, can both checkouts succeed? They can—unless the database operation is designed around the business rule that inventory must never fall below zero.
The individual statements may all be valid. The failure is that the business invariant spans multiple actions and two sessions were allowed to interleave. Transactions and atomic updates exist to protect meaning across time, not merely to make syntax succeed.
Follow the reasoning, not just the result
For inventory, the invariant might be quantity >= 0. For a money transfer, the invariant may be that value removed from one account is added to the other exactly once. Transaction design starts from the rule that must remain true, not from the keyword BEGIN.
Instead of read quantity, decide in application code, then update, a conditional statement can attempt UPDATE inventory SET quantity = quantity - 1 WHERE product_id = ? AND quantity > 0. The application then checks whether one row actually changed. This shrinks the race window dramatically.
An index is another maintained structure. It can help the database locate matching rows without scanning everything, but every insert or update may also need to maintain that structure. An index is a trade: cheaper selected reads in exchange for storage and write work.
For “recent orders for one customer,” an index beginning with customer_id and then created_at often matches the access pattern. Reversing the columns creates a different ordering and may be far less useful. The optimizer can only exploit the structure you actually gave it.
A banking operation moves $50 from account A to account B. Identify the invariant and the transaction boundary.
Hint: The debit and credit are one logical operation. Think about what must happen if the second update fails after the first succeeds.
Show the tutor's reasoning
Both balance changes belong in one transaction so they commit together or roll back together. The transfer invariant is that the total value represented by the two accounts changes only according to the intended transfer, not because one half succeeded alone.
Try the same idea without scaffolding
You frequently query orders for one customer ordered newest-first within a date range. Propose a composite index, explain why its column order matches that query, then name one write-side cost the index introduces.
Common mistakes and misconceptions
Mistakes are useful because they reveal which mental model is being applied. Before moving on, make sure you can explain why each of these approaches fails.
Adding indexes to every column without accounting for write amplification and storage.
Assuming a composite index is equally useful regardless of column order.
Keeping transactions open while waiting for user input or remote network calls.
Assuming “ACID” means every database uses the same isolation behavior by default.
Where this fits in SQL & Relational Databases
Indexes and Transactions: Practical Notes is not meant to stand alone. It supports the broader course outcomes around design clear relational tables and keys, write correct queries with filtering, joins, grouping, and aggregation, reason about indexes, transactions, and data integrity tradeoffs. The useful question is not “Have I read this?” but “Can I use this idea when another topic depends on it?”
SubjectVision deliberately mixes tutorials, articles, MCQs, interview questions, notes, and guides because different stages of learning need different forms of effort. Explanation builds the model; examples make it concrete; retrieval reveals gaps; and application makes the idea durable.