Hello,

I need to execute number of inserts on different tables in the same database and requirements are to insert everything or nothing. JDBC Adapter connection set to LOCAL_TRANSACTION TYPE and I have flow logic as follows:
TRY
Start Trans
→ insertBatch1
→ insertBatch2
→ insertBatch3
Commit Trans
CATCH
rollback trans

I am novice in using JDBC services and have few questions:

1. Is every insert create connection?
2. If 1 is yes then what would be best way to code, as JDBC User Guide says that you can not have multiple connections in explicit/implicit transaction of type local
3. What is XA connection type used for, can it be used in this case
4. What my choices are

Please advise. Any help/comment apprecited

Thanks In advance

Answers to your questions:

1. As you mention that the three tables being inserted are in the same database, I assume they are all using the same adapter connection. In that case, the JDBC adapter’s pooling mechanism will ensure optimal reuse of database connections.
2. Correct, if you are inserting across multiple data sources (i.e. different adapter connections) then you cannot use type local connections.
3. XA is a standard for doing transaction management across multiple separate data sources. You would use this if you want to insert into two or more different (XA-compliant) databases within one transaction.
4. Your pseudo-code looks fine.

regards,

Jonathan Heywood
webMethods Professional Services

Thanks fo quick reply Jonathan,

I did implemented the code. But the trouble I having is that this transaction executed in service that called by TN Processing rule in asyncrhronous mode, therefore I may have high number of transactions running in the same time. Transaction ID generated as unique number but I always end up with incorrect nesting or unable to rollback transaction error, which lead to server restart ( not really conviniene). If I process them in sync mode then I am fine, but it is not what I need from performance point of view.

Is it possible, that this is connected to JDBC Connection setting, where my max pool size less than number of transactions running at the same time?
Is there any limitation on this number, can it be 500? I would have on average 6000-7000 transactions to process in an hour coming from retail station and trying to simulate system load for performance in Development environment?

Any help/comments?

Thanks, Julietta

Hi Julietta,

Change your pseudo code slightly:

Drop any previous trans start records
Start Trans
TRY
→ insertBatch1
→ insertBatch2
→ insertBatch3
Commit Trans
CATCH
→
->rollback trans

You can map out the txnid to an arbitrary field if you use concurrent txns/connections.

Regards,
Adrian

Julietta,

Beware about pushing up the max pool size too far as you need to ensure that the DBMS can handle that number of concurrent sessions. A few hundred would still be OK, but I wouldn’t go much higher.

If you anticipate a large number of process being kicked off, then you can control things somewhat using JDBC pool settings. If a process wants a connection from the pool but they are all still in use, then this process will block (i.e. wait) for one to become available. It will wait for a maximum time specified in the connection under Block Timeout. Default is 1 sec, so if a process has waited for more than 1 sec and no connection has become available, then it will throw an exception saying “unable to get connection from pool”. In you case, you may well want to increase this value to 30 sec or even more to ensure that any processes waiting for a connection will wait for long enough and not time out.

Also, do consider whether you need to use transactions at all. You pseudocode implies you do, as you are doing three inserts as part of a single transaction. If there is only one insert, or they are unrelated and a failure of the third does not necessitate a rollback of the third, then please use NO_TRANSACTION for the connection type as it makes things a lot simpler.

regards,

Jonathan

Hi Julietta,
I had a same issue. Have you used LOCAL_TRANSACTION type to implement the code below?

Start Trans
TRY
→ insertBatch1
→ insertBatch2
→ insertBatch3
Commit Trans
CATCH
→
->rollback trans

I have not tried to use XA_TRANSACTION, but I know I can not use BatchInsertSQL or BatchUpdateSQL adapter services if I choose XA_TRANSACTION type. That’s not what I want.
Any help is appreciated!

Thanks,
Mike

Hi All,

I am facing the similar issue which is stated above. map is coded in flow.
WM 6.5 build 394.

I am using the connection type as LOCAL_TRANSACTION and the other details as below:
Enable Connection Pooling: true
Minimum Pool Size: 0
Maximum Pool Size: 50
Pool Increment Size: 1
Block Timeout (msec): 3000
Expire Timeout (msec): 1000
Startup Retry Count: 0
Startup Backoff Timeout (sec): 10

