Shulou(Shulou.com)05/31 Report--

This article will explain in detail the solutions to ORA-01591 errors in Oracle database distributed transactions. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Recently, when I was in a table in update, I suddenly prompted a relatively rare error, ORA-01591. This problem is a little different from the usual lock. Let's take a look at it.

Train of thought

This error is caused by distributed transactions rather than ordinary locks. Check general object data table locks and only check v$locked_object and v$transaction views to locate specific SQL statements and operators.

Select * from gv$locked_object; select * from gv$transaction

Use the oerr tool to view the error number

Oerr ora 1591 01591, 00000, "lock held by in-doubt distributed transaction% s" / * Cause: Trying to access resource that is locked by a dead two-phase commit / / transaction that is in prepared state. / / * Action: DBA should query the pending_trans$ and related tables, and attempt / / to repair network connection (s) to coordinator and commit point / / If timely repair is not possible, DBA should contact DBA at commit / / point if known or end user for correct outcome, or use heuristic / / default if given to issue a heuristic commit or abort command to / / finalize the local portion of the distributed transaction.

Simply put, the reason for the 01591 error is that the object is locked by a distributed transaction in the "in-doubt" state. Distributed transactions use the "two-phase commit" two-phase commit technology. The way to solve this problem is to look at the internal table pending_trans$, to determine the distributed transaction information. The transaction in this state is mainly due to the sudden interruption of the network during the distributed transaction, which causes the distributed transaction can not end normally and waits for the transaction response of the interrupt node. As a result, tables locked by the transaction of each node are not released.

Treatment method

Rollback force '20.13.14721'

The parameter to Rollback force is the number that records the local transaction information in DBA_2PC_PENDING, namely LOCAL_TRAN_ID.

Processing is relatively simple, here by the way to share the relevant knowledge points of distributed transactions.

Knowledge points related to distributed transactions

Distributed transaction, to put it simply, means that a transaction is executed locally and remotely, and the local needs to wait for confirmation of the completion of the remote transaction before making the next local operation. For example, through the row record of the dblink update remote database, if there is a network exception during execution, or other events cause the local database not to know the execution of the remote database, the in doublt error will occur. At this point, dba intervention is required and needs to be dealt with in a variety of situations.

Oracle automatically handles distributed transactions to ensure the consistency of distributed transactions, with all sites committing or rolling back. In general, the process is completed in a very short time and cannot be detected at all.

However, if a connection is broken or a database site CRASH occurs during commit or rollback, the commit operation may not continue and the DBA_2PC_PENDING and DBA_2PC_NEIGHBORS will contain unresolved distributed transactions. For the vast majority of cases, when the connection is restored or the CRASH's database is restarted, distributed transactions are automatically resolved without human intervention. Only when the object locked by the distributed transaction urgently needs to be accessed, the locked rollback segment prevents the use of other transactions, the network failure or the recovery of the CRASH database takes a long time, and so on, the manual operation is used to maintain the distributed transaction. Manual forced commit or rollback will lose the characteristics of two-tier commit. Oracle can no longer guarantee the consistency of transactions, which should be guaranteed by manual operators.

With ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY, you can prevent Oracle from automatically resolving distributed transactions, even if the network resumes connectivity or CRASH's database is restarted.

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY recovery automatically resolves distributed transactions.

Two important views

1. DBA_2PC_PENDING

DBA_2PC_PENDING: lists all outstanding transactions. This view is empty before the pending transactions are filled in, and is also emptied after resolving this.

Description of the STATE column of DBA_2PC_PENDING

SELECT * FROM DBA_2PC_PENDING

2. DBA_2PC_NEIGHBORS

DBA_2PC_NEIGHBORS: lists all outstanding transactions obtained (from the remote client) and sent (to the remote server) and indicates whether the local node is the commit point site for the transaction.

This is the solution to the ORA-01591 error of Oracle database distributed transaction. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.