Here's my understanding of what happens in PostgresSQL and MySQL:

  1. BEGIN_TXN entry is written in the WAL at the beginning of the transaction and flushed to disk
  2. COMMIT_TXN entry is written to WAL at the end of the transaction and flush to the disk
  3. Upon restart, if BEGIN_TXN is found but no corresponding COMMIT_TXN is found, that TXN needs to be rolledback
  4. Read all the WAL entries from the WAL that need to be undone. If the Undo loging is used, even they are stored eventually(indirectly) in the WAL somewhere. We will apply the undo log entry to the data pages. If undo logging is not used, we don't need to recover the data pages and we simply discard the WAL entry
  5. WAL is not used just to log transactions. Even background activities that modify the physical structure of the pages need WAL logging. They could be modeled as system transactions and TXN_ID given to them as well.

Is this roughly correct?

  • 1
    "Is this roughly correct?" No.
    – mustaccio
    Feb 7 at 0:40
  • Can you please explain the right answer then? @mustaccio
    – user855
    Feb 7 at 0:55
  • 2
    Crash recovery is implemented differently in different DBMS storage engines, so there isn't "the" right answer, only answers specific to Postgres and each MySQL storage engine that supports crash recovery (not all do). It's all described, to different levels of detail, in the respective documentation. Please ask a more specific question if that documentation doesn't satisfy your curiosity.
    – mustaccio
    Feb 7 at 1:27
  • The main problem with this question is that MySQL and PostgreSQL handle that quite differently. For a meaningful answer, you have to be more precise. Feb 7 at 16:31


Browse other questions tagged or ask your own question.