English
Menu

Database Basics

When updating data, many DBMSs record the changes in a log (journal) and write that log to nonvolatile storage before the database itself (WAL: write-ahead logging). What is the main reason for this approach?

  1. AEven if a failure occurs, the log can be used to redo committed updates and undo uncommitted ones, restoring a correct state
  2. BWriting the log first makes the database itself smaller
  3. CThe log can be used as an index for searches by SELECT statements
Show answer and explanation

Answer: A. Even if a failure occurs, the log can be used to redo committed updates and undo uncommitted ones, restoring a correct state

For performance, database pages are updated in buffers in memory and written to storage some time later. So at the moment of a failure, storage may contain a mix of committed updates that have not yet been written and uncommitted updates that have. If the before and after values of each update are recorded in a log and the log is written before the database itself, then after a failure the log can be used to redo committed updates (rollforward, REDO) and undo uncommitted ones (rollback, UNDO), restoring a correct state. A commit can be considered complete as soon as the transaction's commit record is written to the log, so database pages need not be written immediately every time, which preserves performance. This mechanism underpins the atomicity and durability of the ACID properties.

  • A. Correct. Writing the log first makes it possible to restore a correct state after a failure with REDO and UNDO.
  • B. The log is recorded separately from the database and does not make the database smaller.
  • C. The log is a record for recovering from failures and is not used as an index for searches.
Practice this topic Back to topic

Related calculators

SQL PracticeRun SQL in your browser and check the results, with 10 practice exercises that grade your answers

Books on Database Basics Ad

As an Amazon Associate I earn from qualifying purchases.

More Database Basics questions

  1. There are transactions T1 to T5, and one checkpoint was taken before a failure occurred. T1 started and committed before the checkpoint. T2 and T3 started before the checkpoint; T2 committed after the checkpoint but before the failure, and T3 had not committed at the time of the failure. T4 and T5 started after the checkpoint; T4 committed before the failure, and T5 had not committed. In recovery after the failure, which transactions must be rolled forward using the log?
  2. A B+ tree database index was created on the member_no column of a member table with 1 million rows. Which correctly pairs the effect that can generally be expected with its cost?
  3. For a member table with 1 million rows, consider creating a B+ tree index on each of the columns member_no (all different), prefecture (47 values) and gender (2 values, split roughly in half). When searching by specifying a value for one column in the WHERE clause, on which column is an index likely to be least effective?
  4. Among the databases called NoSQL, which is generally a correct description of the characteristics of a key-value store?
  5. Which is an appropriate description of the CAP theorem for a distributed database that keeps copies of data on multiple servers?