This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!

This article is mainly based on MySQL Technical Insider: InnoDB Storage Engine, Wikipedia, related official documents and other content.

Transaction class

Formally, transactions fall into the following categories.

  • Flat transactions (Flat Transactions)
  • Flat transactions with savepoints (Flat Transactions with Savepoints)
  • Chained transactions (Chained Transactions)
  • Nested transactions (Nested Transactions)
  • Distributed transactions (Distributed Transactions)

There are, of course, Multilevel Transactions.

This classification of transactions can also be described as models of transactions, that is, transactions of different models.

Flat transaction

Flat transactions are the most common type. All operations in a flat Transaction are at the same level, starting with Start Transaction/BEGIN WORK and ending with COMMIT [WORK] or ROLLBACK [WORK]. All operations in a flat Transaction are atomic and either all or none of them are executed.

There are three different outcomes of flat transactions: COMMIT, ROLLBACK, and wait timeout. The main limitation of flat transactions is that they cannot commit or roll back a portion, or commit in several steps.

Flat transactions with savepoints

In addition to supporting flat transactions, it is also possible to roll back to an earlier state in the rollback of the transaction.

Savepoints are used by the system to record the state of the current transaction so that in the event of an error, the transaction can be rolled back to the state at the Savepoint. SAVEPOINT savepoint_name; Or the save work command to set savepoints.

Flat transactions by default have a savepoint 1, where the transaction begins.

Chain business

When a transaction is committed, unwanted data objects are freed, implicitly passing the necessary processing context to the next transaction to start. Note: The commit transaction operation and the next transaction start operation are merged into one atomic operation, which means that the next transaction will see the results of the previous transaction.

Chain transactions work as shown in the figure below:

A chain transaction can only roll back the current transaction, that is, restore to the most recent transaction start point. A chain transaction releases locks held by the current transaction after committing. A flat transaction with a savepoint does not affect the locks held so far when the savepoint is created.

Chain-linked transactions can be seen as a variant of the savepoint pattern, flat transactions with savepoints, all of which disappear in the event of a system crash or failure because savepoints are volatile, not persistent. This means that when redo logging is required for recovery, the transaction needs to be reexecuted from the start and cannot be continued from the nearest reservation point.

Chain transactions are executed sequentially and in a specific order.

Nested transactions

Nested transactions are a hierarchical framework in which a top-level transaction controls transactions at each level. A transaction nested under a top-level transaction is called a subtransaction.

The nested transaction hierarchy is shown in the following figure:

  • A nested transaction is a tree composed of several transactions, and the subtree can be either nested or flat transactions
  • A transaction in a leaf node is a flat transaction.
  • The transaction at the root node is the top-level transaction, and others are called sub-transactions. A predecessor of a transaction is a parent transaction, and the next layer of a transaction is called a child transaction.
  • Subtransactions can be committed or rolled back. However, its commit does not take effect immediately unless the parent transaction commits. Therefore, any subtransaction is committed only after the top-level transaction commits.
  • The rollback of any transaction in the tree will cause all its sub-transactions to be rolled back together. Therefore, sub-transactions only retain features A, C, and I, but do not have features D.

No unrelated transactions will see the changes until the outermost transaction commits. This means that a commit in an internal transaction does not necessarily persist updates to the system.

Even if an RDBMS does not support nested transactions, it is possible to simulate nested transactions through retention points. However, when nested transactions are simulated through retention points, the user cannot choose which locks need to be inherited by the child transaction and which locks need to be retained by the parent transaction. In nested transactions, different subtransactions hold different locks on database objects.

MySQL’s InnoDB does not support nested transactions, and PostgreSQL does not support nested transactions.

Nested transactions provide the capability for a true component-based application architecture. Component functions may or may not contain database transactions, and nested transactions occur if a component function containing a transaction is called within begin-commit parentheses. But because popular databases such as MySQL do not support nested transactions, a framework or transaction monitor is needed to handle this issue.

