In the field of database

Database

A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...

s in computer science

Computer science

Computer science or computing science is the study of the theoretical foundations of information and computation and of practical techniques for their implementation and application in computer systems...

, a transaction log (also database log or binary log) is a history of actions executed by a database management system

Database management system

A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...

 to guarantee ACID

ACID

In computer science, ACID is a set of properties that guarantee database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction...

 properties over crash

Crash (computing)

A crash in computing is a condition where a computer or a program, either an application or part of the operating system, ceases to function properly, often exiting after encountering errors. Often the offending program may appear to freeze or hang until a crash reporting service documents...

es or hardware failures. Physically, a log is a file

Computer file

A computer file is a block of arbitrary information, or resource for storing information, which is available to a computer program and is usually based on some kind of durable storage. A file is durable in the sense that it remains available for programs to use after the current program has finished...

 of updates done to the database, stored in stable storage.

If, after a start, the database is found in an inconsistent state or not been shut down properly, the database management system reviews the database logs for uncommitted

Commit (data management)

In the context of computer science and data management, commit refers to the idea of making a set of tentative changes permanent. A popular usage is at the end of a transaction. A commit is an act of committing.-Data management:...

 transactions and rolls back

Rollback (data management)

In database technologies, a rollback is an operation which returns the database to some previous state. Rollbacks are important for database integrity, because they mean that the database can be restored to a clean copy even after erroneous operations are performed...

 the changes made by these transaction

Database transaction

A transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...

s. Additionally, all transactions that are already committed but whose changes were not yet materialized in the database are re-applied. Both are done to ensure atomicity

Atomicity

In database systems, atomicity is one of the ACID transaction properties. In an atomic transaction, a series of database operations either all occur, or nothing occurs...

 and durability

Durability (computer science)

In database systems, durability is the ACID property which guarantees that transactions that have committed will survive permanently.For example, if a flight booking reports that a seat has successfully been booked, then the seat will remain booked even if the system crashes.Durability can be...

 of transactions.

This term is not to be confused with other, human-readable logs that a database management system usually provides.

Anatomy of a general database log

A database log record is made up of

• Log Sequence Number: A unique id for a log record. With LSNs, logs can be recovered in constant time. Most logs' LSNs are assigned in monotonically increasing order, which is useful in recovery algorithm
  Algorithm
  In mathematics and computer science, an algorithm is an effective method expressed as a finite list of well-defined instructions for calculating a function. Algorithms are used for calculation, data processing, and automated reasoning...
  
  s, like ARIES
  Algorithms for Recovery and Isolation Exploiting Semantics
  In computer science, Algorithms for Recovery and Isolation Exploiting Semantics, or ARIES is a recovery algorithm designed to work with a no-force, steal database approach; it is used by IBM DB2, Microsoft SQL Server and many other database systems....
  
  .
• Prev LSN: A link to the last log record. This implies database logs are constructed in linked list
  Linked list
  In computer science, a linked list is a data structure consisting of a group of nodes which together represent a sequence. Under the simplest form, each node is composed of a datum and a reference to the next node in the sequence; more complex variants add additional links...
  
   form.
• Transaction ID number: A reference to the database transaction generating the log record.
• Type: Describes the type of database log record.
• information about the actual changes that triggered the log record to be written

Types of database log records

All log records include the general log attributes above, and also other attributes depending on their type (which is recorded in the Type attribute, as above).

• Update Log Record notes an update (change) to the database. It includes this extra information:
  • PageID: A reference to the Page ID of the modified page.
  • Length and Offset: Length in bytes and offset of the page are usually included.
  • Before and After Images: Includes the value of the bytes of page before and after the page change. Some databases may have logs which include one or both images.

• Compensation Log Record notes the rollback of a particular change to the database. Each correspond with exactly one other Update Log Record (although the corresponding update log record is not typically stored in the Compensation Log Record). It includes this extra information:
  • undoNextLSN: This field contains the LSN of the next log record that is to be undone for transaction that wrote the last Update Log.

• Commit Record notes a decision to commit a transaction.

• Abort Record notes a decision to abort and hence rollback a transaction.

• Checkpoint Record notes that a checkpoint has been made. These are used to speed up recovery. They record information that eliminates the need to read a long way into the log's past. This varies according to checkpoint algorithm. If all dirty pages are flushed while creating the checkpoint (as in PostgreSQL
  PostgreSQL
  PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
  
  ), it might contain:
  • redoLSN: This is a reference to the first log record that corresponds to a dirty page. i.e. the first update that wasn't flushed at checkpoint time. This is where redo must begin on recovery.
  • undoLSN: This is a reference to the oldest log record of the oldest in-progress transaction. This is the oldest log record needed to undo all in-progress transactions.

• Completion Record notes that all work has been done for this particular transaction. (It has been fully committed or aborted)

Tables

These tables are maintained in memory, and can be efficiently reconstructed (if not exactly, to an equivalent state) from the log and the database:

• Transaction Table: The table contains one entry for each active transaction. This includes Transaction ID and lastLSN, where lastLSN describes the LSN of the most recent log record for the transaction.

• Dirty Page Table: The table contains one entry for each dirty page that hasn't been written to disk. The entry contains recLSN, where recLSN is the LSN of the first log record that caused the page to be dirty.