Transaction processing, ACID, record locking and redundancy

Transaction processing, ACID (Atomicity, Consistency, Isolation, Durability), record locking and redundancy

1.3.2 Databases

Databases — Note 6 of 6

Candidates should understand what is meant by transaction processing, and scenarios where transaction processing takes place. Candidates should understand the problems that arise from transaction processing, and how these can be overcome. Candidates should understand the ACID rules for transaction processing, and why databases should be built to these standards. Candidates should understand how record locking prevents the overriding of data, and understand how record locking takes place.

What you need to know

A transaction is a single logical operation on a database, which may involve several changes. When many users access a database at once, rules are needed to make sure transactions are processed reliably and data is not lost or corrupted.

What to know

Key terms

Exam tipCommon mistake

Transaction: A single logical operation on a database, such as transferring money between two accounts, which may involve several changes that must all succeed together.
Other methods of controlling concurrent access include serialisation and timestamp ordering, where transactions are processed in the order of their timestamps.
Atomicity: A transaction is either completed in full or not at all. If any part fails, all changes are rolled back.
Consistency: A transaction takes the database from one valid state to another, never breaking validation or integrity rules.
Isolation: Transactions running at the same time do not affect each other; the result is the same as if they ran one after another.
Durability: Once a transaction is committed, its changes are saved permanently and survive a power failure or crash.
Record locking: A record being edited by one user is locked so other users cannot change it at the same time, preventing lost updates.
Without record locking, two users could read the same record, each make a change, and the second save would overwrite the first.
Deadlock: Two transactions each wait for a record locked by the other, so neither can continue. The DBMS must detect this and abort one.
Redundancy: Keeping duplicate copies of data or hardware (e.g. mirrored disks, backup servers) so the system can continue working if one part fails.
Common mistake: Confusing data redundancy (unwanted duplication, removed by normalisation) with redundancy for fault tolerance (deliberate duplication for reliability).

Exam technique

Tip:For each ACID property, give a short example in the context of the question, e.g. a bank transfer.