CODEWITHSUNDEEP
oracle-databaseoracle12c
Matthew Moisen
Matthew Moisen

Reputation: 18299

Is a Two-Phase Commit initated when Selecting from a remote DB and inserting into a local DB?

Does SELECTing from a remote database and INSERTing into the local database trigger a two-phase commit?

For example, I want to insert into my local table foo all the rows from the remote table foo@bar. At no time am I performing an INSERT, UPDATE, or DELETE on the remote database. Does this initiate a two-phase commit?

BEGIN
    INSERT INTO foo (
       a,
       b,
       c
    ) SELECT a,
        b,
        c
    FROM foo@bar;

    COMMIT;
END;
/

Furthermore, are there any data dictionary views that I can check out to confirm whether or not any transaction performs a two-phase commit?

I tried selecting from DBA_2PC_PENDING and DBA_2PC_NEIGHBORS, on both the local and remote database, after performing an insert into the remote database and before, during, after the commit, but it returned no rows. The documentation seems to state that these tables are only populated by in-doubt transactions.

Upvotes: 1

Views: 525

Answers (1)

Philipp Salvisberg
Philipp Salvisberg

Reputation: 360

No, this statement does not trigger a two-phase commit. It is a remote transaction, it changes data only on the local site. The remote site is read (consistently), but left unchanged. The transaction cannot become in-doubt.

See Managing Distributed Transaction and MOS Note 13229.1 (requires an Oracle support account).

The Note 13229.1 describes the difference between remote transaction and distributed transactions as follows:

  • A remote transaction contains one or more statements which all reference the same remote node.

  • A distributed transaction contains statements that modify data in two or more distinct nodes. The only place where 2-PHASE COMMIT comes into play.

Upvotes: 2

Related Questions