(No need to pay attention to this part)

  • Nesting: The subtransaction commit finally depends on whether the top level transaction committed or not. The SQL Server database system is this form of nesting.
  • Nesting: A child transaction can commit independently of its parent transaction. That is, if the parent transaction is rolled back, the result of the successful child transaction is not rolled back. The commit or rollback of the parent transaction does not affect the commit of the child transaction.

Distributed transaction

A distributed transaction is typically a flat transaction running in a distributed environment. You need to access different nodes on the network based on the data location.

(For example, inter-bank transfers, which involve distributed transactions), distributed transactions also need to satisfy the ACID properties.

InnoDB storage engine provides XA transactions and thus supports the implementation of distributed transactions.

Distributed transactions are those that allow multiple independent transactional resources (such as RDBMS) to participate in a single global transaction. This involves multiple independent resource managers, usually a database, and a transaction Manager that coordinates and manages global transactions across multiple resources, especially across server resources. A global transaction requires that all transactions in it either commit or roll back.

  • Resource manager: Used to manage system resources and is the gateway to transactional resources. A database is a resource manager. Resource management should also have the ability to manage transaction commit or rollback.
  • Transaction Manager: The transaction manager is the core manager of distributed transactions. The transaction manager communicates with each resource manager to coordinate and complete the processing of transactions. Each branch of a transaction is identified by a unique name.

XA protocol was first proposed by Tuxedo, and handed to the X/Open organization of distributed transaction specifications or protocol standards

Distributed transactions are described in detail later.

MySQL’s InnoDB natively does not support nested transactions, but supports flat transactions, transactions with reservation points, chain transactions, and distributed transactions. PostgreSQL does not support nested transactions. SQL Server supports flat transactions, transactions with reservation points, distributed and nested transactions, but does not support chained transactions.

About Multi-level Transactions

Multilevel transactions are a type of nested transactions, or a variant of nested transactions. Most database documents or SQL concepts do not list it as a specific technical term or specifically implement it in a database system.

Multilevel transactions are a variant of nested transactions where subtransactions occur at different levels of the layered system architecture (for example, one operation at the database engine level and one at the operating system level)

Multi-level transactions are a variant of nested transactions where the sub-transactions take place at different levels of a layered system architecture (e.g., with one operation at the database-engine level, one operation at the operating-system level)

(en.wikipedia.org/wiki/Databa…).

Specific can see research.microsoft.com/en-us/um/pe… In the introduction.

About MySQLcommit work,commit work and chain

MySQL/MariaDB can use the alias begin Work for start Transaction to start a transaction.

In addition to COMMIT and rollback, you can also use COMMIT work and rollback work. By default, adding work or not is equivalent.

commit work and chain; Used to start a chained transaction, that is, commit and start a new transaction at the same time.

Use the START TRANSACTION; /begin Explicitly starts a transaction. If commit is performed, the transaction is committed. If you run commit work and chain; The TRANSACTION is committed and the next TRANSACTION is automatically started, eliminating the need to START TRANSACTION again. Or begin work, the number of statement interactions is reduced, and the statement is clearly identified as being in a transaction.

  • commitandcommit workDepends oncompletion_typeParameter Settings:
    1. completion_type=0: This is completely equivalent (also the default) and commits the current transaction.
    2. completion_type=1:commit workIs equivalent tocommit and chainTo commit the current transaction and immediately start a transaction with the same isolation level.
    3. completion_type=2:commit workIs equivalent tocommit and release, indicating that the server is automatically disconnected after the transaction is committed.

Rollback and rollback work are performed the same as commit and COMMIT work.

  • To viewcompletion_typeParameter Settings:
MariaDB [(none)]> show variables like 'completion_type';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| completion_type | NO_CHAIN |
+-----------------+----------+
1 row in set (0.025 sec)
Copy the code
  • Set up thecompletion_typeParameter 1:
MariaDB [(none)]> set @@completion_type=1;
Query OK, 0 rows affected (0.008 sec)

MariaDB [(none)]> show variables like 'completion_type';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| completion_type | CHAIN |
+-----------------+-------+
1 row in set (0.001 sec)
Copy the code