Client server databases — AQA A-Level Computer Science
A client-server database architecture distributes processing between client applications that submit queries and a central database server responsible for query execution, security, and data integrity. In multi-user systems, managing concurrent access through techniques such as record locking and timestamp ordering is vital to uphold ACID properties and prevent data corruption.
Client server databases explained
This statement requires you to know that in a client-server database system the database is held on a central database server, and that many client computers can connect to that server at the same time.
Read the full explanation
Each client sends requests, such as queries or updates, over a network, and the server processes them and returns results. Because the database is shared, the server must manage simultaneous access so that clients do not corrupt each other's data. It does this through mechanisms such as transaction control and locking, which temporarily restrict access to records being changed. Understanding this model means recognising that the database is centralised rather than copied onto every client, which allows many users to work with a single, consistent set of data at once.
Your focus
- Know that a client server database system provides simultaneous access to the database for multiple clients.
Which command word is this?
Set out the technical causes and effects step-by-step, showing how a specific mechanism (e.g., record locking) produces a concrete outcome (e.g., preventing a lost update).
Client server databases exam tips
Topic Overview
Client-server databases divide data management into two distinct layers: client applications that present data to users and dispatch queries, and a centralized database server running a Database Management System (DBMS). The client sends structured queries over a network, while the server executes query plans, enforces referential integrity, and returns only the necessary result sets.
This architecture forms the bedrock of modern multi-user enterprise applications, allowing thousands of distributed nodes to access consistent data stores simultaneously. Success in this topic requires understanding the mechanics of client-server separation alongside concurrency control mechanisms like record locking, timestamp ordering, and ACID principles.
Key Concepts
- →Client-Server Separation: Clients execute presentation and business logic, while the central server runs the DBMS engine, manages physical storage, and enforces relational integrity.
- →Concurrency Hazards: Interleaved execution of concurrent read and write operations can cause lost updates, uncommitted dependencies (dirty reads), and inconsistent analysis.
- →Record Locking: A pessimistic concurrency technique that reserves a specific row during an active transaction to guarantee isolation, at the risk of inducing deadlocks.
- →Timestamp Ordering: An optimistic or non-locking concurrency control technique where transactions are assigned chronological timestamps to establish serialisable execution order.
- →ACID Properties: The four transactional guarantees (Atomicity, Consistency, Isolation, Durability) that ensure database validity despite system failures and concurrent users.
Marking Points
- States that the database resides on a central server accessed by multiple clients over a network.
- Explains that the server handles requests from many clients at the same time.
- Describes that the server controls concurrent access, for example with locking or transactions, to keep data consistent.
- Contrasts client-server access with a single-user arrangement in which only one user can change the data at a time.
- Gives a realistic scenario, such as many booking agents using one shared database simultaneously.
Examiner Tips
- 💡Identify which component stores the data and which component makes requests in any scenario given.
- 💡Link simultaneous access to a practical outcome such as shared, up-to-date information.
- 💡Mention the role of the server in managing concurrent access when a question asks how multiple users are supported.
- 💡Contrast network traffic when comparing client-server systems to file-server systems; emphasize that client-server transfers only processed result sets rather than entire raw files.
- 💡In concurrency questions, always state the exact name of the hazard (e.g., 'lost update' or 'uncommitted dependency') rather than using vague phrases like 'the data becomes corrupted'.
- 💡Reference write-ahead transaction logging on non-volatile secondary storage when explaining the mechanics behind the Durability property.
Common Mistakes
- Describes each client holding its own full copy of the database, which removes the shared-access benefit; the single database resides on the server.
- Assumes simultaneous access means clients never conflict, when the server must still manage conflicting changes; mention locking or transactions.
- Confuses the client and server roles, stating the client stores and manages the database; the server holds and manages it while clients send requests.
- Assuming client-server means downloading the whole database: Students often believe clients download the entire table to filter rows locally, whereas the server executes the filter and returns only matching records, conserving bandwidth.
- Believing record locking locks the whole database: Record locking applies exclusively to the targeted tuple or record, allowing other clients unfettered access to all other records in that table.
- Thinking timestamp ordering causes deadlocks: Timestamp ordering does not lock resources; conflicts are resolved by aborting and restarting transactions, completely eliminating deadlocks.
Revision Plan
- 1Day 1: Diagram the client-server database architecture, distinguishing client responsibilities from DBMS server responsibilities.
- 2Day 2: Trace the lost update problem, uncommitted dependency, and inconsistent analysis using multi-column chronological timeline tables.
- 3Day 3: Study concurrency solutions by comparing the operational algorithms of two-phase record locking versus timestamp ordering.
- 4Day 4: Review ACID properties in detail, writing out explicit definitions and failure scenarios for each letter.
- 5Day 5: Complete AQA A-Level exam questions focusing on 6-mark and 9-mark evaluation prompts on database architectures and concurrency.
Exam Question Types
- 📋Architecture comparison: Contrasting client-server databases with file-server configurations in terms of network load, processing power, and security.
- 📋Concurrency trace questions: Providing a chronological sequence of reads and writes from two users and asking students to identify the hazard and explain the resulting inconsistency.
- 📋Mechanisms and trade-offs: Discussing the trade-offs between record locking (deadlock risk) and timestamp ordering (re-execution overheads).
Command Word Expectations (AQA)
Explain
Set out the technical causes and effects step-by-step, showing how a specific mechanism (e.g., record locking) produces a concrete outcome (e.g., preventing a lost update).
Discuss
Provide a balanced exploration of different perspectives or trade-offs (e.g., the benefits of concurrent throughput versus the risks of deadlocks and overhead costs), concluding with a justified assessment.
Compare
Identify explicit similarities and differences between two technical concepts (e.g., client-server versus file-server databases), linking them directly using comparative terminology.
How Students Lose Marks (Examiner Pitfalls)
Pitfall: Confusing record locking with locking an entire table or database, and failing to identify the resulting deadlock risk.
❌ Weak Answer (Loses Marks):Record locking stops anyone else from editing the database when someone is using it.
Example improved answer:Record locking restricts concurrent write access to a specific record or row while a transaction updates it, allowing other transactions to access unaffected records. However, if two transactions simultaneously hold locks on records needed by the other, a deadlock occurs, requiring intervention such as timeout termination or timestamp ordering.
Examiner Tip: Always specify the granularity of the lock (record-level) and state that other users can still access unaffected rows, while mentioning deadlock as the major drawback.
Pitfall: Explaining the lost update problem without demonstrating the exact chronological sequence of interleaved operations.
❌ Weak Answer (Loses Marks):A lost update happens when two users change data at the same time and one overwrites the other.
Example improved answer:A lost update occurs when Transaction A reads a record, and before Transaction A commits its update, Transaction B reads the original record. When Transaction A writes its modified value followed by Transaction B writing its value, Transaction B overwrites Transaction A's modifications without incorporating them, causing data loss.
Examiner Tip: Use a numbered or chronological trace showing Read(A), Read(B), Write(A), and Write(B) to secure maximum marks on concurrency hazard questions.
Step-by-Step Worked Solutions
Question: A ticketing company uses a client-server database architecture. Explain how record locking prevents the lost update problem during seat reservations, and evaluate one disadvantage of this technique.
- 1.Step 1: Explain initial lock acquisition. When Customer A selects a specific seat, the client sends a request and the database server places an exclusive write lock on that specific seat record, blocking concurrent write access.
- 2.Step 2: Trace concurrent access under lock. If Customer B attempts to reserve the identical seat simultaneously, the DBMS forces Customer B's transaction to wait or fail because the record is locked.
- 3.Step 3: Detail transaction completion and release. Customer A completes payment, the DBMS writes the updated reservation status to disk, commits the transaction, and releases the lock.
- 4.Step 4: Analyze the main disadvantage (Deadlock / Latency). If multiple transactions lock multiple records in conflicting sequences (e.g. Transaction 1 locks Seat A and awaits Seat B; Transaction 2 locks Seat B and awaits Seat A), a deadlock occurs, requiring the DBMS to abort and roll back transactions.
Final Answer: Record locking prevents lost updates by granting exclusive write access to a single transaction until committed; however, it risks deadlocks and increases system latency under high concurrency.
Question: Describe the respective roles of the client and the server when running a parameterised SQL search query, and identify the ACID property that guarantees committed changes survive an unexpected server crash.
- 1.Step 1: Define the client role. The client provides the user interface, captures input parameters, performs basic input validation, constructs the query request, and transmits it across the network.
- 2.Step 2: Define the server role. The database server authenticates the client, parses and compiles the SQL query execution plan, accesses secondary storage, executes the search via the DBMS engine, and returns only the filtered result set.
- 3.Step 3: Identify the relevant ACID property. Durability guarantees that once a transaction has committed, all modifications are recorded permanently in non-volatile storage (such as via write-ahead transaction logs) and will survive any subsequent system crash.
Final Answer: The client handles user interaction and query transmission while the server handles authentication, query execution, and data retrieval; Durability ensures committed transactions survive system crashes.
Active Recall Memory Test
What does each letter stand for in the ACID transactional model?
Key Fact: Atomicity (all or nothing), Consistency (maintains integrity rules), Isolation (independent concurrent execution), Durability (committed data survives system crashes).
Why does a client-server database generate substantially less network traffic than a file-server database?
Key Fact: The server executes queries locally and transmits only the filtered result set across the network, whereas a file-server must transmit whole table files to the client machine for local filtering.
Under what condition does a deadlock occur when using record locking?
Key Fact: A deadlock occurs when two or more transactions each hold an exclusive lock on a record that the other transaction requires to proceed, creating a cyclic dependency where neither can continue.
How does timestamp ordering resolve a situation where a transaction attempts to read data written by a newer transaction?
Key Fact: If a transaction with an older timestamp attempts to read or write a record that has already been accessed or modified by a newer transaction, the older transaction is aborted, rolled back, and restarted with a new timestamp.
Frequently Asked Questions
Why do client-server databases use record locking instead of table locking?
Record locking restricts access only to the specific rows being modified by an active transaction, leaving every other record in the table available for other clients to read and write. Table locking restricts access to the entire table, which severely degrades concurrency and throughput in multi-user systems. High-traffic systems like banking or e-commerce rely on record locking to allow thousands of customers to interact with the same database simultaneously.
What is the difference between a client-server database and a file-server database?
In a file-server database, the server acts merely as shared storage, requiring client computers to download entire database files over the network to execute queries locally using local processing power. In a client-server database, a dedicated DBMS engine resides on the server, processing queries internally and transmitting only matching results back to the client. This dramatically reduces network traffic, improves processing speed, and centralises security and access permissions.
How does timestamp ordering prevent deadlocks from occurring?
Timestamp ordering prevents deadlocks because transactions never lock records or wait for other transactions to release resources. Instead, every transaction receives a unique timestamp upon initialization, and operations are ordered strictly according to these timestamps. If a transaction attempts an operation that violates chronological serialisability, the DBMS immediately aborts and restarts that transaction rather than placing it in a waiting state.
What is an uncommitted dependency or dirty read?
An uncommitted dependency, also known as a dirty read, occurs when Transaction A updates a record but has not yet committed the transaction. If Transaction B reads this updated record and uses it for subsequent operations, and Transaction A then encounters an error and rolls back its changes, Transaction B has operated on data that technically never existed in the persistent database state.
Why is SQLite generally not classified as a full client-server database?
SQLite is an embedded, file-based database engine that runs in the same process memory space as the host application rather than as a separate server daemon listening on a network port. It writes directly to a single disk file and traditionally relies on whole-file or table-level locks during write operations. In contrast, client-server databases like PostgreSQL or MySQL run standalone server processes that handle concurrent remote network requests and fine-grained record-level concurrency.