0

Today I was hit by a successful 2pc that wasn't materialized in Oracle. The other participant was MSMQ which materialized fine.

The problem is that I did not get an Exception in the application (using c# odp.net). Later I found the In-Doubt Transactions in sys.dba_2pc_pending.

Could I somehow have detected this in my application?

EDIT: This is not about getting 2pc to work. It does work, and for more than a year until a day where some rows where missing. Please read about In-Doubt Oracle transactions link1 and pending transactions link2

Stig
  • 1,974
  • 2
  • 23
  • 50
  • 1
    Maybe you can show us your exception handling code.. and you should make sure it is actually handling other ORA errors. There's an OracleException class. – Christian Shay Feb 11 '15 at 03:52
  • Actually there is no Exception handling, and therefore the error could not be swallowed. Also the other participant (MSMQ) did commit fine. The logic is running inside a Nservicebus handler. – Stig Feb 11 '15 at 08:25
  • Can you post sample code. I assume you're using system.transaction? – b_levitt Feb 11 '15 at 15:46
  • NServicebus uses system.transaction, my code doesn't. I use NHibernate/odp.net paticpanting in a DTC with MSMQ created by NServicebus. – Stig Feb 11 '15 at 19:08

1 Answers1

0

My first thoughts is to make sure that distributed transaction processing is enabled on the oracle listener.

In my case no error was thrown. We use RAC and the service did not have distributed transaction processing enabled. In a stand-alone system I'm not sure what this would do, but in the case of RAC it serves the purpose of identifying the primary node for handling the transaction. Without it, a second operation that was supposed to be in the same operation just ended up starting a new transaction and deadlocked with the first.

I have also had significant amounts of time go by without an issue. By luck (there's probably more) it just so happened that transactions were never split over the nodes. But then a year later the same symptoms creap up and in all cases either the service didn't have the DTP flag checked or the wrong service name (one without DTP) was being used.

From:http://docs.oracle.com/cd/B19306_01/rac.102/b14197/hafeats.htm#BABBBCFG

Enabling Distributed Transaction Processing for Services For services that you are going to use for distributed transaction processing, create the service using Enterprise Manager, DBCA, or SRVCTL and define only one instance as the preferred instance. You can have as many AVAILABLE instances as you want. For example, the following SRVCTL command creates a singleton service for database crm, xa_01.service.us.oracle.com, whose preferred instance is RAC01:

srvctl add service -d crm -s xa_01.service.us.oracle.com -r RAC01 -a RAC02, RAC03

Then mark the service for distributed transaction processing by setting the DTP parameter to TRUE; the default is FALSE. Enterprise Manager enables you to set this parameter on the Cluster Managed Database Services: Create Service or Modify Service page. You can also use the DBMS_SERVICE package to modify the DTP property of the singleton service as follows:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE(service_name =>'xa_01.service.us.oracle.com', DTP=>TRUE);

b_levitt
  • 7,059
  • 2
  • 41
  • 56