4

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?

Improve this question
0
5

The log is an append-only data structure, which is much easier to ensure the it is either in its old state, or its new state.

Whereas the transaction data pages are all over the place.

So there are two differences:

(1) the 10MB written to the log could possibly happen in one write, whereas the 10MB written to the data pages may take numerous I/Os to complete.

(2) With the append-only log structure it is easier to assure is all of the transaction is committed into the log or nothing is (log remains unmodified), whereas with the numerous I/Os for the data pages, an all or nothing guarantee is harder (and why the log is used).

Improve this answer
3
  • Gotcha. So it is faster to use the log, but larger transactions will take longer to write than smaller transactions because the log needs to be written to disk. – bobroxsox Feb 1 '16 at 22:54
  • 1
    Yes, it adds overhead, and might be considered as the cost of (a higher level of) durability or resilience to various kinds of failures and outages. However as you noted, the a successful commit can be reported to the client as soon as the log part is completed. (Also, some like to replicate across data centers or regions for even more durability, so there is always more durability to be had!) – Erik Eidt Feb 1 '16 at 23:02
  • Also note that the transaction log is usually smaller than the entire database, so can realistically be stored on more expensive faster storage, eg an SSD, while the bulk of the data remains on slower disk storage. – Jules Feb 2 '16 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.