Flat Transaction
Flat transactions are the simplest of the transaction types, but they are probably the most frequently used in a real production environment.
In a flat transaction, all operations are at the same level, starting with BEGIN WORK and ending with COMMIT WORK or ROLLBACK WORK, where the operations are atomic and either executed or rolled back.
Thus flat transaction applications become the basic building blocks of atomic operations.
The main limitation of flat transactions is that you cannot commit or roll back a portion of the transaction, or commit in several steps.
Flat Transactions with Savepoint Flat Transactions with Savepoint
In addition to operations that support flat transaction support, rollback to an earlier state in the same transaction is allowed during transaction execution. This is because some transaction may go wrong during execution without invalidating all operations, and it is not desirable or expensive to abandon the entire transaction. Savepoints are used to inform the system that it should remember the current state of the transaction so that the transaction can return to that state if an error occurs later.
For flat transactions, a savepoint is implicitly set. However, there is only one savepoint in the entire transaction, so rollback can only be rolled back to the state at the beginning of the transaction. A savepoint is established using the SAVE WORK function to inform the system to record the current processing status. When something goes wrong, savepoints can be used as an internal restart point, depending on the application logic, to decide whether to go back to the most recent savepoint or some other earlier savepoint.
The diagram below:
Chained Transaction
Can be regarded as a variation of savepoint pattern.
Flat transactions with savepoints, where all savepoints disappear in the event of a crash, are volatile rather than persistent. This means that when a restore occurs, the transaction needs to be re-executed from the beginning, rather than continuing from the most recent savepoint.
The idea of chained transactions is that when a transaction commits, unneeded data objects are released, implicitly passing the necessary processing context to the next transaction to be started.
Note that the commit transaction and the start of the next transaction are combined into one atomic operation. This means that the next transaction will see the result of the previous transaction as if it were in one transaction.
As shown in figure
Chained transactions differ from flat transactions with savepoints:
1) Flat transactions with savepoints can be rolled back to any correct savepoint. The rollback in chained transactions is limited to the current transaction, that is, only to the most recent savepoint.
2) Chained transactions release locks held by the current transaction after a COMMIT, while flat transactions with savepoints do not affect all locks so far.
Nested Transaction
Is a hierarchical architecture. A single top-level transaction controls all levels of transactions. The transactions nested beneath the top-level transaction are called subtransactions, which control the transformation of each local.
As shown in figure
Moss’s definition of nested transactions:
1) A nested transaction is a tree composed of several transactions. The subtree can be either a nested transaction or a flat transaction.
2) Transactions at leaf nodes are flat transactions. But the distance from the root to the leaf node can be different for each subtransaction.
3) Transactions at the root node become top-level transactions and other transactions become sub-transactions. The predecessor of transactions is the parent transaction, and the next layer of transactions becomes the child transaction.
4) A subtransaction can be committed or rolled back, but its commit operation does not take effect immediately unless its parent has committed. Therefore, any subtransactions are committed only after the top-level transaction is committed.
5) The rollback of any transaction in the tree will cause all other sub-transactions to be rolled back together. Therefore, sub-transactions only retain the features of A, C and I, but do not have the features of D.
In Moss theory, the actual work is done by the leaf node, that is, only the transactions of the leaf node can access the database, send messages, and obtain other types of resources. The higher-level transactions are only responsible for logical control, deciding when to invoke the related subtransactions. Even if a system does not support nested transactions, nested transactions can be simulated using savepoint techniques. As shown in figure
Using savepoint techniques to simulate nested transactions makes a difference in holding locks for nested transactions
When using savepoint technology, the user cannot choose which locks need to be inherited by a quilt transaction and which locks need to be retained by the parent transaction. That is, no matter how many savepoints there are, all locked objects can be retrieved and accessed.
For example, a parent transaction P1, which holds exclusive locks on objects X and Y, now wants to initiate a call to child transaction P11. The parent transaction P1 may pass no locks, all locks, or only one exclusive lock. When the child transaction p11 has an exclusive lock on object Z, the parent transaction P1 has an exclusive lock on object X, Y, and Z through counter-inheritance.
Distributed Transactions
Typically a flat transaction running in a distributed environment, requiring access to different nodes in the network depending on where the data is located.
InnoDB storage engine supports flat transactions, transactions with savepoints, chained transactions, and distributed transactions, but does not support nested transactions, although sequential nested transactions can be simulated by transactions with savepoints.
Resources: Inside Mysql Technology