Versioning

• Because InnoDB is a multi-versioned storage engine, it must keep information about old versions of rows in the tablespace.
  • This information is stored in a data structure called a rollback segment.
  • InnoDB uses the information in the rollback segment to perform the undo operations needed in a transaction rollback.
  • It also uses the information to build earlier versions of a row for a consistent read.

• Internally, InnoDB adds two fields to each row stored in the database.
  • The transaction identifier.
  • The roll pointer.
    • The roll pointer points to an undo log record written to the rollback segment.
    • If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.

• InnoDB uses the information in the rollback segment to perform the undo operations needed in a transaction rollback.
  • It also uses the information to build earlier versions of a row for a consistent read.

• Undo logs in the rollback segment are divided into insert and update undo logs.
  • Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits.
  • Update undo logs are used also in consistent reads.

• In the InnoDB multi-versioning scheme, a row is not physically removed from the database immediately when you delete it with an SQL statement.
  • Only when InnoDB can discard the update undo log record written for the deletion can it also physically remove the corresponding row and its index records from the database.
  • This removal operation is called a purge, and it is quite fast.

What is the InnoDB table and index structure?

---

---

© 2007 John Michael Pierobon

Notes