| Author |
Message
|
| 5koff |
 Posted: Tue Sep 03, 2013 6:42 am Post subject: JDBC Conf service XA support & GDM |
 |
|
Newbie
Joined: 23 Jun 2013
Posts: 8
|
Hi guys,
I am facing the following issue:
- Configurable service: JDBCProvider, pointing to Oracle DB
- message flow: SOAP Input -> GDM (Update row "X" in table "A") -> GDM (Update row "X" in table "A") -> SOAP Reply
If JDBCProvider property jdbcProviderXASupport='true', then both DB accesses from the two GDM-s are executed in one DB session.
If JDBCProvider property jdbcProviderXASupport='false', then each DB access from the two GDM-s is executed in separate DB session. The first DB access acquires exclusive row lock on row "X" (locktype 3) and waits the flow to finish to commit DB changes. The second DB access waits for the lock to be released. So i have 2 session in deadlock.
Why is the XA capability of the JDBC Provider affecting the behavior of making DB changes in one/separate DB sessions. As far as i read in infocenter, for each instance of a message flow is associated an auxiliary DB transaction (assuming 1 session) and all DB changes are executed in one session.
Transactionality is "automatic" .
WMB 8.0.0.2
Thanks in advance,
Dimitar |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Sep 03, 2013 7:34 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Sep 03, 2013 7:46 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
but the original poster is asking about two accesses to the same db within the same instance of the same flow.
I wonder if the behavior is defined more by the transactional properties of the flow and the relevant nodes, than by the XA properties of the connection.
I wonder if there's an easy way to tell one node to use one connection, and another node to use another connection. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Sep 03, 2013 9:00 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
It's unclear to me what the OP means by
| Quote: |
| for each instance of a message flow is associated an auxiliary DB transaction (assuming 1 session) |
Is the OP asserting that an auxiliary DB transaction gets created by the two nodes ?
@mqjeff - Great pointers. The OP should be able to take it from here without any Google search service posters' assistance. [Tibor, et al].
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| 5koff |
| Posted: Wed Sep 04, 2013 2:05 am Post subject: |
|
|
Newbie
Joined: 23 Jun 2013
Posts: 8
|
Thanks for your relies,
In fact, I am not redeploying the bar file, just altering the JDBCProvider properties. The transactionality of the GDM-s are set to automatic (the only choice).
As far as I know, the XA has nothing to do with how many sessions one flow uses.
Am I wrong, or? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Sep 04, 2013 5:39 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| 5koff wrote: |
Thanks for your relies,
In fact, I am not redeploying the bar file, just altering the JDBCProvider properties. The transactionality of the GDM-s are set to automatic (the only choice).
As far as I know, the XA has nothing to do with how many sessions one flow uses.
Am I wrong, or? |
AFAIK the number of connections by flow depends on the number of different access types. If you have a node participating in the transaction and a node that is set to not participate in the transaction, that would make for at least 2 DB Sessions (one transacted, one not transacted), Procedure calls need another DB Session...
Best way to check, is to verify how your flow handles the transaction and the DB calls and have the DB report what is going on with a single instance of the flow. Up to you thereafter to make the changes needed to your flow.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| 5koff |
| Posted: Wed Sep 04, 2013 11:07 pm Post subject: |
|
|
Newbie
Joined: 23 Jun 2013
Posts: 8
|
Done that.
I've mentioned, that the transactionality of the GDM-s is set to automatic. It takes the property of the flow. The "global transaction" property in the BAR file is set to false. So the flow, as per documentation, should be in a broker transaction - one flow instance - one transaction. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Sep 05, 2013 11:34 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| 5koff wrote: |
Done that.
I've mentioned, that the transactionality of the GDM-s is set to automatic. It takes the property of the flow. The "global transaction" property in the BAR file is set to false. So the flow, as per documentation, should be in a broker transaction - one flow instance - one transaction. |
This is not the way it works with DB access. Direct access vs DB procedure access, even though in the same flow, requires 2 different connections and as such may lead to locking problems.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| 5koff |
| Posted: Sun Sep 08, 2013 11:35 pm Post subject: |
|
|
Newbie
Joined: 23 Jun 2013
Posts: 8
|
10x for the reply,
in my flow I am not invoking any DB procedures/UDF-s. The only DB access is via graphical maps. In the DB teh GDM-s execute plain SQL queries with bind variables.
The thing that bothers me is that the same flow (no redeploy) behaves transactionally different, depending on the XA property of the JDBC Provider. I personally cannot relate why the broker executes and commits DB access (plain SQL) in different/same session(s) with the XA support of the provider. |
|
| Back to top |
|
|
|
|
