PostgreSQL XA DataSource

[gleydson]

Hello,

I have two xa-datasouce using PostgreSQL 8.1 with JBoss 4.0.3. These two data-source are used by diferent systems under same VM. My problem is:

These systems must interop in many ways. EJB is used to do this task. For example: When EJB A insert one row in table TA and call EJB B that insert on row in table TB, in this moment, the data inserted by EJB A are hidden for EJB B.

It looks like that the two phase commit manage consistence in two datasource but the isolate the data among datasources. These datasources access the same database.

Anyone can help me?

[weston.price]

Postgres SQL does not support 'true' XA behavior. Certain aspects of JTA interaction with the TM (such as suspend/resume) are not available. Were you informed of something different? Could you post your ds-xml files?

[gleydson]

Sure, is here:

<xa-datasource>
<jndi-name>jdbc/SIPACDB</jndi-name>
<track-connection-by-tx>true</track-connection-by-tx>
<xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
<xa-datasource-property name="ServerName">sipac.info.ufrn.br</xa-datasource-property>
<xa-datasource-property name="PortNumber">5432</xa-datasource-property>
<xa-datasource-property name="DatabaseName">db</xa-datasource-property>
<xa-datasource-property name="User">user</xa-datasource-property>
<xa-datasource-property name="Password">pass</xa-datasource-property>
<min-pool-size>1</min-pool-size>
<max-pool-size>10</max-pool-size>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<idle-timeout-minutes>12</idle-timeout-minutes>
</xa-datasource>

[gleydson]

I'm using PostgreSQL JDBC 3 Driver with PostgreSQL 8.1. In PostgreSQL site says that supports XA. Is not full suport? I can't use this database for this Task?

Thanks.

[weston.price]

No, it's not full suppport. The driver doesn't support transaction interleaving, as well as the stop/resume portions of the protocol. The developer of the XA functionality remarks on this in a variety of areas in the driver.

Could you describe a bit more about the behavior you are seeing, as well as the transactional attributes of your EJB's?

I am not sure if I understand what exactly the problem is.

Thanks.

[gleydson]

Sure,

I have two enterprise applications (App1 and App2). All EJBs are Container Managed Transaction with transction attribute required.

App1 and App2 are connected with the same database across different datasources (DataSource1 and DataSource2). My problem is:

In the database I have two tables: TABLE1 and TABLE2. TABLE2 have a foreign key for TABLE1. My problem is:

When a EJB in App1 execute, it insert one row with TABLE 1. Inside EJB in App1 I call one EJB in App2, then EJB in App2 try to insert one row in TABLE2 with the foreign key id generated by App1.

I think that when I call EJB in App2 the transaction context is propagated. Is the same transaction because EJB in App2 are with transaction attribute required. But, looks like two database transactions, the data inserted by EJB in App1 are isolated to EJB in App2.

Then, the EJB in App2 throws an error: Foreign key constraint violation, because it can no see the data inserted by EJB 1 in the same XA Transaction but in different datasource.

Thanks.

[weston.price]

Since the data in Table1 effectively doesn't exist until the COMMIT, this is not going to work. Transaction isolation levels will prevent this regardless of whether or not they are in the same transactions.

Do you actually want to implement something like this, or is this just a test scenario for you?

Easiest solution would be the demarcate each EJB method with boundaries such as REQUIRES_NEW. Let the first transaction complete and then insert the second value.