Adapters using Explicit Transactions

, ,
1

Hi.

I’ve used a main service, which uses select adapter service and update service. Another service is called inside main service which uses batch insert adapater service.

Select and update adapter service uses one connection(Local_Transaction)
Insert adpater uses another connection(Local_Transaction)

I’ve used Start,commit,rollback for update and batch insert adapters.

Still i receive the following error.
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4018] Adapter Runtime (Adapter Service): Error while closing transactions at service completion Error:[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections…
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.
[ART.117.4015] Adapter Runtime (Adapter Service): Error(s) occurred while closing adapter connections.

Can anybody share an option to solve the error:( .

2

Hi Saratha,

is it required that the insert adapter uses a different connection?

Can you share the layout of your services (esp. where there transactions are started, commited and/or rollbacked)?

Is it neccessary for update and insert to be committed or rollbacked together (all-or-nothing) or can they be committed separately?

Looks like some improper transaction handling.
Unfortunately those services cannot be debugged in Designer/Developer as this breaks the transactions by default.

Regards,
Holger

3

Hi Holger.

The insert adapter service is to a different database, while Select and update is to a different database.

This is the flow of the service.

MAIN SERVICE:

Try sequence
Select adapter
–logic–
invoke Sub service
start transaction
Update adapter
commit transaction

SUB SERVICE:

start transaction
batch insert adapter
commit transaction

can you help me to resolve the error.

4

Try if this works.


MAIN SERVICE:
start transaction
Main sequence
  Try sequence
    Select adapter
     --logic--
    invoke Sub service
    Update adapter
   commit transaction


SUB SERVICE:

start transaction
batch insert adapter
commit transaction

Since multiple databases are involved did you try with a XA transaction connection?

5

If you plan to try Mahesh´s approach make sure that the transaction id from the sub service is dropped at the end of the sub service and will not be returned to the main service.
Otherwise it might corrupt the transaction in the main service.

As the select Adaptor does not really need transaction control, it can be useful to use an additional NO_TRANSACTION-connection for the select Adaptor outside of the transaction controlled block.

If Update and Insert should complete or fail together you should definitely consider XA_TRANSACTION here.
If Insert completes, but Update fails (or vice versa) XA_TRANSACTION is not neccessary, but depending on your use case and/or data consistency policies might lead to inconsistencies in the data.

Regards,
Holger

6

Hi,

Thanks for you replies.

I used start transaction step and then i called select adpater service and commited the transaction after updating the DB. It worked.

Thank you for sharing your thoughts.

Regards,
V.Saratha

7

Yes startTransaction should be very first step and make sure the transactionName is dropped properly before the next transaction starts and this would be tricky initially because that’s the way how local transaction objects work in the IS with propery Try/Catch sequence handling etc…

It sounds like you got it working! :smiley:

HTH,
RMG

8

HI,
one question if i want to used batch insert and in the same transaction another update normal to a different DB, how the configuration of the adapters have to be ?

batch insert —> local_transaction or xa_transaction
update normal ----> xa_transaction

i have thi doubt because i have read that the operation under batch have to use local_transaction.

thanks in advance…

9

Hi ZA VZ,

Batch Insert - Local Transaction
Update to other DB can be either Local or XA

10

Thumb rule is, you could have only one connection with LOCAL TRANSACTION type inside a transaction boundary, and any other connections should be of type XA while dealing with multiple instances of db…

So, either you can have one LOCAL and another as XA, or both could be XA…

Regards
Senthil

11

Hi Saratha,

one question!
but if I have to use both ?
batch insert with update to different database ? how would you use the adapter configuration?

12

Batch Insert and Batch Update ‘must’ use connections of type LOCAL TRANSACTION as per documentation.

You cannot use ‘Batch Insert to one database’, and ‘Update to another database’ inside a single transaction boundary…

Below is NOT possible

However, you can have them in two different transaction boundaries and below is possible

HTH
Senthil

13

Kindly review :Built-In Transaction Management Services: from the JDBC adapter guide. It is explained clearly with examples. To handle No, Local and XA Transactions.

14

I absolutely agree with Senthil. Please also check documentation which is clear on this.

Thanks,