Distributed transactions allows you to have multiple DML’s over multiple databases within a single transaction
For instance, one local and one remote
insert into t values(1);
insert into t@db02 values(2);
commit;
If you lose connection to db02 and wants to commit, your database server may/does not know about the state of the remote transaction. The transaction then shows up als pending.
Oracle documentation mentions about ORA-2PC-CRASH-TEST transaction comment to test this behavior, however, anything like note 126069.1 who starts with grant dba to scott; should be banned.
Apart from granting DBA to scott and using commit tansaction commment 'ORA-2PC-CRASH-TEST-7', I can still use my good (bad?) old shutdown abort.
SQL> insert into t values(1);
1 row created.
SQL> insert into t@db02 values(2);
1 row created.
SQL> -- shutdown abort on db02
SQL> commit;
commit
*
ERROR at line 1:
ORA-02054: transaction 2.7.4509 in-doubt
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from DB02
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;
LOCAL_TRAN_ID STATE
---------------------- ----------------
2.7.4509 prepared
Now you’ve got an issue. Not only the state of the transaction is unknown, but the in-doubt transaction may prevent further DMLs
SQL> update t set x=x+1;
update t set x=x+1
*
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 2.7.4509
You need to decide whether to commit or rollback the transaction. Let’s say I want to rollback. I need to have FORCE TRANSACTION privilege
SQL> rollback force '2.7.4509';
Rollback complete.
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;
LOCAL_TRAN_ID STATE
---------------------- ----------------
2.7.4509 forced rollback
SQL> update t set x=x+1;
0 rows updated.
SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('7.20.4519')
PL/SQL procedure successfully completed.
SQL> select LOCAL_TRAN_ID, STATE from dba_2pc_pending;
no rows selected
The lock disappears, dbms_transaction.purge_log_db_entry can also cleanup old entries.