Read/write data on the same transaction but different connections

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Read/write data on the same transaction but different connections

tevenin
This post was updated on .
Hi all,
 I am pretty new to BitronixTM and XA but i was wondering about the situation in which,
inside the same XA transaction, one connection write some data and an other one tries to read them.
Look at this simple code fragment:


  UserTransaction utx = TransactionManagerServices.getTransactionManager();
  utx.begin();
       
  DataSource ds = ... ; // Get an istance of  bitronix.tm.resource.jdbc.PoolingDataSource here
 
  Connection connection = ds.getConnection();
  Connection connection2 = ds.getConnection();
 
  PreparedStatement stmt1 = null, stmt2 = null;
  ResultSet rs = null;
  try {
      stmt1 = connection.prepareStatement("INSERT INTO TEST_TABLE (ID, DESCR) VALUES (?, ?)");
      stmt1.setInt(1, 1);
      stmt1.setString(2, "test");
      stmt1.execute();
     
      stmt2 = connection2.prepareStatement("SELECT * FROM TEST_TABLE WHERE ID=?");
      stmt2.setInt(1, 1);
      rs = stmt2.executeQuery();
      while (rs.next()) {
          System.out.println(rs.getInt(1) + " -- " + rs.getString(2));
      }
  } catch (Exception e) {
      System.out.println(e);
  } finally {
      rs.close();
      stmt1.close();
      stmt2.close();
      connection.close();
      connection2.close();
  }
 
  utx.commit();


If i run this code using PostgreSQL as dbms, the ResultSet rs is empty, that is the second connection doesn't see data written by the first one.
If i run the same code obtaining both statements from only one connection all works fine.
While I understand this behavior outside global transaction, in this case i am a bit surprised.
Please remark that the second statement, running the whole example on Firebird and Ms Sql Server (after enabling read committed isolation using row versioning), successfully retrieves data written through the first connection.

Any suggestions are welcome

Thanks
Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

Wim  Goossens
Yes, it would be nice if connection2 would have been the same physical connection as the first one, in fact that all connections returned from the same datasource AFTER utx.begin are the same physical connection to the database.



tevenin wrote
Hi all,
 I am pretty new to BitronixTM and XA but i was wondering about the situation in which,
inside the same XA transaction, one connection write some data and an other one tries to read them.
Look at this simple code fragment:


  UserTransaction utx = TransactionManagerServices.getTransactionManager();
  utx.begin();
       
  DataSource ds = ... ; // Get the datasource here
 
  Connection connection = ds.getConnection();
  Connection connection2 = ds.getConnection();
 
  PreparedStatement stmt1 = null, stmt2 = null;
  ResultSet rs = null;
  try {
      stmt1 = connection.prepareStatement("INSERT INTO TEST_TABLE (ID, DESCR) VALUES (?, ?)");
      stmt1.setInt(1, 1);
      stmt1.setString(2, "test");
      stmt1.execute();
     
      stmt2 = connection2.prepareStatement("SELECT * FROM TEST_TABLE WHERE ID=?");
      stmt2.setInt(1, 1);
      rs = stmt2.executeQuery();
      while (rs.next()) {
          System.out.println(rs.getInt(1) + " -- " + rs.getString(2));
      }
  } catch (Exception e) {
      System.out.println(e);
  } finally {
      rs.close();
      stmt1.close();
      stmt2.close();
      connection.close();
      connection2.close();
  }
 
  utx.commit();


If i run this code using PostgreSQL as dbms, the ResultSet rs is empty, that is the second connection doesn't see data written by the first one.
If i run the same code deriving both statements from only one connection all works fine.
While I understand this behavior outside global transaction, in this case i am a bit surprised.
To notice that the same example works well on Firebird and Ms Sql Server (after enabling read committed isolation using row versioning).

Any suggestions are welcome

Thanks
Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

Ludovic Orban
Administrator
This has nothing to do with XA / JTA but with plain isolation (the I in ACID) logic.

Unless you lower the isolation level down to READ_UNCOMMITTED and your database supports it, you won't be able to see data created in one connection but not yet committed in another connection.

Getting twice the same physical connection when you ask the connection pool to get two would be utterly wrong. Remember: the connection pool must provide the exact same behavior as if you really opened connections to the database when you call ds.getConnection(), you should only get better performance.

Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

tevenin
Ludovic Orban wrote
Unless you lower the isolation level down to READ_UNCOMMITTED and your database supports it, you won't be able to see data created in one connection but not yet committed in another connection.
Thanks ludovic for your quick answer, but there's still something that i would like to clarify
Isolation level is about interaction between multiple transactions. In the above example, if we would not have an XA/JTA context, we have two local (implicit) transaction, one for each connection, and what you have said is absolutely correct. But inside a global transaction scope with two connections to the same datasource, this isolation between local transactions is much less logic; it is meaningful that both Firebird and MsSql, with transaction level set to READ_COMMITTED work as i would expect, "sharing" data between the two connections. Probably PostgreSQL MVCC is more restrictive about that.

I'm not sure but maybe WebLogic support for "per-transaction isolation level" is an implicit answer to my doubts.

Thanks again.
bye




Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

Ludovic Orban
Administrator
OK, now I see what you mean.

Well, it looks like there is a weakness in Postgresql's XA implementation as I agree that two connections working on the same transaction should see the same uncommitted data.

I'm afraid there is little BTM can do about this or can it ?
Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

youngm
I thought this was a basic concept of a JTA provider.  I believe the way other JTA providers provide this functionality is the Provider's pool implementation returns the same connection for multiple requests to ds.getConnection() within the same transaction.  I haven't worked with JTA+XA much but in a nonXA environment I think this is the only way for multiple calls to ds.getConnection() to participate in the same transaction.  It would seem quite a pain in the nonTX use case to force the user to pass the result of a single ds.getConnection() to all code in a transaction.  Thoughts?

Mike

Ludovic Orban wrote
OK, now I see what you mean.

Well, it looks like there is a weakness in Postgresql's XA implementation as I agree that two connections working on the same transaction should see the same uncommitted data.

I'm afraid there is little BTM can do about this or can it ?
Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

Ludovic Orban
Administrator
I don't really like the idea but I can understand your reasoning and this can be done without too much pain.

If you register a JIRA issue asking for this improvement I'll implement it for the next version. The only drawback is that this feature will be configurable and disabled by default.

Does that sound good to you ?

Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

Wim  Goossens
Sounds very good !  
Ludovic Orban wrote
I don't really like the idea but I can understand your reasoning and this can be done without too much pain.

If you register a JIRA issue asking for this improvement I'll implement it for the next version. The only drawback is that this feature will be configurable and disabled by default.

Does that sound good to you ?

Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

youngm
http://jira.codehaus.org/browse/BTM-35

Wim Goossens wrote
Sounds very good !  
Ludovic Orban wrote
I don't really like the idea but I can understand your reasoning and this can be done without too much pain.

If you register a JIRA issue asking for this improvement I'll implement it for the next version. The only drawback is that this feature will be configurable and disabled by default.

Does that sound good to you ?

Ludovic
Reply | Threaded
Open this post in threaded view
|

Re: Read/write data on the same transaction but different connections

Ludovic Orban
Administrator
I've implemented a prototype of this feature with some limitations. Please check the JIRA issue for more details and post your comments here.

Thanks.