Skip site navigation (1) Skip section navigation (2)

Re: XA support

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: XA support
Date: 2005-06-29 20:27:32
Message-ID: 84DC4D65-95BD-4CB5-AD5E-6C1E80EF6E69@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
I see option A below as the lesser of three evils. Is it reasonable  
to believe that people using XA would
know enough to allocate enough connections ?

Optionally we could have two implementations and choose between A and B

Dave
On 29-Jun-05, at 3:49 PM, Heikki Linnakangas wrote:

> Now that we have support for two-phase commit in the backend, it's  
> time to add XA support to the JDBC driver.
>
> The first issue we have to solve is the way we associate  
> transactions with connections.
>
> Consider the following example:
>
> public void testMultiplex() throws Exception {
>     Xid xid1 = new CustomXid(1);
>     Xid xid2 = new CustomXid(2);
>     Xid xid3 = new CustomXid(3);
>
>     xaRes.start(xid1, XAResource.TMNOFLAGS);
>     conn.createStatement().executeUpdate("UPDATE foobar1 SET foo =  
> 'aa'");
>     xaRes.end(xid1, XAResource.TMSUCCESS);
>
>     xaRes.start(xid2, XAResource.TMNOFLAGS);
>     conn.createStatement().executeUpdate("UPDATE foobar2 SET foo =  
> 'bb'");
>     xaRes.end(xid2, XAResource.TMSUCCESS);
>
>     xaRes.start(xid3, XAResource.TMNOFLAGS);
>     conn.createStatement().executeUpdate("UPDATE foobar3 SET foo =  
> 'cc'");
>     xaRes.end(xid3, XAResource.TMSUCCESS);
>
>     xaRes.commit(xid1, true);
>     xaRes.commit(xid2, true);
>     xaRes.commit(xid3, true);
> }
>
> According to the JTA spec, this should be supported. One connection  
> can be used to initiate a new transaction, without committing,  
> preparing or rolling back the previous one.
>
> We don't have backend support for this kind of operation. A  
> connection can be associated with only transaction at a time.  
> Changing that behaviour would require a major overhaul of the way  
> the backend handles transactions and connections, AFAICS.
>
> I can see three alternative ways to handle this in the driver:
>
> A. When the second transaction starts, a new physical connection is  
> opened behind the scenes. The second transaction is associated with  
> the new physical connection and the first transaction remains  
> associated with the original connection.
>
> The application server is usually responsible for connection  
> pooling, so opening new connnections behind the scenes will mess  
> with the pool limits and possible prepared statement cache. We  
> would have to do some kind of connection pooling ourselves,  
> regardless of the application server pool.
>
> B. When the second transaction starts, the first transaction is  
> prepared behind the scenes, freeing the connection for the new  
> transaction.
>
> This makes it impossible to implement the transaction suspend/ 
> resume functionality, since the first transaction cannot be used to  
> issue any more statements after the implicit prepare. It also  
> imposes the additional overhead of two-phase commit for  
> transactions that could otherwise use regular one-phase commit. It  
> will also cause trouble if the connection is broken after the  
> implicit prepare, because the transaction manager might not  
> recognize the implicitly prepared transaction, so it will stay in  
> the system holding locks etc. until it's manually rolled back by  
> the administrator.
>
> C. The second start call blocks, until the first transaction is  
> committed, prepared, or rolled back.
>
> This might cause tricky deadlocks, depending on the threading model  
> of the application server and the transaction manager.
>
> I experimented with some other DBMS' to find out how they handle this.
>
> Apache Derby (IBM Cloudscape):
> Supports XA only in embedded mode. Playing with temporary tables  
> lead me to think that it does something similar to A. Connection  
> management is probably quite lightweight in embedded mode. I didn't  
> bother to look at the source code, though.
>
> DB2 (Type 4 driver):
> Does B, and suffers from the problems described above. "LIST  
> INDOUBT TRANSACTIONS" shows the implicitly prepared transactions,  
> suspend/resume doesn't work as it should.
>
> Oracle:
> The server natively supports having many active transactions in one  
> connection.
>
> I tried to install Firebird and Sybase as well, but couldn't get  
> them installed properly. Anyone want to test them, or some other DBMS?
>
> Which approach should we take?
>
> - Heikki
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to  
> majordomo(at)postgresql(dot)org)
>
>


In response to

  • XA support at 2005-06-29 19:49:51 from Heikki Linnakangas

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2005-06-29 20:36:19
Subject: Re: XA support
Previous:From: Heikki LinnakangasDate: 2005-06-29 19:49:51
Subject: XA support

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group