Transactions are one of the major differences between a database system and a file system in that they guarantee that any changes to the data will either succeed or be rolled back.

Transactions in InnoDB are fully ACID compliant.

To know the transaction

An introduction to

A transaction consists of one or more SQL statements, the smallest unit of execution of a database program.

The definition of ACID is omitted here.

classification

Transactions can be divided into the following five categories:

  • Flat transaction
  • Flat transactions with savepoints
  • Chain business
  • The nested transaction
  • Distributed transaction

Flat transactions are the simplest type of transaction where all operations are at the same level. Flat transactions start with Work, end with Commit/Roolback, and either all succeed or all roll back.

The main limitation of flat transactions is the inability to commit or roll back portions of the transaction. Hence the introduction of flat transactions with savepoints.

A flat transaction with savepoints is, as the name implies, a flat transaction with savepoints that can be used as a rollback/commit location. When you roll back a transaction, you can roll back to a savepoint so you don’t have to roll back all of them. Savepoints are incremented within a transaction.

A flat transaction is equivalent to a flat transaction with a savepoint set at the start.

Chain transaction exists to solve the savepoint vulnerability. Chain transaction is a chain transaction formed by multiple transactions. Chain transactions implicitly and atomically start the next node transaction after the previous node transaction commits.

With a caveat, chain transactions can only be rolled back to the nearest node transaction; Chain transactions release locks held by the current transaction after the current node transaction commits, whereas savepoint transactions can remain until the commit is complete.

Nested transactions are performed by a branch of the top-level transaction control logic, the organization form of it like a tree, leaf node is a flat transaction, perform the actual operation data, the root node is called the top-level transaction and control child nodes, leaf nodes of child nodes represent only the operating logic, not for data operation, at the same time also can control the child nodes of lock held resources.

Nested transactions can also be simulated through savepoints.

Distributed transactions are used to run flat transactions in a distributed environment. Distributed transactions also need to meet ACID properties. If a flat transaction in the entire environment fails, all transactions need to be rolled back. In addition, to ensure consistency, distributed transactions often use serial operations.

Transaction implementation

Transaction isolation is achieved through locks, and atomicity, consistency, and persistence are achieved through redo logs and undo logs.

Redo restores page operations modified by transaction commits, and Undo rolls back row records to a specific version. Redo is a physical log, undo is a logical log.