| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Database operations are NOT coordinated |
« View previous topic :: View next topic » |
| Author |
Message
|
| lium |
 Posted: Tue Aug 05, 2014 6:32 am Post subject: Database operations are NOT coordinated |
 |
|
Disciple
Joined: 17 Jul 2002
Posts: 184
|
There is message flow which accepts webservice call. Then it will invoke Compute node to update the 3 infomix tables through the same ODBC.
We have exception handler connecting to the SOAPInput's catch, Failure and Timeout terminal to handle any error situation. The error handler will log error to file, generate SOAP Fault, and writing error message to the queue.
The Compute node has transaction set to Automatic
The message flow is NOT configured to be coordinated and the broker queue manager is NOT configured as resource manager.
Requirement: the 3 tables operations needs to all succeed or rollback.
Problem: With runtime exception, The first 2 table operation succeeded, However, the 3rd table operation failed.
I checked the Internet, it is obviously this is broker-coordinated transaction, or partially coordinated transaction rather than global transaction.
My understanding is: all database operations within the same Compute node would be always either all committed or rollback. But it seems to conflict the fact.
I also read the statement from IBM help center:
| Quote: |
Updates that are made by a message flow are committed when the flow processes the input message successfully. The updates are rolled back if both of the following conditions are met:
•A node in the flow throws an exception that is not caught by a node other than the input node (for example, the node itself, or a TryCatch node)
•The Catch terminal of the input node is not connected
|
I know if we don't connect the catch terminal, the all 3 operations will be all committed or rollback. However, if we have our own exception handler, then does it say it can not be coordinated?
Thanks,
Michael |
|
| Back to top |
|
 |
| martinb |
| Posted: Wed Aug 06, 2014 5:06 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
I believe the strategy you need to apply here is
- Try - Catch
- log (Perhaps using local commit outside of main transaction)
- Re throw back to Input node for overall flow transaction to be rolled back
Please review:
Handling errors in message flows
| Quote: |
| If you connect the Catch terminal of the input node, you are indicating that the flow handles all the exceptions that are generated anywhere in the out flow. The broker performs no rollback, and takes no action, unless there is an exception on the catch flow. If you want any rollback action after an exception has been raised and caught, you must provide this in the catch flow. |
Message flow transactions
Error Handler sample |
|
| Back to top |
|
|
| lium |
| Posted: Wed Aug 06, 2014 5:39 am Post subject: |
|
|
Disciple
Joined: 17 Jul 2002
Posts: 184
|
Thanks,
All database operations within the same Compute node will be coordinated under any circumstances.
Are you saying this is false statement? |
|
| Back to top |
|
|
| McueMart |
| Posted: Wed Aug 06, 2014 6:39 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Take a user trace.
In it you should see something along the lines of :
"A local transaction has been committed" or "A local transaction has been rolled back" (depending if the broker deemed the transaction to complete successfully or not)
Post any interesting parts of the user trace here. |
|
| Back to top |
|
|
| lium |
| Posted: Wed Aug 06, 2014 8:52 am Post subject: |
|
|
Disciple
Joined: 17 Jul 2002
Posts: 184
|
This is production, and we can not turn on trace.
I might have to develop a message flow to find out this answer.
However, this is very common question, and address the most basic topic.
I am wondering if any one can tell the truth.
Thanks |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Aug 06, 2014 8:56 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Are all three tables under the same DSN?
Is the DSN set on the Compute node, or is it set in the insert statements? |
|
| Back to top |
|
|
| lium |
| Posted: Wed Aug 06, 2014 9:20 am Post subject: |
|
|
Disciple
Joined: 17 Jul 2002
Posts: 184
|
Yes, all the 3 tables are under the same DSN.
The DSN is set on the Compute node.
The insert statement is like:
INSERT INTO Database.XXX (column1, column2 ...) values(value1, value2 ...)
XXX is table name.
BTW, any different for the DSN setting on the Compute node or insert statement?
I think they are identical. correct? |
|
| Back to top |
|
|
| martinb |
| Posted: Wed Aug 06, 2014 10:17 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
| lium wrote: |
All database operations within the same Compute node will be coordinated under any circumstances.
Are you saying this is false statement? |
Yes that is my understanding, and it fits both your observations, and the provided extracts from the documentation, since you have wired in the input node catch terminal and not re thrown an exception. |
|
| Back to top |
|
|
| lium |
| Posted: Wed Aug 06, 2014 11:20 am Post subject: |
|
|
Disciple
Joined: 17 Jul 2002
Posts: 184
|
Thanks for the reply.
I am going to develop test message flow to verify this. I can post whatever I get , for example, the user trace etc. |
|
| Back to top |
|
|
| lium |
| Posted: Thu Aug 07, 2014 6:12 am Post subject: |
|
|
Disciple
Joined: 17 Jul 2002
Posts: 184
|
I was told on the production, they are not coordinated, since the table 1, 2 are much more than table 3.
However, they did some test on DEV, they were rolled back.
This really confused me.
From database perspective, it starts unit of work on Time 0(T0), and do insert on T1 to table 1, insert on T2 to table 2, and failure on inesrt on T3 to Table 3.
From broker perspective, the broker captures the error on T3, and it does the error handling, and at the end of error handling which is T4,
(1) will it still issue COMMIT?
(2) if yes, what database is going to do?
Thanks |
|
| Back to top |
|
|
| lium |
| Posted: Wed Aug 13, 2014 7:50 pm Post subject: |
|
|
Disciple
Joined: 17 Jul 2002
Posts: 184
|
I did a lot of test, and I also turned on the ODBC tracing for detail information.
I confirm the answer to my question is:
(1) Yes, the broker will commit if you have your own catch flow unless a new exception is thrown beyond the input node, which results the message flow to be rolled back. In this situation, SQL_ROLLBACK will be issued.
(2) if SQL_COMMIT is issued, based on the test, the database will commit the insert on T1 to table 1, and insert on T2 to table 2, the insert on T3 to Table 3 will fail and roll back. However, if there is SQL_ROLLBACK on T4, then all operations insert on T1 to table 1, inesrt on T2 to table 2, insert on T3 to table 3 will be all rolled back.
Also, let us recall the comment by martinb which is CORRECT:
| Quote: |
If you connect the Catch terminal of the input node, you are indicating that the flow handles all the exceptions that are generated anywhere in the out flow. The broker performs no rollback, and takes no action, unless there is an exception on the catch flow. If you want any rollback action after an exception has been raised and caught, you must provide this in the catch flow.
|
|
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
