GeeCON Prague 2014*
The moose likes EJB Certification (SCBCD/OCPJBCD) and the fly likes transaction when multiple database connections are open Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Certification » EJB Certification (SCBCD/OCPJBCD)
Bookmark "transaction when multiple database connections are open" Watch "transaction when multiple database connections are open" New topic
Author

transaction when multiple database connections are open

Miki Muzsi
Ranch Hand

Joined: Jun 23, 2003
Posts: 120
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<br /> <br />SCJP 1.4, SCBCD 1.3
Miki Muzsi
Ranch Hand

Joined: Jun 23, 2003
Posts: 120
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
Aravind Kumar
Ranch Hand

Joined: Nov 03, 2003
Posts: 39
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
sawan parihar
Ranch Hand

Joined: Aug 24, 2004
Posts: 250
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


Sawan<br />SCJP,SCWCD,SCBCD<br /> <br />Every exit is an entry somewhere.
Miki Muzsi
Ranch Hand

Joined: Jun 23, 2003
Posts: 120
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
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
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.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
sawan parihar
Ranch Hand

Joined: Aug 24, 2004
Posts: 250
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

Joined: Sep 29, 2002
Posts: 1683
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

Joined: Aug 24, 2004
Posts: 250
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

Joined: Aug 24, 2004
Posts: 250
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

Joined: Sep 29, 2002
Posts: 1683
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

Joined: Aug 24, 2004
Posts: 250
Yes now it is clear.

Thanks for that.
 
GeeCON Prague 2014
 
subject: transaction when multiple database connections are open