Start Trans (taking the transaction name)
SEQUENCE (exits on SUCCESS)
SEQUENCE (exits on SUCCESS)
→ insertBatch1 (batchInsertSQL adapter)
→ updateBatch1
→ Commit Trans (mapping the transaction name from above)
SEQUENCE(exits on DONE)
→
->rollback trans (mapping the transaction name from above)

But in the above sequence, the data posting to the DB table is getting committing before even coming to the step “Commit Trans” and it fails at this step as the data already got committed.

Please advise with any pointers to get this issue resolved.

Thanks in advance.

Best Regards,
Krishna

Hi All,

I am facing the similar issue which is stated above. map is coded in flow.
WM 6.5 build 394.

I am using the connection type as LOCAL_TRANSACTION and the other details as below:
Enable Connection Pooling: true
Minimum Pool Size: 0
Maximum Pool Size: 50
Pool Increment Size: 1
Block Timeout (msec): 3000
Expire Timeout (msec): 1000
Startup Retry Count: 0
Startup Backoff Timeout (sec): 10

Start Trans (taking the transaction name)
SEQUENCE (exits on SUCCESS)
SEQUENCE (exits on FAILURE)
→ insertBatch1 (batchInsertSQL adapter)
→ updateBatch1
→ Commit Trans (mapping the transaction name from above)
SEQUENCE(exits on DONE)
→
->rollback trans (mapping the transaction name from above)

But in the above sequence, the data posting to the DB table is getting committing before even coming to the step “Commit Trans” and it fails at this step as the data already got committed.

Please advise with any pointers to get this issue resolved.

Thanks in advance.

Best Regards,
Krishna

Hi,

When using LOCAL_TRANSACTION be careful of this.

The built-in Transaction Manager, that handles implicit transactions will roll back transactions, if the (Top-Level) Service fails.

If you use try-catch functionality and in addition an “exit flow signal success” step in the catch-sequence. That means, the Service never fails, even if an exception occurs in the try-sequence. So the roll back will not happen!

This may help.

Hi Adrian,

Can you please tell me what difference it makes on using start transaction service inside and outside of try block.

Regards,
Manikumar

Hi Manikumar,

when placing the startTransaction inside the Try-block it is not possible to rollback the transaction in the Catch-block as the transaction identifier is not available there.

Therefore startTransaction needs to be placed outside of the Try-Catch-block.

Regards,
Holger

Hi Holger,

Normally, If user wants to rollback the transaction whenever any error occurs, then rollbackTransaction service should be placed in catch block right?

I think we can place rollbackTransactoin service in catch block by hard coding transaction id in start, commit and rollbackTranssaction services. Please correct me if I am wrong.

Here still I have a doubt why startTransaction service outside of try-catch block.

Regards,
Manikumar

Hi Manikumar,

you are right w.r.t. rollbackTransaction to be placed in the Catch-block.

When hardcoding the Transaction-ID the services cannot be executed multiple times in parallel. This will lead to a duplicate Transaction-ID.

Outline of correct sequence:

startTrans
SEQ (SUCCESS)
SEQ (FAILURE)
insert or update
commitTrans
SEQ (DONE)
getLastError
rolllbackTrans

When startTransaction is placed inside the Try sequence the output with the transaction id is not available in the Catch sequence.
Even if the variable seems to be available in the pipeline it does not have a valid content.
This is a flaw in Designer which exists for a very long time now. It was also present in Developer.

Regards,
Holger

Hi Holger,

Thanks for detailed explanation.
As I am newbie, I have few more quires. Forgive me for taking your valuable time. Can you please clarify below quires.

In Adapter runtime user guide I have read that “Implicit and explicit transactions should not have more than one LOCAL_TRANSACTION connection and can have any number of XA_TRANSACTION” connections.

IS can handle multiple XA_TRANSACTION connections but why dont IS can handle multiple lLOCAL_TRANSACTION connections in single transaction boundary.

If you choose to provide dynamic user credentials
at run time, all the adapter services using the LOCAL_TRANSACTION connection
within a single transaction must use the same user credentials. For example, if you
have two adapter services, s1 and s2, configured using the LOCAL_TRANSACTION
connection c1 in a single transaction context, both s1 and s2 must either use the same
dynamic credentials at run time or the default configured credentials provided at design
time.

I have read above explanation in user guide. If that is the case, Can we use multiple LOCAL_TRANSACTION connections without providing dynamic credentials.

Thanks & Regards,
Manikumar