What to do if your database doesn't support XA transactions?

Bookmark Blog Post

	del.icio.us
	Digg
	DZone
	Furl
	Reddit

Dmitri Maximovich's Blog | May 24, 2005  12:23 PM | Comments (5)

Deepak Vohra wrote an article about configuring and using PostgreSQL from WebLogic Server. While it's certainly good introduction to get you started on using PosgreSql it fails to describe significant limitations that this database have when it comes to Enterprise-level development. Don't get me wrong, I personally think that PosgreSql is great database and is excellent free alternative even to leading commercial RDBMS to certain kind of projects. I personally used PostgreSql for some of my projects in the past and it's certainly in the first line of databases I would recommend to consider when you choose database for your next project. It's easy to install and manage, reliable, have good feature set and standards compliance and even has sequences support which makes it conveniently compatible with Oracle when it comes to PK generation strategies and so on.

But I also believe that developers should understand limitations of tools they're using, probably even better than benefits that these tools provides. Being excellent database overall PosgreSql lack support for distributed (XA) transactions, at least not in latest version 8. I'm not monitoring PosgreSql development closely so I'm not aware if adding XA support in the list for any future release (if anyone is better informed please post reply here, would be interesting to know). As a matter of fact, I don't think that there is any free open-source database with support for XA currently available, is there? Would be glad to learn that I'm wrong.

The point is that you need to analyze your project's requirements to understand if XA support is important for your particular project or not. I personally found that it's quite important feature for most Enterprise projects I've been worked on. Pretty much any time when your application have to work with more than one Resource Manager at once (think for example about MDB updating database) it's good to know that you could use XA transaction to get highest level of Quality of Service . Some people would argue that most of applications doesn't need (or could manage without) XA, which could be true to certain extent but almost always adds at least to development efforts (detecting duplicates in above example) or otherwise leads to degradation of QoS. Sometimes you just have to have XA to deliver functionality required by your use case.

Anyway, back to the topic of this post - what if you're using database that doesn't have support for XA transactions but you need it for one reason or another. Besides from manual strategies for emulating or avoiding XA transactions there is a transaction optimization technique known as "Last resource commit" which could be found in most modern Transaction Manager implementations. The idea is that it's possible for a single resource that is not XA aware (e.g. can only commit or rollback with no support for prepare phase), to be enlisted in a transaction with any number of two-phase commit aware resources. Transaction Manager then threats non-XA resource slightly differently, in that it executes the prepare phase on all (XA) resources first, and if it then decide to commit the transaction it passes control to non-XA resource. If that resource commits, then Manager logs decision to commit and attempts to commit the other resources as well (second phase of commit protocol). If commit on non-XA resource fails for any reason, Transaction Manager executes rollback to previously prepared XA resources. As you can imagine, there are failure scenarious where loss of atomicity is possible here but overall they should be pretty rare in practice.

WebLogic Server, starting from version 9, supports Last Resource Commit optimization (BEA called it Logging Last Resource Transaction Option or LLR). It's worth to add that besides from allowing you to enlist non-XA resource in XA transaction this feature could be viable performance-optimization technique as well, because XA drivers are generally less efficient compared to non-XA drivers and XA protocol itself adds some overhead in terms of network traffic and disk IO.

LLR feature in WebLogic 9 should not be confused with "Emulate Two-Phase Commit", which was available as an option in JDBC Connection Pools level in previous versions. There are subtle but significant differences between them. According to BEA's documentation for WebLogic 8:

When the Emulate Two-Phase Commit for non-XA Driver option is selected (EnableTwoPhaseCommit is set to true), the non-XA JDBC resource always returns XA_OK during the XAResource.prepare() method call. The resource attempts to commit or roll back its local transaction in response to subsequent XAResource.commit() or XAResource.rollback() calls. If the resource commit or rollback fails, a heuristic error results. Application data may be left in an inconsistent state as a result of a heuristic failure.

As you see the behaviour is completely different and should produce more heuristic outcomes when used.

