DBA_2PC_PENDING introduction
- 2020-06-12 10:51:51
- OfStack
DBA_2PC_PENDING
Oracle automatically handles distributed transactions, ensuring that all sites commit or roll back all of the distributed transactions. In most cases, the process is completed in such a short time that it is not noticeable at all. However, if at the time of commit or rollback, there is a connection break or a case of CRASH at a database site, the commit operation may not continue and DBA_2PC_PENDING and DBA_2PC_NEIGHBORS contain unresolved distributed transactions.
In the vast majority of cases, when the connection is restored or the database for CRASH is restarted, distributed transactions are resolved automatically, without human intervention. Manual operations are used to maintain distributed transactions only when the object that the distributed transaction is locked is in urgent need of access, the locked rollback segment prevents other transactions from being used, or when network failures or CRASH's database recovery takes too long.
Manually forcing a commit or rollback loses the two-tier commit feature, and Oracle cannot continue to guarantee the transaction's 1 portability, which should be guaranteed by the manual operator.
For distributed transactions, it is a good practice to name transactions. And during transaction execution, ALTER SESSION ADVISE COMMIT(ROLLBACK) can be used; Statement to provide reference information for manually resolving distributed transactions.
When there is a conflict in manually resolving distributed transactions, such as one site committing and another ROLLBACK, the records in DBA_2PC_PENDING are not cleared and must be cleared using the ES37en_TRANSACTION.PURGE_MIXED procedure.
If the database for CRASH must be rebuilt or cannot be started again, the records in DBA_2PC_PENDING cannot be cleaned automatically and need to be cleaned using the procedure es48EN_TRANSACTION.PURGE_ES51en_ES52en_ES53en.
In Oracle9i, when using the above two procedures, you must be in mode UNDO_MANAGEMENT = MANUAL, a restriction that Oracle does not write to the document. In general, 9i USES the AUTO schema (as recommended by Oracle), which means that to clear the information in DBA_2PC_PENDING, the database must be restarted twice, and it feels that these two procedures are not really useful.
In the event of an unresolvable distributed transaction, the table involved in the distributed transaction may be locked, and even the query operation cannot be performed on the table because Oracle cannot determine which data is committed and which is not, and cannot determine the result set visible to the query operation.
Using ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY, you can prevent Oracle from automatically resolving distributed transactions, even if the network is restored or the database of CRASH is restarted. ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY recovers automatically resolved distributed transactions.
To ensure SCN synchronization between databases, there are two ways to do this: perform SELECT * FROM ES95en@REMOTE before querling the data, or commit or roll back the current transaction before executing the query.
Oracle automatically handles distributed transactions, ensuring that all sites commit or roll back all of the distributed transactions. In most cases, the process is completed in such a short time that it is not noticeable at all. However, if at the time of commit or rollback, there is a connection break or a case of CRASH at a database site, the commit operation may not continue and DBA_2PC_PENDING and DBA_2PC_NEIGHBORS contain unresolved distributed transactions.
In the vast majority of cases, when the connection is restored or the database for CRASH is restarted, distributed transactions are resolved automatically, without human intervention. Manual operations are used to maintain distributed transactions only when the object that the distributed transaction is locked is in urgent need of access, the locked rollback segment prevents other transactions from being used, or when network failures or CRASH's database recovery takes too long.
Manually forcing a commit or rollback loses the two-tier commit feature, and Oracle cannot continue to guarantee the transaction's 1 portability, which should be guaranteed by the manual operator.
For distributed transactions, it is a good practice to name transactions. And during transaction execution, ALTER SESSION ADVISE COMMIT(ROLLBACK) can be used; Statement to provide reference information for manually resolving distributed transactions.
When there is a conflict in manually resolving distributed transactions, such as one site committing and another ROLLBACK, the records in DBA_2PC_PENDING are not cleared and must be cleared using the ES37en_TRANSACTION.PURGE_MIXED procedure.
If the database for CRASH must be rebuilt or cannot be started again, the records in DBA_2PC_PENDING cannot be cleaned automatically and need to be cleaned using the procedure es48EN_TRANSACTION.PURGE_ES51en_ES52en_ES53en.
In Oracle9i, when using the above two procedures, you must be in mode UNDO_MANAGEMENT = MANUAL, a restriction that Oracle does not write to the document. In general, 9i USES the AUTO schema (as recommended by Oracle), which means that to clear the information in DBA_2PC_PENDING, the database must be restarted twice, and it feels that these two procedures are not really useful.
In the event of an unresolvable distributed transaction, the table involved in the distributed transaction may be locked, and even the query operation cannot be performed on the table because Oracle cannot determine which data is committed and which is not, and cannot determine the result set visible to the query operation.
Using ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY, you can prevent Oracle from automatically resolving distributed transactions, even if the network is restored or the database of CRASH is restarted. ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY recovers automatically resolved distributed transactions.
To ensure SCN synchronization between databases, there are two ways to do this: perform SELECT * FROM ES95en@REMOTE before querling the data, or commit or roll back the current transaction before executing the query.