Transaction Processing, ACID, Record Locking and Redundancy

Overview

Recall questions on transactions, the ACID properties of atomicity, consistency, isolation and durability, record locking, lost updates and deadlock, redundancy for availability, and how these keep a high-volume system correct.

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 transaction in a database?

   Model answer

   A transaction is a single logical unit of work made up of one or more operations on the database, which must be treated as a whole. A transfer between two bank accounts is one transaction consisting of a debit from one account and a credit to the other; the pair only makes sense together.

2. 2

   Why must a bank transfer be treated as one transaction rather than two separate updates?

   Model answer

   Because if the system failed between the two updates, money would have left one account without arriving in the other, and it would simply have vanished from the database. Treating both operations as one transaction means either both take effect or neither does, so the total across the accounts is always correct whatever happens mid-way.

3. 3

   What does atomicity mean?

   Model answer

   Atomicity means a transaction is all or nothing: either every one of its operations completes and the transaction is committed, or, if any part fails, the whole transaction is rolled back so the database is left exactly as it was before the transaction started. A transaction can never be left half done.

4. 4

   What does consistency mean as an ACID property?

   Model answer

   Consistency means a transaction may only take the database from one valid state to another valid state. All the defined rules and constraints, such as data types, validation rules and referential integrity, must still hold after the transaction as they did before. Any transaction that would leave the database breaking one of those rules is rejected and rolled back rather than allowed to commit.

5. 5

   What does isolation mean?

   Model answer

   Isolation means that transactions running at the same time do not interfere with one another: the result must be the same as if they had been carried out one after the other. A transaction cannot see another transaction's partial, uncommitted changes, so it never makes a decision based on a half-finished update.

6. 6

   What does durability mean?

   Model answer

   Durability means that once a transaction has been committed, its changes are permanent and will survive a subsequent failure such as a power cut, a crash or the system being switched off. This is achieved by writing the committed changes to non-volatile storage, with a transaction log that lets the database management system reapply committed work when it restarts.

Other ways to work this

The same content, four other ways.

What is inside Levo

Six ways to work the same material.