There are many different approaches developer can use to deal with distributed transactions and it's good to have a choice as always. It pays to know your database and Transaction Manager features. I'd also recommend excellent book "Java Transaction Processing : Design and Implementation" by Mark Little et al. if you need to get a good background on Transaction processing theory and practice including JTA.

Comments

• Some discussion on the topic can be found here

  Posted by: maximdim on May 27, 2005 at 10:35 AM

• From the write-up:

  "WebLogic Server, starting from version 9, supports Last Resource Commit optimization (BEA called it Logging Last Resource Transaction Option or LLR)."

  Previous versions of Weblogic have last resource commit optimization support as an option for "resource adapter" applications (also known as Connectors).

  WebLogic's unique new 9.0 JDBC Logging Last Resource (LLR) feature is fundamentally different and fundamentally safer than standard last resource commit (LRC).

  In theory LLR, is as safe as full XA. This is because for LLR two-phase transactions, unlike LRC, the transaction manager's two-phase commit record (TLOG record) is stored in the database in the same local transaction as the application's JDBC work. This eliminates the heuristic hazard in standard LRC that occurs when a server crashes just after the last resource commits but before the TLOG commit record is written - causing other participating resources to eventually roll back their work.

  Tom Barnes, BEA

  Posted by: barnes on June 24, 2005 at 9:03 AM

• Tom,

  Thanks for comments. Is there any BEA documentation available which describes this behaviour in more details?

  From your description it's unclear, at least for me, where this "TLOG record" is being saved in the database - is there are special table for this or how it works? I assume DB doesn't know about transaction coordinator and partisipation in XA transaction. Could you elaborate? Thanks.

  Posted by: maximdim on June 24, 2005 at 10:16 AM

• Dimitri,

  I think the LLR essentials are already in the WebLogic 9.0 beta documentation for JDBC, but more detailed documentation is scheduled to be updated soon (perhaps to coincide with the upcoming full release of WebLogic 9.0).

  The key point is that LLR is fully "ACID", unlike standard last-participant-optimizations (aka last-agent-optimizations) that have long been available from BEA and other application server vendors.

  Under JDBC LLR, all of an application's SQL work in a particular JTA transaction is transparently routed to a single physical JDBC connection which, in turn, maintains a single JDBC local transaction. A standard application JDBC call to "getConnection()" under a transaction implicitly reserves this connection (and its local transaction) for the duration of the JTA transaction, putting it under the control of the transaction manager (TM). Subsequent application calls to "getConnection()" under the same transaction implicitly create a virtual connection that routes back to the reserved connection. If the JTA transaction becomes two-phase, the TM uses this connection and local transaction to store a 2PC transaction record to the database during the JTA "commit" instead of storing the record to a file based transaction log as would normally be the case (a JTA LLR transaction becomes two-phase if non-LLR resources also participate). The following steps illustrate the LLR algorithm as it runs the commit of a 2PC transaction:

• 1 TM calls prepare on all participating non-LLR resources (such as JMS servers), and rolls back all LLR and non-LLR resource work if any such resource votes "no"
• 2 TM writes a 2PC record to an "LLR table" in the database using the transaction's reserved JDBC connection and local transaction. There is one "LLR table" created per WebLogic server. (if this step fails, TM rolls back all work)
• 3 TM commits the JDBC connection using JDBC local commit and releases (closes) the JDBC connection, this commits both the 2PC record insert as well as the application's SQL work in a single local transaction (if this fails, the TM tests to the DB to see if the 2PC record exists, if the 2PC record is not found then the database work must have rolled back and the TM rolls back the non-LLR resources, otherwise, the database work must have commited, so the TM continues and commits the remaining resources)
• 4 Provided 3 completes OK, TM commits non-LLR resources
• 5 Once 4 completes, TM eventually deletes the 2PC record

  If the application server crashes at any point during the transaction, the presence or absence of the 2PC record reliably determines the outcome of the transaction, and is used to drive the transaction to completion. During crash recovery, the absence of the 2PC record indicates that the transaction must rollback all participating resources, while its presence indicates that the transaction must commit all participating resources.

Posted by: barnes on July 18, 2005 at 11:28 PM

• LLR documentation available here

  Posted by: maximdim on August 10, 2005 at 11:09 AM