Transactions 4
MySQL
The undo log is stored in the rollback segment
a
of the system tablespace.
Each undo log is split into two sections, one responsible for rolling back purposes and the
other for reconstructing the before image. The first section can be wiped out right after the
transaction is ended, while the other needs to linger for as long as any currently running query
or other concurrent transactions need to see a previous version of the records in question.
Behind the scenes, MySQL runs a purge process that cleans up the storage occupied by
deleted records, and it also reclaims the undo log segments that are no longer required.
Long-running transactions delay the purge process execution, causing the undo log
to grow very large, especially in write-heavy data access scenarios.
a
https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html
1.2 Consistency
A modifying transaction can be seen as a state transformation, moving the database from one
valid state to another. The relational database schema ensures that all primary modifications
(insert/update/delete statements), as well as secondary ones (issued by triggers), obey
certain rules on the underlying data structures:
• column types
• column length
• column nullability
• foreign key constraints
• unique key constraints
• custom check constraints.
Consistency is about validating the transaction state change so that all committed trans-
actions leave the database in a proper state. If only one constraint gets violated, the entire
transaction will be rolled back, and all modifications are going to be reverted.
Although the application must validate user input prior to crafting database statements, the
application-level checks cannot span over other concurrent requests, possibly coming from
different web servers. When the database is the primary integration point, the advantages of
a strict schema become even more apparent.