In a distributed transaction system, databases perform Data Manipulation Language (DML) operations across multiple databases. This complexity arises from the need to maintain consistency between these separate databases, or even across different DBMSs like Oracle and MS SQL. To ensure atomicity, Oracle uses a 2-phase commit mechanism, involving phases such as “prepare”, “commit”, and “forget”. These phases form the handshake mechanism for distributed transactions.<\/p>\n\n\n\n
However, issues like network failures, system problems, or reconfiguration of the underlying database objects can cause failures in one phase of the transaction. When this happens, the transaction enters an “in-doubt” state. Typically, the RECO (Recovery) process resolves these issues, but in certain scenarios, it may fail.<\/p>\n\n\n\n
If the lookup tables are consistent, resolving an in-doubt transaction is straightforward. Here’s how to identify and resolve pending transactions.<\/p>\n\n\n\n
You can check for pending transactions using this SQL query:<\/p>\n\n\n\n
SET LINESIZE 200;\nSET PAGESIZE 50;\nSET COLSEP '|';\nSET LONG 500;\nSET HEADING ON;\nSELECT \n LOCAL_TRAN_ID, \n GLOBAL_TRAN_ID, \n STATE, \n MIXED, \n COMMIT# \nFROM \n DBA_2PC_PENDING;<\/code><\/pre>\n\n\n\nIf the state is “prepared,” you can force a rollback or commit:<\/p>\n\n\n\n
-- Force Rollback\nSQL> ROLLBACK FORCE '96.22.163456';\n\n-- Force Commit\nSQL> COMMIT FORCE '96.22.163456';<\/code><\/pre>\n\n\n\nIf the command hangs, proceed to handle stuck transactions (see below).<\/p>\n\n\n\n
If the Transaction is “Collecting”<\/h3>\n\n\n\n
If you receive the following error:<\/p>\n\n\n\n
ERROR at line 1:\nORA-02058: no prepared transaction found with ID 96.22.163456<\/code><\/pre>\n\n\n\nExecute the following command to purge the lost transaction entry:<\/p>\n\n\n\n
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('96.22.163456');<\/code><\/pre>\n\n\n\nHandling Stuck Transactions<\/h3>\n\n\n\n
For stuck transactions, consistency between the DBA_2PC_PENDING view and the actual transaction in X$KTUXE must be maintained. Here are common conditions and solutions for stuck transactions.<\/p>\n\n\n\n
Condition 1: Entry Exists in DBA_2PC_PENDING but Not in X$KTUXE<\/h4>\n\n\n\n
When a transaction shows up in the DBA_2PC_PENDING but not in the X$KTUXE table, indicating the transaction doesn\u2019t exist in reality:<\/p>\n\n\n\n
SET LINESIZE 200;\nSET PAGESIZE 50;\nSET COLSEP '|';\nSET LONG 500;\nSET HEADING ON;\n\nSELECT * \nFROM X$KTUXE \nWHERE KTUXEUSN = 96 \n AND KTUXESLT = 22 \n AND KTUXESQN = 163456;\n\n-- Returns: No Rows<\/code><\/pre>\n\n\n\nSolution<\/strong>: <\/h4>\n\n\n\nClean up the transaction manually:<\/p>\n\n\n\n
SQL> DELETE FROM SYS.PENDING_TRANS$ WHERE LOCAL_TRAN_ID = '96.22.163456';\nSQL> DELETE FROM SYS.PENDING_SESSIONS$ WHERE LOCAL_TRAN_ID = '96.22.163456';\nSQL> DELETE FROM SYS.PENDING_SUB_SESSIONS$ WHERE LOCAL_TRAN_ID = '96.22.163456';\nSQL> COMMIT;<\/code><\/pre>\n\n\n\nCondition 2: No Entry in DBA_2PC_PENDING but Transaction Exists<\/h4>\n\n\n\n
When there is a transaction but no entry in the DBA_2PC_PENDING view, the transaction is orphaned. You may see an error like:<\/p>\n\n\n\n
SQL> ROLLBACK FORCE '96.22.163456'\n-- ORA-02058: no prepared transaction found with ID 96.22.163456<\/code><\/pre>\n\n\n\nSolution<\/strong>: Recover the transaction by inserting dummy records:<\/h2>\n\n\n\nSQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;\n\nSQL> INSERT INTO PENDING_TRANS$ (LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR, RECO_VECTOR, TYPE#, FAIL_TIME, RECO_TIME)\nVALUES ('96.22.163456', 299354, 'XXXXXXX.12345.1.2.3', 'prepared','P', hextoraw('00000001'), hextoraw('00000000'), 0, sysdate, sysdate);\n\nSQL> INSERT INTO PENDING_SESSIONS$ VALUES ('96.22.163456', 1, hextoraw('05004F003A1500000104'), 'C', 0, 30258592, '', 146);\n\nSQL> COMMIT;\n\nSQL> ROLLBACK FORCE '96.22.163456';<\/code><\/pre>\n\n\n\nFinally, remove the dummy entries:<\/p>\n\n\n\n
Condition 3: COMMIT or ROLLBACK Hangs<\/h4>\n\n\n\n
If COMMIT FORCE or ROLLBACK FORCE hangs, try the following steps:<\/p>\n\n\n\n
Solution<\/strong>:<\/p>\n\n\n\n\n- Delete the dictionary entries:<\/strong><\/li>\n<\/ol>\n\n\n\n
SQL> DELETE FROM SYS.PENDING_TRANS$ WHERE LOCAL_TRAN_ID = '96.22.163456';\nSQL> DELETE FROM SYS.PENDING_SESSIONS$ WHERE LOCAL_TRAN_ID ='96.22.163456';\nSQL> DELETE FROM SYS.PENDING_SUB_SESSIONS$ WHERE LOCAL_TRAN_ID = '96.22.163456';\nSQL> COMMIT;<\/code><\/pre>\n\n\n\n\n- Insert dummy records and force commit or rollback:<\/strong><\/li>\n<\/ol>\n\n\n\n
SQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;\n\nSQL> INSERT INTO PENDING_TRANS$ (LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR, RECO_VECTOR, TYPE#, FAIL_TIME, RECO_TIME)\nVALUES ('96.22.163456', 306206, 'XXXXXXX.12345.1.2.3', 'prepared','P', hextoraw('00000001'), hextoraw('00000000'), 0, sysdate, sysdate);\n\nSQL> INSERT INTO PENDING_SESSIONS$ VALUES ('96.22.163456', 1, hextoraw('05004F003A1500000104'), 'C', 0, 30258592, '', 146);\n\nSQL> COMMIT;\n\nSQL> COMMIT FORCE '96.22.163456';\nSQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('96.22.163456');<\/code><\/pre>\n\n\n\nConclusion<\/h3>\n\n\n\n
Managing in-doubt transactions in Oracle distributed systems can be tricky but is essential to ensuring transaction integrity. By using the steps above, you can effectively resolve unstuck and stuck transactions, allowing the distributed recovery process to complete smoothly.<\/p>\n","protected":false},"excerpt":{"rendered":"
In a distributed transaction system, databases perform Data Manipulation Language (DML) operations across multiple databases. This complexity arises from the need to maintain consistency between these separate databases, or even across different DBMSs like Oracle and MS SQL. To ensure atomicity, Oracle uses a 2-phase commit mechanism, involving phases such as “prepare”, “commit”, and “forget”. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-1792","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1792","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=1792"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1792\/revisions"}],"predecessor-version":[{"id":1795,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/1792\/revisions\/1795"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=1792"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=1792"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=1792"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}