INFO20003 Chap.10 Transactions and Database Administration
Transactions and Database Administration
Transactions group several data changes into one logical unit and protect correctness while many users operate concurrently. This chapter connects atomicity, consistency, isolation and durability to invalid intermediate states, serialisable schedules, locks, waits and deadlocks.
It then broadens to logging, crash recovery, capacity planning, backup strategy and the decisive operational habit of testing a restore instead of trusting a completed copy job. Atomicity ensures that a transaction's changes take effect together or are undone together. Consistency means a correct transaction moves the database between states satisfying declared rules; it does not make flawed program logic correct.
Isolation constrains observations and interference among concurrent transactions, while durability preserves committed effects across failure. Schedules interleave operations from several transactions. Conflict serialisability asks whether precedence edges between conflicting reads and writes form an acyclic graph equivalent to some serial order.
Locking enforces compatible access, and strict two-phase locking retains exclusive locks until completion to support isolation and recoverability. Blocking is ordinary waiting; deadlock exists when a wait-for graph contains a cycle. Detection selects a victim for rollback, while prevention can impose resource order or timestamp rules. Logs provide evidence for recovery.
Write-ahead logging requires the relevant log record to reach stable storage before a changed data page, and commit evidence must be durable before success is reported. After a crash, recovery identifies which committed effects need redo and which incomplete effects need undo under the stated protocol. Checkpoints limit the log region that must be examined but do not replace backups.
Media loss requires restored data plus retained logs; site loss requires independent copies and a broader continuity plan. Recovery point objective bounds acceptable lost work and recovery time objective bounds acceptable outage. A backup is useful only if its contents, permissions, dependencies and log chain can be restored and validated. Administration also covers monitoring, security, capacity and controlled change.
Establish normal workload baselines, track growth and contention, test index or configuration changes against representative work, and preserve rollback plans. Diagnose with evidence at the right layer: a lock graph for waits, a plan for slow SQL, storage and buffer metrics for pressure, and restore logs plus application checks for recoverability.
Each operational control should name the risk, detection signal, preventive action, recovery action and proof that the service returned correctly.
What this chapter covers
- 01
Transaction boundaries
- 02
ACID properties
- 03
Concurrent schedules
- 04
Shared and exclusive locks
- 05
Deadlock cycles
- 06
Write ahead logging
- 07
Backup and recovery
- 08
Capacity planning
Worked example · free
Recover a failed transfer
- 1Before the transfer, balances are 600 and 300.
- 1An uncommitted failure must undo the debit and restore the initial state.
- 1A committed transfer must recover as 480 and 420.
- 1Isolation prevents others from treating 480 and 300 as a valid final state.
Key terms
- Atomicity
- The guarantee that all effects of a transaction occur or none do.
- Isolation
- The control of interference among concurrently executing transactions.
- Durability
- The guarantee that committed effects survive later failures.
- Serializability
- Equivalence of a concurrent schedule to some serial transaction order.
- Deadlock
- A cycle of waits in which each transaction needs a resource held by another.
- Recovery log
- A record used to redo committed changes and undo incomplete changes after failure.
Transactions and Database Administration FAQ
What evidence proves a backup strategy works?
A successful copy job proves only that bytes were written. Perform a timed restore using the retained backup and log chain, validate schema and application-level facts, record achieved recovery point and recovery time, and test access credentials plus dependencies. That exercise demonstrates recoverability for the declared failure scenario.
How are deadlocks identified?
Represent waiting transactions in a wait-for graph. A cycle means the transactions cannot all progress. Detection can abort a victim to release locks, while prevention can impose ordering rules that stop the cycle forming.
Why is a backup not enough by itself?
A completed copy does not prove that data, credentials and logs can restore service within the required time. Isolated restore tests validate consistency, recovery point and elapsed recovery time before a real incident.
How does a transaction log support durability?
The log records changes in an order that lets recovery redo committed work even when final data pages were not all written before failure. It also provides evidence for undoing incomplete transactions.
Exam move
For each anomaly, draw the interleaved operations and label the conflicting read or write. Translate waits into a graph to detect cycles and practise one victim-abort resolution. For ACID questions, attach each property to a visible before, intermediate or after state rather than repeating definitions. Build a recovery sequence from backup or checkpoint through logs, redo, undo and validation.
In administration answers, name the risk, detection signal, preventive control, recovery action and evidence that recovery worked. Keep concurrency control, crash recovery, backups and capacity planning separate because each provides a different guarantee.
Working through Transactions and Database Administration in INFO20003? Sia is AskSia’s AI Computer Science tutor — ask any INFO20003 Transactions and Database Administration question and get a clear, step-by-step explanation grounded in how INFO20003 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.