Worksheet

A11.7 Client server databases and concurrent access

Databases and big data · A level · OCR H446 1.3.2, AQA 7517 4.10.5, Eduqas A500QS 2.5 · about 60 min

What this lesson is about

Many clients, one database: the lost update, and the four ways to prevent it: record locks, serialisation, timestamp ordering and commitment ordering.

• Client server databases
• The lost update
• 1. Record locks
• 2. Serialisation
• 3. Timestamp ordering
• 4. Commitment ordering

Questions 6 marks in all

1. [1 mark]Two clients read a score of 10. One writes 12, then the other writes 13. What is this problem called?

   1. AA lost update
   2. BA deadlock
   3. CA deletion anomaly
   4. DAn orphan record

2. [1 mark]Transaction A has locked record X and waits for Y; transaction B has locked Y and waits for X. What is this?

   1. ADeadlock
   2. BSerialisation
   3. CA lost update
   4. DTimestamp ordering

3. [1 mark]In timestamp ordering, transaction 3 tries to write a record whose read timestamp is 5. What happens?

   1. ATransaction 3 is aborted, because a younger transaction has already read the record
   2. BThe write goes ahead and the write timestamp becomes 3
   3. CTransaction 3 waits for transaction 5 to finish
   4. DTransaction 5 is aborted

4. [1 mark]What does this print?

   read_ts, write_ts, aborted = {}, {}, set()
   schedule = [(1, "read", "x"), (2, "write", "x"), (1, "write", "x"), (3, "read", "x")]
   for ts, op, rec in schedule:
       if ts in aborted:
           result = "skipped"
       elif op == "read":
           result = "abort" if ts < write_ts.get(rec, 0) else "ok"
           if result == "ok":
               read_ts[rec] = max(read_ts.get(rec, 0), ts)
       else:
           late = ts < read_ts.get(rec, 0) or ts < write_ts.get(rec, 0)
           result = "abort" if late else "ok"
           if result == "ok":
               write_ts[rec] = ts
       if result == "abort":
           aborted.add(ts)
       print(f"T{ts} {op}: {result}")

5. [1 mark]Which are advantages of a client server database over separate copies of the data on each computer?

   Tick every answer that is true.

   1. AEvery client sees the same, up-to-date data
   2. BIntegrity rules and access rights are enforced in one place
   3. CThe server can never be a single point of failure
   4. DConcurrent updates can never cause problems

6. [1 mark]How does serialisation prevent lost updates?

   1. AIt makes transactions act as if they ran one after another, never overlapping
   2. BIt stores every update as a new timestamped fact
   3. CIt converts the data to JSON before saving
   4. DIt deletes transactions that run at the same time

The task: timestamp ordering

schedule is a list of operations in the order they reach the DBMS. Each is (ts, op, record): the transaction's timestamp (a whole number from 1 to 5), "read" or "write", and the record's name. Apply timestamp ordering exactly as described above. Every record's read and write timestamps start at 0. For each operation, in order, print T<ts> <op> <record>: <result>, where result is: - skipped if transaction ts has already been aborted (do not restart it; its later operations are skipped); - abort if the rules refuse the operation (the transaction is now aborted); - ok otherwise, updating the record's timestamp. Then print committed: followed by the transactions that were never aborted, and aborted: followed by those that were, each as T<ts> in increasing order, separated by a comma and a space: for example aborted: T1, T2, T4. Fourteen lines in all. The robot does not move.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

# (transaction timestamp, operation, record)
schedule = [
    (1, "read", "balls"),
    (2, "read", "balls"),
    (2, "write", "balls"),
    (1, "write", "balls"),
    (3, "write", "score"),
    (1, "read", "score"),
    (2, "read", "score"),
    (3, "read", "balls"),
    (2, "write", "score"),
    (3, "write", "balls"),
    (5, "write", "led"),
    (4, "write", "led"),
]

Plan your program here, then type it in and press Run.

Do it on the robot
www.bugbotlab.com/learn/a11-7-client-server-and-concurrency/
The simulator checks it and tells you when it passes. Nothing to install, no account.

Challenges

1. Draw the timeline for the deadlock between A and B, with the time each lock is taken and requested.
2. Run the lost update with locks, but let B lock first. What is the final score?
3. In the task schedule, T4 is aborted only because T5 wrote led first. Does that write actually conflict with anything T4 has done? (Look up the Thomas write rule.)