Lecture 12. Journaling DB Changes

One of the main requirements for an advanced database management system is the reliability of database storage. This requirement implies, in particular, the possibility of restoring the consistent state of the database after any kind of hardware and software failures. Obviously, some additional information is needed to complete the restorations. In the vast majority of modern relational DBMS, such redundant additional information is maintained in the form of a database change log.

So, the overall goal of logging database changes is to ensure that the consistent state of the database can be restored after any failure. Since the basis of maintaining a complete database state is the transaction mechanism, logging and recovery are closely related to the concept of a transaction. The general principles of recovery are as follows:

• the results of committed transactions must be saved in the restored state of the database;
• uncommitted transaction results must not be in the restored database state.

This, in fact, means that the most recent consistent state of the database is being restored.

The following situations are possible in which you want to restore the state of the database:

• Individual transaction rollback. The trivial situation of rolling back a transaction is its explicit completion by the ROLLBACK statement. There are also situations where the rollback of a transaction is initiated by the system. Examples include the occurrence of an exception in an application program (for example, dividing by zero) or selecting a transaction as a victim when a synchronization deadlock is detected. To restore a consistent state of the database when an individual transaction is rolled back, you need to eliminate the consequences of database modification statements that were executed in this transaction.
• Recover from a sudden loss of memory contents (mild failure). Such a situation may occur when an emergency power supply is turned off, in the event of a fatal processor malfunction (for example, a RAM control trigger), etc. The situation is characterized by the loss of that part of the database, which at the time of the failure was contained in memory buffers.
• Recovery after breakdown of the main external database carrier (hard failure). This situation with relatively high reliability of modern external memory devices may occur relatively rarely, but nevertheless, the DBMS should be able to restore the database even in this case. The basis for recovery is an archive copy and a database change log.

In all three cases, the basis of recovery is redundant data storage. This redundant data is stored in a log containing a sequence of database change records.

There are two main options for maintaining journal information. In the first variant, for each transaction a separate local change log of the database is maintained by this transaction. These local logs are used for individual rollbacks of transactions and can be maintained in operational (or, more correctly, in virtual) memory. In addition, a general database change log is maintained, used to restore the state of the database after soft and hard failures.

This approach allows you to quickly perform individual transaction rollbacks, but leads to duplication of information in local and general journals. Therefore, the second option is more often used - maintaining only the general change log of the database, which is also used when performing individual rollbacks. Next, we consider this option.