| Author |
Message
|
| Maverik |
 Posted: Wed Jul 12, 2006 11:40 pm Post subject: Db Transaction Commit |
 |
|
Apprentice
Joined: 14 Jun 2005
Posts: 27
|
Hello all,
I have a flow with two compute nodes accessing the same database.
The first compute only does a select on a table. The second compute is doing a insert into the same table later in the flow.
I am getting a Transaction will be committed later, as there is a pending automatic transaction existing error. I tried changing the insert compute nodes transaction to Commit, then i made both commit, but nothing is working.
I searched the forum and found the solution of declaring a new dsn, but dont want to do that.
Please help. |
|
| Back to top |
|
 |
| shalabh1976 |
| Posted: Thu Jul 13, 2006 1:16 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
What DB are you using ?
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| Maverik |
| Posted: Thu Jul 13, 2006 1:53 am Post subject: |
|
|
Apprentice
Joined: 14 Jun 2005
Posts: 27
|
Using DB2 8.2 and WBIMB v 6.0.0.1
Thanks. |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Thu Jul 13, 2006 1:56 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
After changing the transaction mode to Commit what errors, if any, are you getting ?
I have used a similar scenario as yours with V2.1 and I did not face any problems.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| Maverik |
| Posted: Thu Jul 13, 2006 2:08 am Post subject: |
|
|
Apprentice
Joined: 14 Jun 2005
Posts: 27
|
Let me explain about my flow
MQInput(Tx:Automatic)-->compute1(Tx: Automatic, select from table)-->tryCatch
try of TryCatch-->compute2(Tx: Automatic, doing some validation, no db interaction, multiple propagates)-->MQOutput(Tx:No)
catch of TryCatch-->compute3(Tx: Commit, insert to same table)-->MQOutput(Tx:No)
The error from the failure terminal of the compute3 is "Transaction will be committed, but a pending automatic transaction exists".
Which is this automatic transaction is what i cant figure out ?
Thanks in advance |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Thu Jul 13, 2006 3:07 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Let me simulate this scenario and see what happens:
Meanwhile ponder on this:
If there is no error the flow path will not travel through the catch.
Now in normal scenario the flow is :
MQInput->Compute->TryCatch->Compute2->MQOutput
In all the nodes except the last(MQOutput) the transaction mode is automatic.
Now in the last you are changing the mode to explicit ->commit.
Hence the error, I think.
If you change the transaction mode to Automatic in the MQOutput, the error should go away.
I am still working on this in simulation.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Thu Jul 13, 2006 3:54 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
I checked and found that what you say happens.
If I change the transaction mode to commit on the first compute then the problem is resolved.
Since I am anyway doing a SELECT there this should not make any difference.
I opened one of my earlier flows to see this and indeed that is how I had done it also.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| Maverik |
| Posted: Thu Jul 13, 2006 4:33 am Post subject: |
|
|
Apprentice
Joined: 14 Jun 2005
Posts: 27
|
Thanks shalabh1976 !!
That worked.
My only doubt now is, what if tomorrow i have an insert first and then another insert, and i don't want the first to be committed but the second to be, then what will I do, Or will i have to make my design to simply avoid these situations. |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Thu Jul 13, 2006 4:58 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
This is a simple database concept:
A rollback will rollback all transactions till the last commit point.
A commit will commit all transactions till the last commit.
You can never have a scenario where you do
Trans A followed by Trans B
You want Trans A to be under syncpoint control but not Trans B !!!
The moment Trans B is commited Trans A has to be committed.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| jbanoop |
| Posted: Sat Jul 15, 2006 10:30 pm Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
U could try using two datasources (DSNs) pointing to the same DB. Use one DSN for all the nodes you want to commit the transaction(s) on and the toher one for the automatic ones. It should work fine.
Regards,
Anoop |
|
| Back to top |
|
|
| amol5985 |
| Posted: Mon Jun 18, 2012 4:26 am Post subject: |
|
|
Newbie
Joined: 29 Sep 2010
Posts: 5
|
Hi shalabh1976,
I am getting the same exception but in my case for both the compute node has same transaction property as "Commit".
In my scenario compute1 is in main flow and compute2 is in subflow. Message is getting propagated to the failure terminal from compute1 and failure terminal is connected to the subflow which do the database insert for exception table.
Now this problem is getting resolved in case if I select a Transaction property as "Automatic" for compute2. |
|
| Back to top |
|
|
|
|
