Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.4Mb
PDF (A4) - 31.5Mb
PDF (RPM) - 29.8Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.5Mb
Man Pages (TGZ) - 177.2Kb
Man Pages (Zip) - 287.6Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

14.6.7 Undo Logs

An undo log is a collection of undo log records associated with a single read-write transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data as part of a consistent read operation, the unmodified data is retrieved from undo log records. Undo logs exist within undo log segments, which are contained within rollback segments. By default, rollback segments are physically part of the system tablespace, but they can also reside in undo tablespaces. For more information, see Section 14.6.3.3, “Undo Tablespaces”.

InnoDB supports 128 rollback segments. The innodb_rollback_segments variable defines the number of rollback segments used by InnoDB.

The number of transactions that a rollback segment supports depends on the number of undo slots in the rollback segment and the number of undo logs required by each transaction.

The number of undo slots in a rollback segment differs according to InnoDB page size.

InnoDB Page Size Number of Undo Slots in a Rollback Segment (InnoDB Page Size / 16)
4096 (4KB) 256
8192 (8KB) 512
16384 (16KB) 1024

A transaction is assigned up to two undo logs, one for each of the following operation types:

  1. INSERT operations

  2. UPDATE and DELETE operations

Undo logs are assigned as needed. For example, a transaction that performs INSERT, UPDATE, and DELETE operations is assigned two undo logs. A transaction that performs only INSERT operations is assigned a single undo log. Undo logs are assigned to a transaction from a rollback segment that is also assigned to the transaction.

An undo log assigned to a transaction remains tied to the transaction for its duration. For example, an undo log assigned to a transaction for an INSERT operation is used for all INSERT operations performed by that transaction.

Given the factors described above, the following formulas can be used to estimate the number of concurrent read-write transactions that InnoDB is capable of supporting.

Note

A transaction can encounter a concurrent transaction limit error before reaching the number of concurrent read-write transactions that InnoDB is capable of supporting. This occurs when the rollback segment assigned to a transaction runs out of undo slots. In such cases, try rerunning the transaction.

  • If each transaction performs either an INSERT or an UPDATE or DELETE operation, the number of concurrent read-write transactions that InnoDB is capable of supporting is:

    (innodb_page_size / 16) * innodb_rollback_segments
  • If each transaction performs an INSERT and an UPDATE or DELETE operation, the number of concurrent read-write transactions that InnoDB is capable of supporting is:

    (innodb_page_size / 16 / 2) * innodb_rollback_segments

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.