[Oracle] distributed transactions and two-phase commit (2 PC)

Distributed transactions are transactions that occur between multiple databases. Oracle uses the dblink Method for transaction processing. distributed transactions are much more complex than standalone transactions. Most customs

Distributed transactions are transactions that occur between multiple databases. Oracle uses the dblink Method for transaction processing. distributed transactions are much more complex than standalone transactions. Most customs

Distributed transactions are transactions that occur between multiple databases. Oracle uses the dblink Method for transaction processing. distributed transactions are much more complex than standalone transactions. Most relational databases use the two-Phase Commit (2 Phase Commit 2 PC) algorithm to complete distributed transactions. The following describes the two PC algorithm.

1. Composition of distributed transactions

Distributed transactions consist of the following parts:

In distributed transactions, the Commit Point Site is very important. It does not need to enter the 2 PC Prepared State, because it usually operates on the most critical data, so it does not appear in-doubt state. The Commit Point Site always takes precedence over the Commit of other database nodes to protect the most critical data. It determines whether the entire distributed transaction is committed or rolled back. In distributed transactions, other database nodes perform subsequent commit (or rollback) under the command of GC ).

In Oracle, how does one select a Commit Point Site? It serves as the Commit Point Site based on the database with the largest commi _ point_strength parameter.

2. Two-Phase submission (2 PC)

The two-phase commit protocol ensures strong data consistency. Many Distributed Relational Data Management systems use this Protocol to complete distributed transactions. It is a distributed algorithm that coordinates all distributed atomic Transaction participants and decides to commit or cancel (rollback. It is also an algorithm for solving consistency problems. This algorithm can solve many temporary system faults (including processes, network nodes, communications, and other faults) and is widely used. However, it cannot solve all faults through configuration. In some cases, it still requires human intervention to solve the problem.

As the name implies, two-phase commit is divided into the following two phases:

1) Prepare Phase (preparation node)

2) Commit Phase (submission Phase)

1) Prepare Phase

In the request phase, the Coordinator notifies the transaction participant to prepare to submit or cancel the transaction and then enters the voting process. During the voting, the participants will inform the coordinator of their decision: consent (successful execution of the local job of the transaction participant) or cancellation (local job execution failure ).

To complete the quasi-preparation phase, other database nodes except the commit point site follow these steps:

2) Commit Phase

At this stage, the Coordinator will make a decision based on the voting results of the first stage: Submit or cancel. If and only when all participants agree to submit the Transaction Coordinator notifies all participants to submit the transaction, otherwise the Coordinator will notify all participants to cancel the transaction. After receiving a message from the Coordinator, the participant performs a response operation.

In the submission phase, follow these steps:

3) End Stage

Generally, the two-phase commit mechanism works well. When a participant goes down during a transaction and restarts, you can ask other participants or coordinators, to know whether the transaction has been committed. Of course, the premise is that each participant writes logs in advance during each step of operation.

The only dilemma that cannot be solved by a two-phase commit is that when the Coordinator sends a commit message, the only participant who receives the command goes down, at this time, the transaction is in an unknown state. No one knows whether the transaction has been committed or not. This requires the intervention of the database administrator to prevent the database from entering an inconsistent state. Of course, if one premise is that all node or network exceptions will eventually be restored, the problem will not exist, and the coordinators and participants will eventually restart, other nodes will eventually receive the commit information. This is also in line with the CAP theory.