• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

transaction when multiple database connections are open

 
Ranch Hand
Posts: 120
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This might be a dumb questiuon but I am a bit confused at this our regarding transactions in general...

Considering the code:
...
Context initContext = new InitialContext();

DataSource ds1 = (DataSource)initContext.lookup("java:comp/env/db1");
Connection con1 = ds1.getConnection();
DataSource ds2 = (DataSource)initContext.lookup("java:comp/env/db2");
Connection con2 = ds2.getConnection();

UserTransaction tx = ejbContext.getUserTransaction();
tx.begin();
//use the connection to update database 1.
//use the connection to update database 2.
tx.commit();
con1.close();
con2.close();
...


I was wondering, when the user starts a transaction ( tx.begin() , both databases are locked? So the transaction is associated with two databases? What if one is oracle the other is sqlserver? Basically both databses must successfully commit, which I think is just no way to control that...

Miki
 
Miki Muzsi
Ranch Hand
Posts: 120
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Apparanttly I found the answer to my own question.

Transactions can indeed be defined so that the operation is ACID across multiple databases. It is about tw-phase commit, see spec pg.333-334 chapter 17.2.1 Update of multiple databases.

Miki
 
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Miki Muzsi:
This might be a dumb questiuon but I am a bit confused at this our regarding transactions in general...

Considering the code:
...
Context initContext = new InitialContext();

DataSource ds1 = (DataSource)initContext.lookup("java:comp/env/db1");
Connection con1 = ds1.getConnection();
DataSource ds2 = (DataSource)initContext.lookup("java:comp/env/db2");
Connection con2 = ds2.getConnection();

UserTransaction tx = ejbContext.getUserTransaction();
tx.begin();
//use the connection to update database 1.
//use the connection to update database 2.
tx.commit();
con1.close();
con2.close();
...


I was wondering, when the user starts a transaction ( tx.begin() , both databases are locked? So the transaction is associated with two databases? What if one is oracle the other is sqlserver? Basically both databses must successfully commit, which I think is just no way to control that...

Miki



database as a whole is never locked. If 2 resources are invloved, it's not needed to be in 2PC protocol. It can be in 2 phase commit when you have distributed-transaction-enabled transaction manager. In non-2PC situation, i think each operation would involve in normal transaction. rollback of one would cause rollback of other too. If I am wrong, correct me.

Aravind
 
Ranch Hand
Posts: 250
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

In non-2PC situation, i think each operation would involve in normal transaction. rollback of one would cause rollback of other too.



If its in 2 phase commit (like using XA driver etc )then only the rollback of one will cause the rollback of other. Just providing more information about how to do it:

In your server you can define two connection pool for two different database and create the datasourcse for the two connection pools. Each data source should have the two phase commit enabled. eg: in weblogic you can define the datasource like :

<JDBCTxDataSource EnableTwoPhaseCommit="true"
JNDIName="MYTXPOOL" Name="MyJDBC Tx Data Source"
PoolName="MYPOOL" Targets="server"/>


After this in one transaction you can use these two datasource to get the connections that will participate in 2-phase commit. Although I think that this won't work with JTA.


Sawan
 
Miki Muzsi
Ranch Hand
Posts: 120
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

In non-2PC situation, i think each operation would involve in normal transaction. rollback of one would cause rollback of other too. If I am wrong, correct me.



Do you mean that even without 2PC you can have this working? What happens than if you commit one of the connections, and the other one you must roll back. You can not rollback anymore the first commit. So I am not sure how this works what you say:

rollback of one would cause rollback of other too



Miki
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

After this in one transaction you can use these two datasource to get the connections that will participate in 2-phase commit. Although I think that this won't work with JTA.


All EJB container managed transactions are done using JTA under the covers.
 
sawan parihar
Ranch Hand
Posts: 250
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes Roger you are right but a couple of days back I read following two paragraphs in bea docs and thats why I am a little confused.

You can start a JTA user transaction after a transacted session has been started; however, the JTA transaction will be ignored by the session and vice versa.

WebLogic Server supports the two-phase commit protocol (2PC), enabling an application to coordinate a single JTA transaction across two or more resource managers. It guarantees data integrity by ensuring that transactional updates are committed in all of the participating resource managers, or are fully rolled back out of all the resource managers, reverting to the state prior to the start of the transaction.



In the first paragraph it says that the transaction will be ignored and in the second peragraph it says that it supports 2 PC (JTA transaction.). I think I need to do some research on this to fully understand it. Hopefully should get some time tommorow to code this thing.

Thanks.

Sawan
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You are referring to a JMS transacted session which supports transactions that are located within the session. A JMS transaction is for messages sent or received to be treated as an atomic unit. So, rolling back a JMS transacted session will roll back all sends and receives on that session, but will not roll back any database updates.

If you need JMS and EJB operations to be done in a transaction, you need a JTA transaction in a non-transacted JMS session.
 
sawan parihar
Ranch Hand
Posts: 250
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hmmm. Not very sure that I understand all of that. But it makes sense perhaps I need to spend some time on this.

If you need JMS and EJB operations to be done in a transaction, you need a JTA transaction in a non-transacted JMS session.



Can you elaborate this.I don't remember but I read somewhere that with JTA you can bring JMS and EJB opertaions in one transaction. Yesterday only I found that in weblogic you can configure it.

Thanks
[ June 25, 2005: Message edited by: sawan parihar ]
 
sawan parihar
Ranch Hand
Posts: 250
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
quote:
--------------------------------------------------------------------------------
If you need JMS and EJB operations to be done in a transaction, you need a JTA transaction in a non-transacted JMS session.
--------------------------------------------------------------------------------



Can you elaborate this.I don't remember but I read somewhere that with JTA you can bring JMS and EJB opertaions in one transaction. Yesterday only I found that in weblogic you can configure it.

Thanks
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Let's say you must have a transaction for both a JMS message and a database update which is triggered by the receipt of the message. So, it must all succeed or fail. What you need to do is have a JTA transaction for this.

First, you create a non-transacted session using either the createQueueSession() or createTopicSession() method (for a Queue or Topic respectively), passing in false to the first parameter (which is declared as "boolean transacted").

Second, you start the JTA transaction. This can be done explicitly by invoking the UserTransaction.begin() method or implicitly by using a CMT message-driven bean. This bean must have the transaction attribute of Required for its onMessage() method. This will allow the receipt of a JMS message that triggers a call to an onMessage() method to be included in the scope of a transaction. Note that for a BMT message-driven bean, the JMS message receipt is always outside the scope of the bean's transaction.

After the operations have completed, eg a database update is done, the transaction will either commit or rollback. A commit means that sent messages are made visible, received messages are removed from the messaging system and database updates are saved. A rollback means that sent messages are cancelled, received messages are returned to the messaging system and database updates are not saved.
 
sawan parihar
Ranch Hand
Posts: 250
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes now it is clear.

Thanks for that.
 
Are you okay? You look a little big. Maybe this tiny ad will help:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic