2 PHASE COMMIT OVERVIEW
Two-phase commit protocol is comprised of a set of actions that ensure a transaction involving multiple
databases does not produce unsynchronized updates.
Two-phase commit provides a way for a series of database interactions on multiple different data sources
to be grouped together and completed or rolled back as a single transaction. Two-phase commit
transactions that represent a series of database interactions on multiple data sources are referred to as
global transactions.
At the beginning of a global transaction, a global transaction ID (XID) is generated and used by an external
transaction manager to drive the two-phase commit processing across all of the resource managers
involved. Each database interaction within the scope of the global transaction is executed upon its
associated resource manager.
The results of the interactions are then sent back to the application for processing. When the database
interactions within the scope of the global transaction are finished, a prepare call is sent to each resource
manager that was accessed by the global transaction.
The prepare call provides each resource manager a chance to determine and report on its ability to commit
the work it has done as a part of the global transaction. Upon receiving verification that each resource
manager can commit its work, the transaction manager sends a commit call to each resource manager.
At any point in time prior to sending the commit call, the two-phase commit transaction can be rolled back.
If the transaction is rolled back, a rollback call is sent to each resource manager involved in the transaction
and the temporary changes are removed or discarded.
If any database failures occur during the commit phase, the external transaction manager tries to
reestablish a connection with the failed resource manager and resumes its call for the resource manager to
commit.
If the transaction manager fails during the commit phase, the transaction manager performs recovery
processing upon restart and attempts to reestablish a connection with all of the resource managers
involved. When the transaction managers reestablishes the connections, it resumes its call for the resource
managers to commit.
Below two scripts to find transactions sql_text (1) and to execute roll back automatically (2):
1) check_2pc.sh
. /home/oracle11/cfg/env_ORCL
sqlplus -s / As sysdba @/home/oracle11/check_2pc.sql
a) check_2pc.sql
alter session set nls_date_Format='YYYYMMDD';
col filename new_value filename
select '/var/log/'||SYSDATE||'_'||NAME||'_check_2pc.log' filename from v$database;
spool &filename append;
SET SERVEROUTPUT ON
DECLARE
tmpStmt VARCHAR2(4000);
ESITO VARCHAR2 (4000);
CURSOR table_cur IS
select a.sql_id as sql_id from v$session a, dba_2pc_neighbors n, dba_2pc_pending p where
a.USERNAME=n.DBUSER_OWNER and a.STATUS='ACTIVE' and p.state='prepared' group by a.sql_id;
BEGIN
FOR tbl IN table_cur LOOP
BEGIN
tmpStmt := 'SELECT s.sql_text FROM DBA_HIST_ACTIVE_SESS_HISTORY h join DBA_HIST_SQLTEXT s on
h.sql_id=s.sql_id where h.SQL_ID = '''||tbl.sql_id||''' ORDER BY 1';
EXECUTE IMMEDIATE tmpStmt into ESITO;
DBMS_OUTPUT.PUT_LINE(ESITO);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
alter session set nls_date_Format='YYYY-MM- DD HH24:MI:SS';
set head off feedback off
select sysdate from dual;
select '– — — — — — — — — — — — — — ' from dual;
exit
2) rollback.sh
. /home/oracle11/cfg/env_ORCL
sqlplus -s / As sysdba @/home/oracle11/rollback.sql
a) rollback.sql
alter session set nls_date_Format='YYYYMMDD';
col filename new_value filename
select '/var/log/'||SYSDATE||'_'||NAME||'_rollback_2pc.log' filename from v$database;
spool &filename append;
SET SERVEROUTPUT ON
DECLARE
tmpStmt VARCHAR2(4000);
BEGIN
FOR sqlStmt IN
(
SELECT LOCAL_TRAN_ID FROM dba_2pc_pending where state='prepared' order by FAIL_TIME
)
LOOP
tmpStmt := 'ROLLBACK FORCE '''||sqlStmt.local_tran_id||'''';
DBMS_OUTPUT.PUT_LINE(tmpStmt);
EXECUTE IMMEDIATE tmpStmt;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
— select 'rollback force '''||LOCAL_TRAN_ID||''';' as TO_ROLLBACK from dba_2pc_pending where
state='prepared' order by FAIL_TIME;
alter session set nls_date_Format='YYYY-MM- DD HH24:MI:SS';
set head off feedback off
select sysdate from dual;
select '– — — — — — — — — — — — — — ' from dual;
exit
https://www.ibm.com/support/knowledgecenter/en/SSEPH2_13.1.0/com.ibm.ims13.doc.ccg/ims_ct_2phazcomt_overview.htm