Definition
A transaction is a single logical operation on a database. It is often made up of several steps that must all succeed together, such as moving money from one bank account to another.
A transfer of £50 needs at least two changes: take £50 from one account and add £50 to the other. If the system failed between the two steps, £50 would vanish. Transaction processing is the set of rules a DBMS follows so that this cannot happen.
ACID
Definition
ACID stands for Atomicity, Consistency, Isolation and Durability: the four properties that a DBMS guarantees so that every transaction is processed reliably.
Key Point
| Property | Meaning | In the bank transfer |
|---|---|---|
| Atomicity | A transaction is completed in full or not carried out at all. It can never be left partially complete | If the £50 cannot be added to the second account, the withdrawal from the first is cancelled as well |
| Consistency | A transaction can only take the database from one valid state to another. Every change must obey the rules defined for the database, such as validation rules and referential integrity | A transfer to an account number that does not exist is rejected |
| Isolation | Transactions that happen at the same time cannot interfere with each other. The outcome is the same as if they had been carried out one after another | Two payments from the same account at the same moment each work from the correct balance |
| Durability | Once a transaction has been committed its changes are not lost, even if there is a power cut or system failure, because they are written to secondary storage | After the customer is told the transfer is complete, a crash cannot undo it |
Record Locking
When two users change the same record at the same time, one update can overwrite the other.
Worked Example
Worked Example: Two Updates That Clash
Two travel agents try to book the last seat on a flight. The record shows 1 seat left.
1. Agent A reads the record: 1 seat left
2. Agent B reads the record: 1 seat left
3. Agent A books the seat and saves the record: 0 seats left
4. Agent B books the seat and saves the record: 0 seats left
Both customers have been sold the same seat, because B's transaction used a value that A's transaction was in the middle of changing.
Two travel agents try to book the last seat on a flight. The record shows 1 seat left.
1. Agent A reads the record: 1 seat left
2. Agent B reads the record: 1 seat left
3. Agent A books the seat and saves the record: 0 seats left
4. Agent B books the seat and saves the record: 0 seats left
Both customers have been sold the same seat, because B's transaction used a value that A's transaction was in the middle of changing.
Definition
Record locking prevents more than one user or process from modifying a record at the same time. While one transaction is using a record it is locked, and no other transaction can change it until the lock is released.
With record locking, the record is locked as soon as Agent A's transaction starts to use it. Agent B has to wait until A has finished, then reads 0 seats left and cannot book. This is how the isolation property of ACID is achieved.
Warning
Deadlock: Locking can leave two transactions waiting for each other for ever. Transaction 1 has locked record X and needs record Y. Transaction 2 has locked record Y and needs record X. Neither can continue, so the DBMS has to detect the deadlock and cancel one of the transactions, undoing its changes, so that the other can finish.
Redundancy
The word redundancy is used in two ways in this topic, and both can be examined.
Definition
Data redundancy is the same data being stored more than once in a database when it does not need to be. It wastes storage space and can lead to inconsistencies if one copy is changed and the others are not. Normalisation removes it.
Definition
Redundancy in a transaction processing system means keeping duplicate copies of the data and hardware, so that if one part fails another takes over straight away with no loss of data or service.
Key Point
How redundancy protects a database:
• The database is mirrored on a second server, ideally at a different site, and every transaction is written to both
• RAID stores the data across several disks, so that one disk can fail without data being lost
• If the main system fails, the duplicate takes over and the organisation carries on working
• It costs more, because the extra hardware has to be bought and kept in step with the original
• The database is mirrored on a second server, ideally at a different site, and every transaction is written to both
• RAID stores the data across several disks, so that one disk can fail without data being lost
• If the main system fails, the duplicate takes over and the organisation carries on working
• It costs more, because the extra hardware has to be bought and kept in step with the original
Exam Tip
Read the context: In a question about normalisation or flat files, redundancy is unwanted repeated data. In a question about transactions, or about a system that must never go down, it is a deliberate duplicate kept as a spare.