This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,051 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Categories

Transcation rollback issue in SQL server DB poller

shann
shann Member Posts: 8
edited Feb 26, 2020 10:44AM in Business Process Management Suite

Hi All,

I do have a problem in using the DB adapter poller with SQLServer. The below steps describes my setup.

1. DB adapter is configured with SQLServer for polling every 15 seconds.

2. BPEL process receives the polled records and updates another db(oracle)

3. Both oracle and sqlserver (with the required SPs) are configured with XA datasource

4. db adapter datasource is set to xa for both the outbound connections

5. bpel.config.transcation is set to required

product version: 11.1.1.7

"Set XA Transaction Timeout" is checked in datasource transcation tab.

Problem: When the oracle db is not available, I am throwing the rollback exception from BPEL process but the change is committed on sqlserver.

I have enabled the  log configuration for oracle.soa.adapter to FINEST(32) and found the commit is done internally before the process is complete.


the below is the trace


ctivationSpec: [email protected][[

]]

[2014-07-18T14:04:35.616+10:00] [soa_server1] [NOTIFICATION] [] [oracle.soa.adapter] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']

[userId: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33346] [APP: soa-infra] Database Adapter test_delete <oracle.tip.adapter.db.DBEndp

oint start> Kicked off 1 threads.[[

]]

[2014-07-18T14:04:35.618+10:00] [soa_server1] [NOTIFICATION] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [u

serId: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] Database Adapter test_delete <oracle.tip.adapter.db.DBAdapte

rConstants getUniqueProcessIdentifier> Unique process identifier will be test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0[[

]]

[2014-07-18T14:04:35.619+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:

weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMa

nagerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor basicExecuteCall> SELECT REQUEST_NUM_ID, REQUES

T_STATUS, OPPORTUNITY_ID, CUSTOMER_CODE, PROJECT_CODE, REQUEST_HEADING, REQUEST_COMMENT, REQUEST_PROCESSED, REQUEST_FAILURE_COUNT FROM dbo.REQUEST WHERE (REQUEST_PROCESSED

= ?)[[

        bind => [1]

]]

[2014-07-18T14:04:35.623+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI

d: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.Lo

gManagerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.transaction.AbstractTransactionController beginTransaction> TX beginTransaction, stat

us=NO_TRANSACTION[[

]]

[2014-07-18T14:04:35.623+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI

d: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.Lo

gManagerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.transaction.AbstractTransactionController beginTransaction> TX Internally starting[[

]]

[2014-07-18T14:04:35.624+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI

d: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.Lo

gManagerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.sessions.AbstractSession beginExternalTransaction> external transaction has

begun internally[[

]]

[2014-07-18T14:04:35.624+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:

weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMa

nagerImpl] [SRC_METHOD: log] Database Adapter test_delete <oracle.tip.adapter.db.ox.O_XParser parse> Transforming the row(s) [<Request 1 />] read from the database into xm

l.[[

]]

[2014-07-18T14:04:35.624+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:

weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMa

nagerImpl] [SRC_METHOD: log] Database Adapter test_delete <oracle.tip.adapter.db.DBAdapterConstants isElementFormDefaultQualified> Element is Request namespace is http://x

mlns.oracle.com/pcbpel/adapter/db/top/testsqlserver[[

]]

[2014-07-18T14:04:35.624+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:

weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33347] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMa

nagerImpl] [SRC_METHOD: log] JCABinding=>  testsqlserver shouldProcessMessage: error = false --> true

[2014-07-18T14:04:35.702+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI

d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana

gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism executeBatchedStatements> Be

gin batch statements[[

]]

[2014-07-18T14:04:35.703+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:

weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogManager

Impl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism executeBatchedStatements> UPDAT

E dbo.REQUEST SET REQUEST_PROCESSED = ? WHERE (REQUEST_NUM_ID = ?)[[

]]

[2014-07-18T14:04:35.703+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:

weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogManager

Impl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism executeBatchedStatements>     b

ind => [0, 1][[

]]

[2014-07-18T14:04:35.703+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI

d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana

gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism executeBatchedStatements> En

d Batch Statements[[

]]

[2014-07-18T14:04:35.717+10:00] [soa_server1] [NOTIFICATION] [] [oracle.soa.adapter] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']

[userId: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33346] [APP: soa-infra] JCABinding=>  test_delete JCA Binding Component instance:

SCA_AS11R1 - successfully completed endpointActivation for portType=testsqlserver_ptt, operation=receive

[2014-07-18T14:04:35.717+10:00] [soa_server1] [NOTIFICATION] [] [oracle.soa.adapter] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)']

[userId: weblogic] [ecid: 0a36163dd1c027a2:67729baf:14719120a43:-8000-000000000005f3d7,1:33346] [APP: soa-infra] JCABinding=>  [test_delete.testsqlserver/1.0] :init Succes

sfully initialized testsqlserver_db.jca

[2014-07-18T14:04:35.720+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI

d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana

gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.transaction.AbstractTransactionController commitTransaction> TX commitTransaction, status=

STATUS_ACTIVE[[

]]

[2014-07-18T14:04:35.721+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI

d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana

gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.transaction.AbstractTransactionController commitTransaction> TX Internally committing[[

]]

[2014-07-18T14:04:35.829+10:00] [soa_server1] [TRACE:16] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userI

d: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogMana

gerImpl] [SRC_METHOD: log] Database Adapter test_delete <org.eclipse.persistence.internal.sessions.AbstractSession commitExternalTransaction> external transaction has comm

itted internally[[

]]

[2014-07-18T14:04:35.830+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId:

weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e9,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogManager

Impl] [SRC_METHOD: log] JCABinding=>  testsqlserver onBatchBegin: Batch 'test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0.test

sqlserver.Request1405656275830' (test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0.testsqlserver.Request1405656275830) starting

...

[2014-07-18T14:04:35.830+10:00] [soa_server1] [TRACE] [] [oracle.soa.adapter] [tid: Workmanager: , Version: 0, Scheduled=false, Started=false, Wait time: 0 ms\n] [userId: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e9,0] [APP: soa-infra] [SRC_CLASS: oracle.integration.platform.blocks.adapter.fw.log.LogManagerImpl] [SRC_METHOD: log] JCABinding=>  testsqlserver onBatchComplete: Batch 'test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0.testsqlserver.Request1405656275830' (test_delete_fineart_test_delete_1_0_soa_2ab29ef6_f318_42c3_92de_28d249030a6c_testsqlserveri0.testsqlserver.Request1405656275830) has completed - final size = 1

[2014-07-18T14:04:35.878+10:00] [soa_server1] [ERROR] [] [oracle.soa.bpel.engine] [tid: orabpel.invoke.pool-4.thread-7] [userId: weblogic] [ecid: 0a36163dd1c027a2:-357dc867:1474727e54e:-7ffd-00000000000093e7,1:33349] [APP: soa-infra] [composite_instance_id: 110029] [component_instance_id: 110022] [composite_name: test_delete!1.0] [component_name: testsqlserverbpel] This exception was caused by a bpelx:rollback fault being thrown or a ROLLBACK_ONLY FabricInvocationException being thrown. BPEL engine is directed to roll back the current JTA transaction. Root cause : [[

com.oracle.bpel.client.BPELFault: faultName: {{http://schemas.oracle.com/bpel/extension}rollback}

messageType: {{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage}

Tagged:
· Share on TwitterShare on Facebook
This discussion has been closed.