Community resourceWorksheet
OCR H446 1.3.2 Transactions, ACID, locking and redundancy
Part 11 of 13 · H446 1.3.2 · Databases
A booking system meets its hardest test when two people want the last seat or the power fails mid-update. This worksheet covers the H446 1.3.2 reliability content: transactions, the four ACID properties, record locking, and the two very different meanings of redundancy.
Students will:
- explain a transaction as a unit of work that completes fully or not at all
- apply each ACID property to a described failure or a clash between users
- explain how record locking stops two users changing the same record, and give one drawback
- separate harmful duplicated data from deliberate copies kept for recovery
- design a reliable transaction approach for a scenario with simultaneous users and a replicated database
Inside: 8 explanation cells, 3 multiple-choice questions, 2 fill-in-the-blanks cells and 4 written answers. 40 marks, about 35 to 50 minutes.
Series: H446 1.3.2 · Databases, part 11 of 13.
Shared by Coding PathwayVerified teacher
- 17 cells
- About 45 minutes
- CC BY-SA 4.0
- Shared 31 Aug 2026
- Updated 3 Sept 2026
Preview
The whole resource, exactly as a class sees it. Answers and marking are held back.
Transactions, ACID, locking and redundancy
A transaction is a logical unit of database work. Booking the last seat may require checking availability, creating a booking and reducing the count. These steps must behave reliably even when many users act at once or a failure occurs.
By the end, you will be able to
- explain transaction processing and the four ACID properties;
- trace a lost-update or overselling problem;
- explain how record locking protects shared data;
- evaluate helpful and harmful redundancy.
Reactivate: database consistency here means rules and constraints remain satisfied. It is not the same concept as removing transitive dependencies in 3NF.
Protect a shared record
Without coordination, A and B could both read spaces=1, both accept a booking and both write spaces=0. Two bookings would have been sold for one seat.
A record lock makes B wait while A changes the Event record. After A commits, B reads the new value 0 and rejects the booking. Locking prevents updates from overriding one another, although waiting and poor lock design can reduce performance.
Worked model: ACID as four guarantees
- Atomicity: all steps complete or none are kept. A booking row must not remain if payment/seat update fails.
- Consistency: each completed transaction leaves rules valid, such as spaces never becoming negative and foreign keys remaining valid.
- Isolation: concurrent transactions do not expose unsafe intermediate effects to one another; their result is as if run safely in sequence.
- Durability: once committed, the result survives later failure and can be recovered from persistent records or logs.
Atomicity is about the transaction as a whole. An atomic database field in 1NF is a different use of the word.
A power failure occurs after a transaction has committed. Which ACID property requires the committed booking to remain recoverable?
- AAtomicity
- BConsistency
- CDurability
- DIsolation
Which design most directly supports both isolation and durability?
- ALock the relevant records during conflicting work, then ensure committed changes are written to permanent storage
- BLock the entire database permanently and keep committed changes only in RAM
- CAdd the same customer data to every table and disable transactions
- DUse referential integrity as another name for every ACID property
Which statement correctly distinguishes transaction atomicity from an atomic field in First Normal Form?
- ABoth terms mean that every transaction may store several values in one field
- BTransaction atomicity removes partial dependencies; a 1NF atomic field guarantees rollback
- CBoth terms are another name for referential integrity
- DTransaction atomicity keeps all-or-none operations together; a 1NF atomic field stores one indivisible value for that attribute instance
- Atomicity
- Consistency
- Isolation
- Durability
- Redundancy
Guided checkpoint: failed money transfer
A transfer subtracts £40 from Account X and should add £40 to Account Y. The system fails after the subtraction.
Use this plan: state the incorrect partial state → name the ACID property → describe rollback or completion → state the restored rule.
Explain how transaction processing should handle the failed transfer, referring to Atomicity and Consistency.
Do not define only; apply both properties to the two balances.
Students type their answer here.
Explain how record locking prevents two users from booking the final seat and give one drawback.
Use read, update, wait and commit/release.
Students type their answer here.
Redundancy has two meanings in context
Uncontrolled duplicate customer addresses in many booking rows are harmful: copies may disagree and updates are repeated. Deliberate redundant copies, such as replicated data or recovery copies, can improve availability and resilience if synchronisation and storage costs are managed.
Therefore 'redundancy is bad' and 'redundancy is good' are both incomplete. Identify the purpose and control mechanism.
Evaluate storing a second copy of booking data at another site for recovery while also repeating customer email in every booking row.
Treat the two forms of redundancy separately.
Students type their answer here.
Independent transfer: limited workshop places
A college enrolment transaction checks capacity, inserts Enrolment and reduces spaces. Several students submit at once. The database is replicated for disaster recovery.
Design a reliable transaction approach. Apply all four ACID properties, explain record locking and evaluate the replicated copy.
Use one contextual consequence for every property.
Students type their answer here.
Closed-book checkpoint
Complete each sentence from memory. There is no answer bank and correctness is held for teacher review.
Review your understanding
Before submitting, check that you can explain the main distinction in your own words, apply it in an unfamiliar context and justify each consequence rather than only naming a feature.