Database Selection & Design (Part III)

— Internals of ACID implementation —

Byte = Block Address (Sector # and Track #) + Offset

In a hard disk, by spindle movement, the sectors are changed & by head post movement, your tracks are changed.

  • STEAL: All transactions have their own dedicated memory block. When steal happens, pages in the stolen memory block can be written out to disk, even if that page is dirty (having uncommitted operations within a transaction).
  • NO-STEAL: In this case, all dirty pages are retained in the buffer pool until the final outcome of the transaction has been determined. In case of memory full situation, the OS level flush kicks in.
  • FORCE: Make sure that every update is on disk before commit. This policy has a potential for dirty read between the write-to-disk and commit operations.
  • NO-FORCE: Allow transaction to commit to disk as per operating system rules. In this case, since the pages are not forced back to disk every time, other transactions can make use of the pages for their reads and writes.
Example #2
  • UNDO record contains the information needed to reverse a change made by a transaction (in the event of rollback). Undo logs are collections of records that capture all actions (operations performed on a tuple) to the transactions BEFORE they are committed.
  • REDO record contains the information to redo a change made by a transaction (if they have been lost). Redo logs are collection of records which captures all actions (operations performed on a tuple) to the transactions AFTER they are committed.
  • No-Force means: Pages are committed without flushing the data to the disk. No-Steal means: The pages are never stolen and dirty data are never written to the disk
  • If failure occurs, the committed transactions needs to be rolled forward (REDO) & UNDO is not required as there are no dirty pages in the disk. Hence this quadrant shows No-UNDO / REDO
  • No-Force means: Pages are committed without flushing the data to the disk. STEAL means: The pages are stolen and dirty data are written to the disk
  • If failure occurs, the committed changes need to be rolled forward (REDO) to the database & the dirty writes needs to be rolled back (UNDO). Hence this quadrant shows UNDO / REDO
  • Force means: Pages are committed and the data is flushed to the disk. No-Steal means: The pages are never stolen and dirty data are never written to the disk
  • If failure occurs, REDO is not required as every commit is flushed into the disk & UNDO is not required as there are no dirty pages in the disk. Hence this quadrant shows No-UNDO / REDO
  • Force means: Pages are committed and the data is flushed to the disk. Steal means: The pages are stolen and dirty data are written to the disk
  • If failure occurs, REDO is not required as every commit is flushed into the disk & UNDO is required as there are dirty pages in the disk. Hence this quadrant shows UNDO / No-REDO

Conclusion:

The concept of how the internals work is very critical to understand the properties of how database work on the data structure and model level. The above details are something you might not be using or discussing with your teams and architects on a regular basis, but is important to keep in mind when making your choice of data storage. So, please understand the internals of any property you review as part of the whole series.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Faisal Mohamed

Engineering Director, People Leader, Offroader, Handyman, Movie Buff, Photographer, Gardener