COMP9120 · Database Management Systems
Transaction Management & Concurrency
Transaction management is the part of COMP9120 Database Management Systems at the University of Sydney that decides what it means for a database to stay correct while many users read and write at the same time. This chapter builds the ACID guarantees, the three classic concurrency anomalies, the precedence-graph test for conflict-serializability, and the locking protocols (2PL, Strict 2PL) that enforce it — the exact toolkit examined in the final exam's Transactions question.
What this chapter covers
- 01What a transaction is, and how BEGIN / COMMIT / ROLLBACK delimit an atomic unit of work
- 02The four ACID properties — and who enforces each (Recovery Manager, Concurrency Control, the developer)
- 03The three concurrency anomalies: lost update, dirty read (temporary update), and incorrect summary
- 04Serial vs serializable schedules, and why serializability is the correctness target
- 05The conflict rule: two ops conflict iff same item, different transactions, and at least one is a write
- 06Building a precedence graph and using the acyclic test to decide conflict-serializability
- 07Reading an equivalent serial order off the graph with a topological sort
- 08Shared / exclusive locks, Two-Phase Locking and Strict 2PL
- 09Deadlock: how it forms, and prevention vs detection-and-abort
Is this schedule conflict-serializable?
- +1Number the operations in time order and note the data items touched: A is read by T1 and T2 and written by T1; B is read and written by T2 only.
- +1Find the conflicts on A. Two ops conflict only if they hit the same item, belong to different transactions, and at least one is a write. r2(A) at step 2 comes before w1(A) at step 3 (read-then-write, different transactions), giving a precedence edge T2 -> T1.
- +1Check the remaining pairs. r1(A) and r2(A) is read-read, so it is NOT a conflict. B is touched only by T2, so no cross-transaction conflict exists on B.
- +1Build the precedence graph: two nodes T1 and T2, with a single edge T2 -> T1.
- +1Test for a cycle. There is only one edge and no way back, so the graph is acyclic. An acyclic precedence graph means the schedule IS conflict-serializable.
- +1Read off the serial order with a topological sort: the edge T2 -> T1 means T2 must come first, so the schedule is equivalent to the serial order T2 then T1.
Key terms
- Transaction
- One or more read/write operations executed as a single atomic unit of work, delimited in SQL by BEGIN and ending with COMMIT (make changes permanent) or ROLLBACK/ABORT (undo them all).
- ACID
- The four transaction guarantees: Atomicity (all-or-nothing), Consistency (each transaction moves the database from one valid state to another), Isolation (the concurrent result equals some serial order), and Durability (committed effects survive crashes).
- Serializable schedule
- An interleaved schedule whose overall effect equals that of SOME serial (non-interleaved) execution of the same transactions. It is the correctness target for concurrency control.
- Conflict
- Two operations conflict if and only if they act on the same data item, belong to different transactions, and at least one is a write. The conflicting pairs are read-write, write-read and write-write; read-read never conflicts.
- Conflict-serializable
- A schedule that is conflict-equivalent to a serial schedule — same operations, with every conflicting pair in the same relative order. It is tested with the precedence graph and implies serializability (but not conversely).
- Precedence graph
- A directed graph with one node per transaction and an edge Ti -> Tj whenever a conflicting operation of Ti precedes one of Tj. The schedule is conflict-serializable if and only if this graph is acyclic; a topological sort then gives the equivalent serial order.
- Two-Phase Locking (2PL)
- A locking protocol with a growing phase (acquire locks only) followed by a shrinking phase (release locks only). Once any lock is released, no new lock may be acquired. 2PL guarantees conflict-serializability when there is no deadlock.
- Strict 2PL & deadlock
- Strict 2PL holds all locks until commit or rollback, which additionally prevents dirty reads and cascading aborts. Its risk is deadlock — a circular wait such as T1 holding A and wanting B while T2 holds B and wants A — handled by prevention or by detection-and-abort of a victim.
Transaction Management & Concurrency FAQ
What is the difference between a serial and a serializable schedule?
A serial schedule runs the transactions one after another with no interleaving — always correct but with no concurrency. A serializable schedule may interleave operations, but its overall effect is equivalent to some serial order, so it is just as correct while allowing concurrency. In the exam you usually test the stricter, mechanical version — conflict-serializability — by drawing the precedence graph and checking that it is acyclic.
How is conflict-serializability related to two-phase locking?
They are two sides of the same goal. The precedence graph is an after-the-fact test: given a finished schedule, an acyclic graph means it is conflict-serializable. Two-Phase Locking prevents non-serializable schedules from ever occurring: by acquiring all locks before releasing any, it guarantees the resulting schedule is conflict-serializable. Strict 2PL goes further by holding locks to commit, which also stops dirty reads — at the cost of possible deadlock.
Can AI help me with transactions and concurrency in COMP9120?
Yes, as a study aid for understanding the method. Sia, the AskSia AI tutor, can explain step by step how to classify conflicts, build a precedence graph, decide conflict-serializability, or trace a schedule under Strict 2PL, using practice-style scenarios so you learn the technique. It is built to explain reasoning, not to hand over answers to assessed work — use it to check that you can reproduce each step yourself, and always follow the University of Sydney's academic-integrity rules on your own submissions.
Studying with AI? Sia — free AI data science tutor works through COMP9120 step by step.
Exam move
Transactions are a method topic, so practise the procedure until it is automatic rather than memorising facts. Drill three moves: (1) classify any pair of operations as conflicting or not (same item, different transactions, at least one write); (2) build a precedence graph and check for a cycle — acyclic means conflict-serializable, and a topological sort gives the serial order; (3) trace a schedule under Strict 2PL with the S/X compatibility rule (only shared-with-shared is compatible), watching for a circular wait that signals deadlock. The final exam is paper-based, closed book, and two hours (120 minutes) for about 131 marks, so the roughly 23-mark Transactions question deserves around 21 minutes — comfortably inside the paper with time to re-check each graph. Keep the hurdle in mind: you must score at least 40% in the final exam and at least 50% overall, so this reliable, marks-per-step topic is worth banking. Confirm the exact mark split and timetable on Canvas.
