ORA-01591 error: “LOCK Held by in-doubt Distributed Transaction %s, caused by a lock held by a distributed transaction.” Trying to access resource that is locked by a dead two-phase commit transaction that is in preparation State ‘This error is caused by accessing a resource with a lock held by a Phase 2 transaction in the Prepared state.
The following is a brief introduction to distributed transactions.
Distributed transactions, in simple terms, means that a transaction is executed locally and remotely, and the local needs to wait to confirm the completion of the remote transaction, and then proceed to the next local operation. For example, if a row of records in the remote database is updated by dblink, if the local database cannot know the execution of the remote page game database due to network abnormity or other events during the execution, then an “IN DOUBLT” error will occur. This is where the DBA gets involved, and it needs to be handled in a variety of cases.
Distributed transactions go through three phases:
1. PREPARE PHASE:
1.1 Determine which database is a Commit Point Site. Commit_point_strength = commit_point_strength = commit_point_strength = commit_point_strength = commit_point_strength = commit_point_strength
1.2 The Global Coordinator requires all points (except the Commit Point Site) to be Commit or Rollback ready. At this point, the table of the distributed transaction is locked.
1.3 The node of all distributed transactions notifies the global coordinator of its SCN.
1.4 The global coordinator takes the maximum SCN of each point as the SCN of the distributed transaction.
At this point, all points are ready and we begin the Commit Phase, when all transactions except the Commit Point Site are in Doubt until the end of the Commit Phase.
2.COMMIT Phase: 2.1 Global Coordinator sends the maximum SCN to the Commit Point site and asks it to commit. Try Commit Point or Rollback. Distributed transaction lock release. 2.3 Commit Point notifies the Global Coordinator that it has committed. 2.4 Global Coordinator notifies all points of a distributed transaction to commit.
3. Forget Phase: 3.1 The participating point notifies the Commit Point Site that they have completed the Commit Point Site, so they can FORGET the transaction. 3.2 Commit Point Site clears distributed transaction information on a remote database. 3.3 Commit Point Site Notification of Global Coordinator can cleanse locally distributed transaction information. 3.4 Global Coordinator cleans distributed transaction information.
See the Oracle online documentation for more information about distributed transactions.
The current distributed transaction is in the Prepare Phase of the two-phase Commit mechanism. At this stage, the transaction has already placed a lock on the table. Now we want to access the table, but the transaction is not finished, and the lock is still held, so the access to the resource fails to report to ORA-01591. (It is important to point out here that locks held by distributed transactions block reads because Oralce does not know which version of data to display.) If the transaction is terminated, the corresponding lock is released, which resolves the problem. We know that there are two ways to end a transaction: COMMIT and www.pizei.comrollback. Now we try to close the transaction: from the base table of X $ktuxe, we see that the transaction does exist, and that it is prepared.
ORA-01591: When a distributed transaction dies, the lock held by the transaction will not be released because the transaction did not terminate properly. ORA-01591: When a distributed transaction dies, the lock held by the transaction will not be released. ORA-01591: When a distributed transaction dies, the lock held by the transaction will not be released properly. Since it is a distributed transaction, it is not possible to terminate the transaction by COMMIT or ROLLBACK if no transaction information is found in the DBA_2PC_PENDING file.
So, our current task is to simulate this distributed transaction. Since the DBA_2PC_PENDING attempt is dependent on the PENDING_TRANS $table and transactions are associated with sessions, we need to manually insert data into the PENDING_TRANS $and PENDING_SESSIONS $tables.