Transactions and concurrency control
CIE A-Level Computer ScienceΒ· 10 min read
1. Transactions and ACID Propertiesβ β ββββ± 3 min
A transaction is a logical unit of work in a database that consists of one or more database operations (such as reads, writes, updates, or deletes). Transactions are designed to be treated as a single, indivisible unit to maintain data integrity even when errors or multiple concurrent accesses occur.
Transaction
A sequence of database operations that forms a single logical unit of work, which must either complete entirely (commit) or not complete at all (rollback) if any part fails.
Example:
Transferring $100 from a savings account to a checking account, which requires two updates: subtract 100 from savings, add 100 to checking.
All reliable database systems require transactions to follow four core properties, known as the ACID properties:
- Atomicity: All operations in the transaction complete successfully or none do; the database is rolled back to the state before the transaction started if any step fails.
- Consistency: The transaction must bring the database from one valid state to another, adhering to all predefined database rules (constraints, integrity rules) at all times.
- Isolation: Transactions running concurrently do not interfere with each other; each transaction behaves as if it is running alone on the database.
- Durability: Once a transaction is committed, its changes are permanent and will survive any system crash or power failure.
A bank transaction to transfer $500 from Account A to Account B fails halfway through after the system debits Account A but before it credits Account B. Which ACID property guarantees the database returns to its original state?
- 1
- Identify the requirement: we need the partial transaction to be undone, leaving no partial changes to the database.
- 2
- Recall that atomicity guarantees that all operations of a transaction complete, or none do. No partial changes are allowed.
- 3
- The database will roll back the debit to Account A, returning both accounts to their original pre-transaction balances.
Exam tip:
CIE exam questions often ask you to name and explain each ACID property; make sure you can define all four, not just name them, as you will be expected to describe what each means.
2. Common Concurrency Anomaliesβ β β βββ± 3 min
When multiple transactions access the same database data at the same time (concurrently), they can interfere with each other and produce inconsistent results, even if each transaction is correct when run alone. These inconsistencies are called concurrency anomalies.
Concurrency Anomaly
An inconsistency in the database state caused by uncontrolled concurrent execution of multiple transactions accessing the same data.
Three main types of concurrency anomalies are regularly tested in CIE exams:
- Dirty read: A transaction reads data that has been written by an uncommitted transaction. If the uncommitted transaction is rolled back, the read data is invalid.
- Non-repeatable read: A transaction reads the same row twice, and gets different values because another transaction modified and committed the row between the two reads.
- Phantom read: A transaction runs the same query twice, and gets a different number of rows because another transaction inserted or deleted rows and committed between the two queries.
Transaction 1 adds a 10% discount to all orders over $1000, and is not yet committed. Transaction 2 reads all orders, including the updated discount from Transaction 1, and generates a sales report. Transaction 1 is then rolled back. What type of anomaly is this?
- 1
- Note that Transaction 2 read data modified by an uncommitted transaction (Transaction 1) that was later rolled back, so the report contains invalid data.
- 2
- Match this description to the definition of a dirty read, which is specifically defined as reading uncommitted changes from another transaction.
- 3
- Confirm this is not a non-repeatable read, which requires the reading transaction to read the same data twice, which did not happen in this scenario.
3. Concurrency Control: Lockingβ β β βββ± 4 min
Locking is the most widely used concurrency control method. It works by having transactions acquire locks on data items before accessing them, to prevent conflicting access from other transactions.
Locking
A concurrency control method that restricts access to a data item to only compatible transactions, preventing conflicting changes to shared data.
Two common types of locks are used in most locking systems:
- Shared (read) locks: Multiple transactions can hold a shared lock on the same data item. Shared locks are acquired when a transaction only wants to read data, no changes.
- Exclusive (write) locks: Only one transaction can hold an exclusive lock on a data item at any time. Exclusive locks are acquired when a transaction wants to modify data.
Transaction A wants to read the balance of Account X and then update it. What lock should it acquire, and why?
- 1
- Transaction A needs to prevent another transaction from modifying Account X's balance while it completes its read and update sequence.
- 2
- Since Transaction A will eventually modify the balance, it needs to acquire an exclusive lock on Account X from the start (or upgrade a shared lock to exclusive later).
- 3
- An exclusive lock prevents any other transaction from reading or writing Account X until Transaction A commits or rolls back, so no conflicting changes can occur.
4. Alternative Concurrency Control Approachesβ β β β ββ± 2 min
Besides locking, other methods of concurrency control exist that avoid some of the downsides of locking such as deadlocks. Two common approaches tested in CIE are timestamp ordering and optimistic concurrency control.
- Timestamp ordering: Each transaction is assigned a unique timestamp when it starts. A transaction can only write a data item if its timestamp is older than the most recent timestamp of any read or write on that item. Conflicting transactions are rolled back and restarted with a new timestamp.
- Optimistic concurrency control: This approach assumes that conflicts between transactions are rare, so it allows transactions to execute without locking, and only checks for conflicts when the transaction tries to commit. If a conflict is found, the transaction is rolled back and restarted.
A low-traffic blog database where multiple authors edit posts at the same time, with very few conflicting edits. Would locking or optimistic concurrency control be a better choice here?
- 1
- The use case has low traffic and very few conflicts between transactions, which matches the design goal of optimistic concurrency control.
- 2
- Locking would add unnecessary overhead and reduce concurrency for a system with few conflicts, since locks are required for every access.
- 3
- Optimistic concurrency control has higher performance when conflicts are rare, as it avoids locking overhead during transaction execution.
The summary below compares the three main concurrency control methods:
Locking
Prevents conflicts by locking data before access
+ Pros: Good for systems with high contention (many conflicts)
β Cons: Can lead to deadlocks, reduces overall concurrency
Timestamp Ordering
Uses timestamps to order transactions, rolls back conflicts
+ Pros: No deadlocks
β Cons: High rollback rate for systems with high contention
Optimistic Concurrency
Checks for conflicts only at commit time
+ Pros: High concurrency, no deadlocks, low overhead for rare conflicts
β Cons: Poor performance if conflicts are common, many rollbacks
5. Common Pitfalls
Wrong move:
Confusing Atomicity and Consistency when describing ACID properties
Why:
Atomicity relates to partial transactions being undone, while Consistency relates to the transaction adhering to all database rules
Correct move:
Remember: Atomicity = all or nothing for transaction steps; Consistency = the final database state is always valid
Wrong move:
Calling any concurrency issue involving changing data a non-repeatable read
Why:
The key feature of a dirty read is reading uncommitted data that is later rolled back. Non-repeatable reads involve reading committed changed data twice
Correct move:
Check if the problem involves reading uncommitted changes: if yes, it is a dirty read, not a non-repeatable read
Wrong move:
Claiming multiple transactions can hold exclusive locks on the same data item
Why:
Exclusive locks are required for writes, and only one exclusive lock can be held per data item to prevent conflicting writes
Correct move:
Shared locks = multiple reads allowed; exclusive locks = single transaction access for writes only
Wrong move:
Thinking optimistic concurrency control uses locks to prevent conflicts
Why:
Optimistic concurrency control assumes conflicts are rare, so it does not use locks during execution
Correct move:
Only locking-based methods use locks during execution; optimistic concurrency control is lock-free until commit time
Wrong move:
Claiming Durability guarantees uncommitted changes survive a system crash
Why:
Durability only applies to changes from successfully committed transactions
Correct move:
Only committed changes are guaranteed to be durable, surviving system crashes; uncommitted transactions are rolled back after a crash
6. Quick Reference Cheatsheet
Concept | Key Summary |
|---|---|
Atomicity | All transaction steps complete, or none do |
Consistency | Transaction leaves database in a valid state |
Isolation | Concurrent transactions do not interfere |
Durability | Committed changes are permanent |
Dirty Read | Reads uncommitted, rolled-back data |
Non-repeatable Read | Same row read twice gives different values |
Phantom Read | Same query returns different number of rows |
Shared Lock | Multiple transactions can read same data |
Exclusive Lock | Only one transaction can modify data |
Optimistic Concurrency | Check conflicts at commit, no locks for rare conflicts |
7. Frequently Asked
What is the difference between a dirty read and a non-repeatable read?
A dirty read occurs when one transaction reads uncommitted changes from another transaction that is later rolled back, resulting in invalid data. A non-repeatable read occurs when a transaction reads the same row twice and gets different values, because another transaction modified and committed the row between the two reads.
When this came up on past exams
AI-estimated based on syllabus patterns β cross-check with official past papers for accuracy. Use only as revision-focus signals.
- 2022 Β· 2
Explain ACID properties of transactions
- 2023 Β· 2
Identify concurrency anomaly type
- 2024 Β· 2
Compare concurrency control methods
Going deeper
What's Next
Understanding transactions and concurrency control is critical for designing reliable multi-user database systems, a frequently tested topic in CIE A-Level Computer Science Paper 2. This sub-topic builds on core relational database concepts, and is a foundation for more advanced topics like database recovery and distributed databases. Mastering ACID properties and concurrency anomalies will help you answer both short-answer and essay-style questions on database design in the exam.