FIT2094 Chap.5 DML, Transactions and Locking
DML, Transactions and Locking
Data manipulation and transaction management turn a specification into a controlled state change. INSERT creates rows, UPDATE changes existing rows and DELETE removes rows. A safe statement identifies the exact target, previews the relevant rows and verifies the affected-row count.
Oracle sequences can generate surrogate identifiers, but they do not replace natural uniqueness or relational integrity.
A transaction groups related changes into one logical unit of work. Atomicity makes the required changes succeed or fail together, consistency preserves invariants, isolation controls interference between concurrent work, and durability preserves committed results.
Locks help serialise conflicting operations, but boundaries should remain narrow enough to complete promptly. Verification happens before commit while rollback is still available.
Design a change from its invariant and failure path. State the permitted starting condition, the rows that must change together, the expected affected-row counts and the condition that authorises commit.
A guarded UPDATE can include the old status in its predicate so a stale transition changes no row instead of overwriting newer work. For multi-row operations, force a later statement to fail and confirm that rollback removes earlier changes. Concurrency reasoning also needs two sessions: identify which resource is locked, which session waits and whether the chosen order can deadlock.
Short, predictable access order reduces contention without weakening atomicity.
What this chapter covers
- 01
Translating a change specification into target rows
- 02
INSERT, UPDATE and DELETE operations
- 03
Oracle sequences and generated identifiers
- 04
Atomicity, consistency, isolation and durability
- 05
Transaction boundaries, commit, rollback and savepoints
- 06
Locking, serialisation and stale-read windows
- 07
Affected-row counts and before-and-after verification
Worked example · free
Reserve the last available place safely
- +1Begin one transaction for the capacity change and enrolment creation because they represent one business event.
- +2Update the workshop only where its identifier matches and available_places is greater than zero, decrementing the value by one.
- +2Inspect the affected-row count. Continue only when exactly one row changed; zero means the condition no longer holds.
- +2Insert the enrolment using the same workshop and participant identifiers, then verify the row and non-negative capacity.
- +1Commit only after both changes pass verification. Roll back the decrement if the enrolment insert or an invariant check fails.
Key terms
- Data manipulation language
- SQL operations that insert, update, delete or retrieve row data while the database enforces defined constraints.
- Transaction
- A logical unit of related database work whose changes are committed together or reversed together.
- Atomicity
- The transaction property requiring all operations in the unit to succeed or none of them to remain.
- Isolation
- The transaction property controlling how concurrent operations observe and interfere with uncommitted or changing data.
- Lock
- A database coordination mechanism that restricts conflicting access to a resource while an operation is in progress.
- Savepoint
- A named position inside a transaction to which later changes may be rolled back while earlier work remains pending.
- Sequence
- An Oracle object that generates numeric values, commonly used for surrogate identifiers without expressing business identity.
DML, Transactions and Locking FAQ
How do I make an UPDATE safer?
Preview the exact predicate with SELECT, compare the candidate count with the requirement, include the current-state precondition where relevant and inspect the affected-row count. Verify changed and unchanged rows before committing.
What should be inside one transaction?
Include every change required to preserve one business invariant. Keep unrelated work outside so locks are not held longer than necessary. State what must be true after commit and roll back when a required stage fails.
Why can concurrent users cause a correct statement to fail?
A value read earlier may become stale before a later update. Two users can both observe one remaining place and act on it. Guarded updates, appropriate locking or isolation and affected-row checks coordinate that conflict.
Does a sequence guarantee that a business event is unique?
No. It generates distinct numbers, so stored rows receive distinct surrogate values. A separate candidate-key or unique constraint is still needed to prevent two rows from describing the same real-world event.
Assessment move
For each data-change exercise, write a before state and required after state. Preview the target rows, choose the narrowest predicate and predict the affected-row count. Use a transaction when several statements carry one business event, and write the invariant that must hold at commit. Create failure tests for a missing parent, duplicate key, invalid status and later-stage error.
Practise concurrency with two sessions or a timeline: mark the read, lock, update and commit points, then identify the stale-read window. Finish every answer with observable evidence such as counts, values and rejected competing changes. Repeat each transaction with one deliberately failing later statement and inspect the database after rollback; this verifies atomic behaviour rather than merely describing it.
