I know the basics of why a database uses a transaction log - fulfilling ACID properties, ability to rollback/restore, etc.
The basic algorithm that I see for a transaction is as follows:
- transaction changes data - data pages are changed in memory
- transaction log is written to disk (must happen before commit is recorded)
- commit is issued
- data pages are written to disk asynchronously
In theory, this means that you save I/O operations because you don't need to wait for data pages to be written to disk before a commit. You only need to wait for the log to finish being written because the transaction can be redone from the log if a crash occurs before the data pages are written. However, if the transaction log needs to store before/after values or at least all the new data that changed in the data pages anyway, how does this save I/O? Would it not take the same amount of time to not store the data changes in the log and just wait for data pages to be written synchronously?
For example, if my transaction makes 10 MB of changes to data, wouldn't it take the same amount of time to write 10MB changes to the log as it would to just wait and write those 10MB of data pages to disk? What's the point of also storing the changed data in the log?