Distributed transaction, Oracle Distributed Transactions, distributed transaction in oracle
Distributed transaction, Oracle Distributed Transactions, distributed transaction in oracle
A distributed transaction modifies data related to
two or more databases, it contains DML statements than span many nodes. For a
distributed transaction to be successful all or none of the database nodes
involved in the transaction need to commit or rollback the whole transaction.
Note the difference between a distributed and a
remote transaction; a remote transaction contains one or more DML statements
that are executed on the SAME remote node, consider the examples below:-
Example of Distributed
transaction:
insert into table@remotesite;
insert into mytable: --local table
commit;
Example of Remote transaction:
insert into table@remotesite;
Database links are used to communicate between the
databases performing distributed transactions. In a distributed environment, it
is fundamental that the global name of a database is unique in the network so
that each database can be unambiguously identified. Oracle Distributed
Transactions are Operating System and Database version independent.
The global name of the database is composed of
DB_NAME.DB_DOMAIN, where DB_DOMAIN specifies the network domain.
The below sql will allow to check the current name of
the database and to change it if necessary to a new database name:
SQL> SELECT *
FROM global_name;
SQL> ALTER DATABASE RENAME GLOBAL_NAME TO
<NEW_DB_NAME>;
Two-Phase Commit (2PC)
The two phase-commit mechanism is used to ensure data
integrity in a distributed transaction. It is automatically used during all
distributed transactions and coordinates either the commit or roll back of all
the changes in the transaction as a single, self-contained unit.
There are three phases for the Two-Phase Commit
(2PC):
COMMIT: The initiating node commits and writes to its
redo log the committed SCN. The Data Block locks are released.
FORGET:
Pending transactions tables are related database views are cleared
(dba_2pc_pending/dba_2pc_neighbors)
NOTE:-
A crash during the PREPARE Phase results in a
ROLLBACK
A crash during the COMMIT Phase results in either
COMMIT or ROLLBACK
Recoverer Process (RECO)
If a distributed transaction fails, it may leave
in-doubt transactions on one or more databases. RECO is a mandatory background
process that, in a distributed database, automatically resolves failures in
distributed transactions. The RECO process of a node automatically connects to
other databases involved in an in-doubt distributed transaction; it can use an
existing connection or establish a new connection to other nodes involved in
the failed transaction.. When RECO reestablishes a connection between the
databases, it automatically resolves all in-doubt transactions, removing from
each database's pending transaction table any rows that correspond to the
resolved transactions.
For RECO to start - set in parameter 'distributed_transactions
> 0'
If the open_links parameter is set to 0, then no distributed
transactions are allowed.
The RECO process is present only if the instance permits
distributed transactions.
Disabling and Enabling RECO:-
We can enable and disable RECO using the ALTER SYSTEM
statement with the ENABLE/DISABLE DISTRIBUTED RECOVERY options.
We can disable distributed recovery if the database
is mounted but not open. We can temporarily disable RECO to force the failure
of a two-phase commit and manually resolve the in-doubt transaction.
-To disable RECO:
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY
-To enable RECO and let it automatically resolve
indoubt transactions
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
Some Error messages related to distributed transactions fail are shown below:
ORA-02053: transaction committed, some remote DBs may
be in-doubt
The transaction has been locally committed, however
we have lost communication with one or more local coordinators.
ORA-02054: transaction in-doubt
The transaction is neither committed nor rolled back
locally, and we have lost communication with the global coordinator.
ORA-02050: transaction rolled back, some remote DBs
may be in-doubt
Indicates that a communication error occurred during
the two-phase commit
ORA-01591: lock held by in-doubt distributed
transaction
Encountering the above error, users/applications
unable to proceed with their work (i.e., trying to access resource that is
locked by a dead two-phase commit transaction). In this case, Oracle
automatically rolls back the user attempted transaction and the DBA has now to
manually commit or rollback the in-doubt transaction.
ORA-02049 timeout: distributed transaction waiting
for lock
Increasing the value of the
distributed_lock_timeout/retry the issued SQL, are typical approaches when this
error is encountered. If it becomes a recurrent problem and certain
transactions appear to be hanging for ever or causing contention in the
database, then further investigation is required, and there is a need determine
what other transaction is holding the lock and what type of problem exists.
Some more articles you
might also be interested in :-
Distributed transaction in oracle
ORA-02050: transaction rolled back, some remote DBs may be
in-doubt
ORA-01591: lock held by in-doubt distributed transaction
ORA-31693: Table data object failed to load/unload in expdp
Comments
Post a Comment