CODEWITHSUNDEEP
mysqldatabasetransactionsacidwal
user855
user855

Reputation: 19918

What is the need for undo log if redo(WAL) exists?

If there is WAL (redo) log available, then it would allow us to persist to disk the records and commit the transaction without needing to update the data structures first.

Since you can always re-apply from the redo log and update the data structures on crashes or failures, why do we still have a need to undo the log.

My understanding for this question is that we still need undo log because:

  1. If we update the data structures with the records before they are committed and persisted to disk, then we may need to undo the changes in the data structures on transaction rollback. However, I don't understand why cannot we just wait for the transaction to commit before updating the data structure? That sounds simpler than maintaining an undo log.
  2. Snapshot isolation. MVCC requires data structure to be able to access older versions os records. With an undo log, it can go back in time. Without an undo record it cannot provide backtracking features.

Is my understanding correct about why we need undo logging even if redo log exists?

Upvotes: 4

Views: 2070

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

The assumption is that commits happen more often than rollbacks. I.e. a client would not have executed an update if they didn't intend that it should be committed. Rollbacks are the exception.

When you execute a change, the change is written to the data pages, and the old version of the page is written to the undo log. The change is also written to the redo log for crash recovery. On commit, then the only thing that needs to happen is that the new version is now considered committed. The copy in the undo log may be scheduled to be discarded as garbage, at least once other concurrent transactions no longer need it for their MVCC snapshot. In fact, there may be multiple versions of a given row in the undo log, to satisfy different snapshots.

If there were no undo log, and everything relied on the redo log, then the consequences would be:

  • Executing changes would write only to the redo log. This is a slight speed-up during query execution.
  • But commit would be forced to do a lot more work. Basically, equivalent work to that which is done during crash recovery, reconciling the redo log entries against existing pages, and reconstructing the "current" committed version there.
  • Redo log reconciliation couldn't happen until all other concurrent repeatable-read transactions had completed, because they still need to view the old version of data in the data pages. This would be a problem given that the redo log has a fixed size; an unfinished transaction could cause the redo log to fill up, and then further writes could be blocked (this can happen in theory with the undo log too, but it takes a lot longer).
  • Clients who start new transactions would all be forced to do dynamic reconciliation of the redo log against the old data page for every read, at least until the redo log is finally merged. This is a lot of busy-work for them to do, and kind of a shame because it would be more common in an OLTP database for new transactions to start.

Given all this, it seems like using an undo log helps optimize for the most common case: new transactions reading fresh snapshots of the most recently-committed data, as pages directly from the buffer pool.

Upvotes: 2

Related Questions