Client server databases

Overview

Recall questions on why concurrent access threatens integrity, the lost update problem, and record locking, serialisation, timestamp ordering and commitment ordering.

What you need to know

Answer each one before you reveal it.

Questions from this subtopic

Each one with the model answer underneath.

1. 1

   What is a client server database system?

   Model answer

   A system in which the database itself is held and managed on a server, and the users work at separate client machines which send requests to it over a network. The client sends a query or an update, the server carries it out against the single central copy of the data and sends back only the results, and the client displays them. Its defining property at this level is that it provides simultaneous access to the database for multiple clients.

2. 2

   Give the advantages of holding a database on a server rather than distributing copies to each user.

   Model answer

   There is one copy of the data, so every user sees the same, current information and there is nothing to reconcile. Security is managed in one place, so access rights can be granted per user and enforced regardless of which machine they use. Backup is done once, centrally, and is therefore actually done. The server does the processing and returns only the results, so far less data crosses the network than if whole tables were sent to be searched at the client. Clients need less powerful hardware, and the database can be upgraded or its structure changed without touching every client. Rules such as referential integrity are enforced centrally and so cannot be bypassed.

3. 3

   Why does simultaneous access by multiple clients create a problem for the integrity of a database?

   Model answer

   Because two clients may work on the same data at the same time without either knowing about the other. Each reads the current values, each works out a change based on what it read, and each writes the result back. Since the second write is based on a value that was already out of date when it was read, it can overwrite the first client's work, leaving the database in a state that neither client intended and that does not follow from either transaction alone. The data ends up incorrect even though every individual operation succeeded.

4. 4

   Explain the lost update problem with a worked example.

   Model answer

   The lost update problem occurs when two clients edit the same record at the same time and one client's update is overwritten and lost. Suppose the stock level of an item is 40. Client A reads 40 and begins processing a sale of 5, intending to write 35. Before A writes, client B reads the same 40 and begins processing a sale of 3, intending to write 37. A writes 35. B then writes 37, calculated from the value it read before A's change. The stock now reads 37, but 8 items have been sold, so it should read 32. A's update has been lost entirely, and nothing in the database records that anything went wrong.

5. 5

   Name the four methods used to manage the problem of concurrent access.

   Model answer

   Record locks, serialisation, timestamp ordering and commitment ordering.

6. 6

   How does record locking prevent lost updates, and what is its drawback?

   Model answer

   When a client begins to update a record, the database places a lock on that record. While the lock is held, no other client may write to it, and depending on the type of lock may not read it either; other clients must wait until the transaction completes and the lock is released. In the stock example, client B cannot read the stale value of 40 because A holds the lock, so when B does proceed it reads 35 and correctly writes 32. The drawbacks are that waiting clients are held up, reducing throughput, and that deadlock can arise: if A locks record 1 and needs record 2 while B locks record 2 and needs record 1, neither can proceed and neither will release, so the system must detect the situation and abort one of the transactions.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.