| Author |
Message
|
| sunny_30 |
 Posted: Mon Sep 24, 2012 9:03 am Post subject: Rollback on DB-exception |
 |
|
Master
Joined: 03 Oct 2005
Posts: 258
|
Setup: MB7 Flow with http-Input node. Compute node has Throw-exception on DB- error property selected.
Flow does MQ output to queue. There is a database-exception inside compute node of the flow. Flow set to 1-phase commit.
2 questions related to this setup:
1) HTTP-Input does not have a property for Transaction. For this reason, Im assuming all processing inside a HTTPInput based flow is always non-transactional, for both MQ & DB,unless the MQ-output, compute nodes' transaction properties are explicitly set to "Yes" instead of "Automatic".
Will the message written to MQ-queue ever be rolled back if the MQ-output node transaction is set to Automatic & there was a DB-exception inside compute-node of the Http-input based message-flow ?
2) Compute-node failure terminal is connected. But there is no error (or) explicit throw in the failure terminal's processing. There is a DB-exception. Transaction on compute node set to "Yes". Will the DB-operation rollback if there is no failure (throw) in failure-terminal processing of the compute node ?
Please let me know
Thanks in advance |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Mon Sep 24, 2012 9:49 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
For best results, IMHO, you may like to code your exception handler to compensate for db operations rather than rely on WMB's transactional capability to rollback database operations. I find this to be true regardless of the transport (MQ, HTTP, SOAP). This implies you keep track of successful database operations in memory, like the Environment tree.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| sunny_30 |
| Posted: Mon Sep 24, 2012 12:56 pm Post subject: |
|
|
Master
Joined: 03 Oct 2005
Posts: 258
|
Sorry. Mistake in my question. I overlooked a simple thing:
Compute node only has either 'Automatic' or "commit'. No "yes" option as with MQoutput nodes
Reframing my 2-questions again:
1) HTTP-Input does not have a property for Transaction-mode. For this reason, Im assuming all MQ processing inside a HTTPInput based flow is always non-transactional, unless the MQ-output node transaction property is explicity set to "yes" instead of setting it to "automatic".
Will the message written to MQ-queue ever be rolled back if the MQ-output node transaction is set to Automatic & there was a DB-exception inside compute-node of the Http-input based message-flow ?
2) Compute-node failure terminal is connected. But there is no error (or) explicit throw in the failure terminal's processing. There is a DB-exception. Transaction on compute node set to "Automatic". Will the DB-operation rollback if there is no failure (throw) in failure-terminal processing of the compute node ? Are transactions even an option for compute nodes involving HTTP-Input based message -flows , even if flow generates a failure (by throw node etc)? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 25, 2012 4:53 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| sunny_30 wrote: |
Sorry. Mistake in my question. I overlooked a simple thing:
Compute node only has either 'Automatic' or "commit'. No "yes" option as with MQoutput nodes |
Still same answer. Code compensation in your error handler.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Sep 25, 2012 7:12 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| Im assuming all MQ processing inside a HTTPInput based flow is always non-transactional |
I think the confusion here is that "Automatic" for a transaction means different things depending on the node. For an MQOutput node it means "transactional" if the input node starting the flow is transactional, so for HTTP it will be non-transactional as the HTTPInput node is non-transactional. For a Compute node, "Automatic" for a transaction means that it will only be committed or rolled back when the flow finishes and which is chosen depends on how the flow ends. If the flow ends with an exception, it will rollback, if not, it will commit. This end of flow commit or rollback will affect all transactional resources accessed by the flow. For example, an MQOutput node set to a transaction mode of "Yes" will also be included in this. So, each transactional resource accessed within the flow can be viewed as a single phase transaction (in the non XA case). However, you can specify "Coordinated Transaction" on the whole flow if you want to enable XA.
So with reference to your questions, in this case a message put to a queue by an MQOutput node set to automatic not would be rolled back if the flow ended with an exception (but would be if it was set to yes). This leads into your question 2. In the case of a Compute node a DB error will cause an exception, but if you choose to swallow the exception down a catch or failure terminal then the DB action would be comitted if the flow did not end with an exception.
I hope this helps a little.
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| sunny_30 |
| Posted: Tue Sep 25, 2012 8:09 am Post subject: |
|
|
Master
Joined: 03 Oct 2005
Posts: 258
|
To sum it up:
1) As long as all "failure", "catch" paths in the flow are ending with an exception (throw), the rollback of DB operation occurs, IF compute node transaction-mode property is set to "automatic". Doesnt matter if the input is MQ / http.
2) for MQ-output nodes, the MQ rollback occurs if transaction property is set to "automatic" in case of MQ-input message & it shd be set to "yes" in case of http-Input message. For non-persistent MQ messages, MQinput must specify yes instead of automatic
please correct me if Im wrong. Otherwise, I think Im really good !
Lancelotlinc,
Thk U for responding. Will sure consider your advice (along with a PMR to IBM) if default Broker transactional behavior/ settings wont rollback what we are expecting it to do.
Last edited by sunny_30 on Tue Jul 16, 2019 7:43 am; edited 1 time in total |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 25, 2012 8:20 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| sunny_30 wrote: |
Lancelotlinc,
Thk U for responding. Will sure consider your advice (along with a PMR to IBM) if default Broker transactional behavior/ settings wont rollback what we are expecting it to do.
Thank you both - again |
Your welcome. I'm not sure that your expectations are reasonable with regard to Broker rolling back database transactions. Let's say you have a message flow. A compute node in your flow has ESQL code that inserts a row into a database. Later in the flow, some error happens and the WMB transaction is rolled back. WMB has no practical way of automatically reversing that row insert unless you specifically code it in your error handler.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 25, 2012 8:42 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| sunny_30 wrote: |
2) for MQ-output nodes, the MQ rollback occurs if transaction property is set to "automatic" in case of MQ-input message & it shd be set to "yes" in case of http-Input message. For non-persistent MQ messages, MQinput must specify yes instead of automatic
please correct me if Im wrong. Otherwise, I think Im really good ! |
This may be true for Fire-and-Forget patterns. For Request-Reply patterns, you must make sure your MQOutput node is not within the same transaction boundary as your MQGet node. If they are, your transaction will timeout because the MQOutput message will never be committed and your MQGet node will never see the reply.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| sunny_30 |
| Posted: Tue Sep 25, 2012 9:15 am Post subject: |
|
|
Master
Joined: 03 Oct 2005
Posts: 258
|
| lancelotlinc wrote: |
WMB has no practical way of automatically reversing that row insert unless you specifically code it in your error handler. |
is this a limitation if its 1PC or even if XA (2PC) is implemented ?
yes- thats what I expect out of a rollback & I expect DB to internally take care of the reversal process... (isnt tht what a DB-rollback automatically does ?) I agree 1PCs arent 100% guaranteed |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 25, 2012 9:57 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| sunny_30 |
| Posted: Tue Sep 25, 2012 10:30 am Post subject: |
|
|
Master
Joined: 03 Oct 2005
Posts: 258
|
AFAIK- the major difference between partial (1PC) & global (2PC-XA) type coordinations is that the latter assures 100% transactions (using external Transaction Manager, in case of broker, it will be MQ) eg: even after a system failure the rollback-changes take place to the external system after restart.
But I dont think it means that the Db-insertions (eg) cannot be rolled back, in a partial 1PC setup. As long as Broker maintains the UOW with external DB system, I expect DB-rollbacks to occur (agree not 100% guaranteed) as part of WMB-UOW.
Please correct me if Im wrong |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 25, 2012 10:37 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Tick Coordinated Transaction (by default, this property is not ticked); the input node calls the external transaction manager WebSphere MQ for commit and rollback processing. Have you configured the WMQ XA manager? Only the following databases are supported for XA-aware transaction processing: IBM DB2, Oracle 9i, Release 2 with Patch Set 4 (V9.2.0.5), or Oracle 10g, Release 1.
What database + db version are you using? What WMB version are you using? What WMQ version are you using? What XA manager are you using?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 25, 2012 10:48 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| sunny_30 wrote: |
But I dont think it means that the Db-insertions (eg) cannot be rolled back, in a partial 1PC setup. As long as Broker maintains the UOW with external DB system, I expect DB-rollbacks to occur (agree not 100% guaranteed) as part of WMB-UOW.
Please correct me if Im wrong |
You'll have to try out your theory. I think you will find that your database INSERT will not rollback once execution has moved past the Compute node that the INSERT took place.
You might experiment with your flow configuration. mqjeff may be more helpful here than I have been. I would expect the database INSERT to not roll back unless you have enabled XA-aware processing.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| sunny_30 |
| Posted: Tue Sep 25, 2012 11:27 am Post subject: |
|
|
Master
Joined: 03 Oct 2005
Posts: 258
|
Yes- no XA setup at my place
I indicated this in my initial post, mentioned we have 1PC (phase-commit)setup.
we are running SQL-Server v2005
| lancelotlinc wrote: |
| You'll have to try out your theory. I think you will find that your database INSERT will not rollback once execution has moved past the Compute node that the INSERT took place. |
As long as the INSERT is not committed, & the flow is part of UOW, i expect the DB-operations to rollback on flow-error, even if no XA has been configured. Lets wait for some1 to confirm
Last edited by sunny_30 on Tue Jul 16, 2019 7:42 am; edited 1 time in total |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 25, 2012 11:29 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| sunny_30 wrote: |
| lancelotlinc wrote: |
| You'll have to try out your theory. I think you will find that your database INSERT will not rollback once execution has moved past the Compute node that the INSERT took place. |
As long as the INSERT is not committed, & the flow is part of UOW, i expect the DB-operations to rollback on flow-error, even if no XA has been configured. Lets wait for some1 to confirm |
Sounds like we need a wager. I'd like a Bud light lime if my theory is validated on your system.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
|
|
