Copy Page

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account. You can also Sign Up for a new account.

<h1>Databases XA support evaluation</h1>

<p>Those are the information collected while testing BTM against the most common databases. Also included:</p>

<ul>
	<li>An overall evaluation of the database's XA support</li>
	<li>Special features and/or limitations</li>
	<li>A configuration example</li>
</ul>

<table class="wysiwyg-macro" data-macro-name="info" data-macro-parameters="title=XA only" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2luZm86dGl0bGU9WEEgb25seX0&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="RICH_TEXT"><tr><td class="wysiwyg-macro-body"><p>Databases not providing an implementation of <code>javax.sql.XADataSource</code> have not been included.</p></td></tr></table>

<p>If you find mistakes, inconsistencies or simply want some other database to be tested (as long as there is at least a free trial version available) just let us know via the <a class="confluence-link" href="/display/BTM/Support" data-linked-resource-id="9240673" data-linked-resource-type="page" data-linked-resource-default-alias="Support" data-base-url="http://docs.codehaus.org">mailing list</a> and we'll consider your request.</p>

<h4>Contents</h4>
<img class="editor-inline-macro" src="/plugins/servlet/confluence/placeholder/macro?definition=e3RvYzptYXhMZXZlbD0yfG1pbkxldmVsPTJ9&amp;locale=en_GB&amp;version=2" data-macro-name="toc" data-macro-parameters="maxLevel=2|minLevel=2">

<h2>Apache Derby</h2>
<h3>Supported version(s)</h3>
<p>Tested against versions 10.3.1.4 and 10.2.2.0.</p>

<h3>Heuristics</h3>
<p>I could not find how to list in-doubt transactions nor how to manually terminate them.</p>

<h3>Example ResourceLoader configuration</h3>
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
resource.ds.className=org.apache.derby.jdbc.EmbeddedXADataSource
resource.ds.uniqueName=derby
resource.ds.maxPoolSize=5
resource.ds.driverProperties.user=users1
resource.ds.driverProperties.password=users1
resource.ds.driverProperties.databaseName=users1
</pre></td></tr></table>

<h3>Evaluation</h3>
<p>XA support in the driver and the DB look good. Documentation is too light.</p>

<h2>IBM DB2</h2>
<h3>Supported version(s)</h3>
<p>Tested against 9.1 Express-C.</p>

<h3>Settings</h3>
<p>All default settings are fine.</p>

<h3>Heuristics</h3>
<p>A single command allows listing in-doubt transactions, forcing their termination and forgetting them:</p>

<p><code>LIST INDOUBT TRANSACTIONS WITH PROMPTING</code></p>

<p>See: <a class="external-link" href="http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/t0004636.htm" rel="nofollow">http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/t0004636.htm</a></p>

<h3>Example ResourceLoader configuration</h3>
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
resource.ds.className=com.ibm.db2.jcc.DB2XADataSource
resource.ds.uniqueName=dbtwo
resource.ds.maxPoolSize=5
resource.ds.keepConnectionOpenUntilAfter2Pc=true   # (1.1 only)
resource.ds.driverProperties.user=db2user
resource.ds.driverProperties.password=db2password
resource.ds.driverProperties.databaseName=users1
resource.ds.driverProperties.driverType=4
resource.ds.driverProperties.serverName=localhost
resource.ds.driverProperties.portNumber=50000
</pre></td></tr></table>

<h3>Evaluation</h3>
<p>BTM 1.0 cannot be used with IBM DB2. This is because IBM's JDBC driver does not allow XAResource calls after the java.sql.Connection object acquired from javax.sql.XAConnection has been closed (see <a class="external-link" href="https://issues.apache.org/jira/browse/DERBY-246" rel="nofollow">https://issues.apache.org/jira/browse/DERBY-246</a>). This is fixed in BTM 1.1 thanks to the new <code>keepConnectionOpenUntilAfter2Pc</code> parameter. BTM 1.2 removed that flag so DB2 just works without setting anything special.</p>

<h2>Informix</h2>
<h3>Supported version(s)</h3>
<p>Tested against versions 10.00.TC3TL and 10.00.TC5I1 both with driver version 3.00.JC3.</p>

<h3>Settings</h3>
<p>All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable <code>allowLocalTransactions</code>.<br />
Informix fully supports Transaction Interleaving so you should set <code>deferConnectionRelease</code> to <code>false</code> if you properly configured the datasource and the database: all tables must be configured with row-level locking and you have to update the statistics in the system catalogs that the optimizer uses to determine the lowest-cost query plan. This basically means executing these commands:</p>

<h3>Heuristics</h3>
<p>This gets the list of prepared transactions: onstat -G.</p>

<p>I could not find to to heuristically terminate the TX however. Documentation speaks about <code>onmode -Z &lt;address&gt;</code> but I get this error when I try:</p>

<h3>Example ResourceLoader configuration</h3>
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
resource.ds.className=com.informix.jdbcx.IfxXADataSource
resource.ds.uniqueName=informix
resource.ds.maxPoolSize=5
resource.ds.driverProperties.user=informix
resource.ds.driverProperties.password=informix
resource.ds.driverProperties.serverName=ifxtest
resource.ds.driverProperties.databaseName=users1
resource.ds.driverProperties.portNumber=1526
resource.ds.driverProperties.ifxIFXHOST=localhost
resource.ds.driverProperties.ifxIFX_LOCK_MODE_WAIT=5
resource.ds.driverProperties.ifxIFX_XASPEC=Y
</pre></td></tr></table>

<h3>Evaluation</h3>
<p>You have to create your databases with <code>create database with log</code> or else the XA datasource will refuse to connect to it. If you do not configure driver property <code>ifxIFX_LOCK_MODE_WAIT</code> to be different than <code>0</code>, you will get this exception if you run multiple transactions concurrently:</p>

<p>See <a class="external-link" href="http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.jdbc.doc/jdbc55.htm" rel="nofollow">http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.jdbc.doc/jdbc55.htm</a>.</p>

<p>Another cause for this problem is that by default XA transactions with the same GTRID don't share locks. Setting <code>ifxIFX_XASPEC</code> to <code>Y</code> allows those locks to be shared which not only helps solving locking problems but will also improve overall speed.</p>

<p>See <a class="external-link" href="http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0602sudhakar/index.html#tight" rel="nofollow">http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0602sudhakar/index.html#tight</a>. </p>

<h2>Microsoft SQL Server</h2>
<h3>Supported version(s)</h3>
<p>Tested against version 2005 Express with Microsoft JDBC driver version 1.2</p>

<h3>Settings</h3>
<p>All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable <code>allowLocalTransactions</code>.</p>

<h3>Heuristics</h3>
<p>XA transactions are controlled by Microsoft DTC. Run <code>Control Panel &gt; Administrative Tools &gt; Component Services</code>. Then choose <code>Component Services &gt; Computers &gt; My Computer &gt; Distributed Transaction Coordinator &gt; Transaction List</code>.</p>

<p>From there, you have a list of all in-doubt transactions. You can force termination or forget by right clicking on the entries.</p>

<h3>Example ResourceLoader configuration</h3>
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
resource.ds.className=com.microsoft.sqlserver.jdbc.SQLServerXADataSource
resource.ds.uniqueName=sqlsrv
resource.ds.maxPoolSize=5
resource.ds.driverProperties.user=users1
resource.ds.driverProperties.password=users1
resource.ds.driverProperties.serverName=localhost
</pre></td></tr></table>

<h3>Evaluation</h3>
<p>XA seems to work fine when everything is setup properly but this is no simple task.</p>

<p>You need to grab the Microsoft JDBC driver from <a class="external-link" href="http://msdn.microsoft.com/data/jdbc/" rel="nofollow">http://msdn.microsoft.com/data/jdbc/</a>. Version 1.1 does not work so grab version 1.2 instead. You then have to copy the <code>sqljdbc_xa.dll</code> inside SQL Server's <code>binn</code> folder then execute <code>xa_install.sql</code> as user <code>sa</code> as documented.</p>

<p>You must also enable MS DTC's XA support and grant <code>SqlJDBCXAUser</code> role to your user as explained here: <a class="external-link" href="http://msdn2.microsoft.com/en-us/library/aa342335.aspx" rel="nofollow">http://msdn2.microsoft.com/en-us/library/aa342335.aspx</a></p>

<p><em>Note to Windows XP users:</em><br />
Windows XP lacks some internal components related to DTC as discussed here: <a class="external-link" href="http://forums.microsoft.com/msdn/showpost.aspx?postid=262525" rel="nofollow">http://forums.microsoft.com/msdn/showpost.aspx?postid=262525</a>. Microsoft has fixed this issue and released a Hotfix: <a class="external-link" href="http://support.microsoft.com/kb/922668" rel="nofollow">http://support.microsoft.com/kb/922668</a>. Unfortunately you cannot freely download it, you must call Microsoft support to get it. Also, it refuses to install on any non-English version of Windows.<br />
Windows 2000 and Windows Server 2003 are said to be unaffected by this problem.</p>

<h2>Oracle</h2>
<h3>Supported version(s)</h3>
<p>Tested against 9.2.0.1 and 10 XE both with Oracle driver 10.2.0.1.0.</p>

<h3>Settings</h3>
<p>All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable <code>allowLocalTransactions</code>.</p>

<h3>Heuristics</h3>
<p>I could not heuristically terminate a prepared transaction with <code>COMMIT FORCE / ROLLBACK FORCE</code>. Could be a bug in my version of XE or me not understanding how to do it. I followed these instructions without success: <a class="external-link" href="http://www.akadia.com/services/ora_important_part_1.html#How%20to%20remove%20%ABin-doubt%BB%20transactions" rel="nofollow">http://www.akadia.com/services/ora_important_part_1.html#How%20to%20remove%20%ABin-doubt%BB%20transactions</a>. There is no show-stopper except for this problem but I suspect it works fine in other versions.</p>

<p>To view current XA transactions, query system view <code>SYS.DBA_2PC_PENDING</code>.</p>

<h3>Example ResourceLoader configuration</h3>
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
resource.ds.className=oracle.jdbc.xa.client.OracleXADataSource
resource.ds.uniqueName=oracle
resource.ds.maxPoolSize=5
resource.ds.driverProperties.user=users1
resource.ds.driverProperties.password=users1
resource.ds.driverProperties.URL=jdbc:oracle:thin:@localhost:1521:XE
</pre></td></tr></table>

<h3>Evaluation</h3>
<p>The driver and the DB support all the features of XA but sometimes in an exotic and non-standard way. Handling of <code>XAResource.XA_RDONLY</code> is quite bizarre. Error messages returned by the driver are poor at best and often non-existent. Documentation is low in quality and sparse.</p>

<p>You need these privileges to run XA transactions:</p>

<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
grant select on sys.dba_pending_transactions to user_test;
grant select on sys.pending_trans$ to user_test;
grant select on sys.dba_2pc_pending to user_test;
grant execute on sys.dbms_system to user_test;
</pre></td></tr></table>

<p>I could not find documentation about this anywhere, I had to spy the driver to figure that out.</p>

<p>There are several limitations to Oracle's XA support. One of them is for instance the use of temporary tables which is forbidden, see: <a class="external-link" href="http://forums.oracle.com/forums/thread.jspa?threadID=638791&amp;tstart=0" rel="nofollow">http://forums.oracle.com/forums/thread.jspa?threadID=638791&amp;tstart=0</a>. There is a suggestion of a workaround using two connections.</p>

<p>Some user reported to have tested direct use of the underlying OracleConnection instead of the BTM connection handler but that didn't work. Other workarounds have been tried too without much success either. See <a class="external-link" href="http://www.nabble.com/Oracle-exception-to16479456.html" rel="nofollow">http://www.nabble.com/Oracle-exception-to16479456.html</a> for more details.</p>

<p>If someone can suggest a working solution be welcome to contribute it.</p>

<p>Oracle Spatial cannot be used together with XA anymore starting with 10g due to this limitation, see: <a class="external-link" href="http://escuelle.blogspot.com/2008/11/ora-14450.html" rel="nofollow">http://escuelle.blogspot.com/2008/11/ora-14450.html</a></p>

<h2>PostgreSQL</h2>
<h3>Supported version(s)</h3>
<p>Tested against version 8.3 with driver version 8.3-603.</p>

<h3>Settings</h3>
<p>All default settings are fine.</p>

<h3>Heuristics</h3>
<p>Not fully supported. You can list in-doubt transactions and force their termination but the database will immediately forget about them.</p>

<p>To get the list of in-doubt transactions, query the <code>pg_prepared_xacts</code> system view.<br />
You can then forcibly commit or rollback any pending one with<br />
<code>COMMIT TRANSACTION '&lt;content of the GID column&gt;'</code> or <code>ROLLBACK TRANSACTION '&lt;content of the GID column&gt;'</code></p>

<h3>Example ResourceLoader configuration</h3>
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
resource.ds.className=org.postgresql.xa.PGXADataSource
resource.ds.uniqueName=pgsql
resource.ds.maxPoolSize=5
resource.ds.driverProperties.user=postgres
resource.ds.driverProperties.password=postgres
resource.ds.driverProperties.databaseName=postgres
resource.ds.driverProperties.serverName=localhost
</pre></td></tr></table>

<h3>Evaluation</h3>
<p>Started to work really well very recently. Driver version 8.3-603 is the very first one that is fully working with BTM, do not try using any earlier version.</p>

<p>There is a configuration setting called <code>max_prepared_transactions</code> in <code>postgresql.conf</code> which defaults to 5. This is the maximum amount of XA transactions that can exist at any single time in the database. The default setting is way too low to be useful. It is recommended to set it at least to twice the maximum amount of connections in your pool.</p>

<h2>Sybase ASE</h2>
<h3>Supported version(s)</h3>
<p>Tested against versions 12.5.3 and 15 both with driver jConnect version 5.5.</p>

<h3>Settings</h3>
<p>All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable <code>allowLocalTransactions</code>.</p>

<h3>Heuristics</h3>
<p>the <code>sp_transactions</code> stored procedure shows in-doubt transactions.</p>

<p>Terminate an in-doubt transaction with <code>dbcc complete_xact ('&lt;content of xactname column of sp_transactions&gt;', '&lt;commit/rollback&gt;')</code>.</p>

<p>Forget a transaction with <code>dbcc forget_xact ('&lt;content of xactname column of sp_transactions&gt;')</code>.</p>

<p>User must have <code>sa_role</code> role to call <code>sp_transactions</code> or <code>dbcc</code>, see: <a class="external-link" href="http://infocenter.sybase.com/help/topic/com.sybase.help.ase_12.5.1.dtm/html/dtm/dtm1.htm" rel="nofollow">http://infocenter.sybase.com/help/topic/com.sybase.help.ase_12.5.1.dtm/html/dtm/dtm1.htm</a>.</p>

<h3>Example ResourceLoader configuration</h3>
<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
resource.ds.className=com.sybase.jdbc2.jdbc.SybXADataSource
resource.ds.uniqueName=ase
resource.ds.maxPoolSize=5
resource.ds.driverProperties.user=users1
resource.ds.driverProperties.password=users1
resource.ds.driverProperties.serverName=localhost
resource.ds.driverProperties.portNumber=5000
</pre></td></tr></table>

<h3>Evaluation</h3>
<p>XA support is handled by Distributed Transaction Manager that is not part of the default install (must be manually selected during the installation) and must be manually enabled in the DB before it can be used: <a class="external-link" href="http://manuals.sybase.com/onlinebooks/group-as/asg1250e/xainterf/@Generic__BookTextView/1079" rel="nofollow">http://manuals.sybase.com/onlinebooks/group-as/asg1250e/xainterf/@Generic__BookTextView/1079</a>.</p>

<p>After too many unfinished transactions (<em>not sure here</em>) the database segments get full because of transaction logs. Symptoms are that connections get stuck and eventually even opening a new connection freezes the client process. You can clean that using these commands as user <code>sa</code>:</p>

<p>See: <a class="external-link" href="http://www.isug.com/Sybase_FAQ/ASE/section1.3.html#1.3.1" rel="nofollow">http://www.isug.com/Sybase_FAQ/ASE/section1.3.html#1.3.1</a>.</p>

<p>There is a bug in the JDBC driver that messes up recovery: calling commit or rollback on a heurstically terminated transaction, an <code>XAException</code> is thrown with an invalid error code (0) instead of <code>XA_HEUR&lt;something&gt;</code>. Stack trace looks as follow in that case:</p>

<table class="wysiwyg-macro" data-macro-name="code" style="background-image: url(/plugins/servlet/confluence/placeholder/macro-heading?definition=e2NvZGV9&amp;locale=en_GB&amp;version=2); background-repeat: no-repeat;" data-macro-body-type="PLAIN_TEXT"><tr><td class="wysiwyg-macro-body"><pre>
javax.transaction.xa.XAException: Unrecognized return code from server: -259
at com.sybase.jdbc3.jdbc.SybXAResource.a(Unknown Source)
at com.sybase.jdbc3.jdbc.SybXAResource.a(Unknown Source)
at com.sybase.jdbc3.jdbc.SybXAResource.a(Unknown Source)
at com.sybase.jdbc3.jdbc.SybXAResource.rollback(Unknown Source)
at bitronix.tm.drivers.SybaseTest.testRecover(SybaseTest.java:101)
...
</pre></td></tr></table>

<p>See <a class="external-link" href="http://search.sybase.com/kbx/changerequests?bug_id=417417" rel="nofollow">http://search.sybase.com/kbx/changerequests?bug_id=417417</a></p>

<p>There is another bug in the driver that makes it add a <code>SQLWarning</code> to a connection each time it is enlisted.</p>

<p>See <a class="external-link" href="http://search.sybase.com/kbx/changerequests?bug_id=531772" rel="nofollow">http://search.sybase.com/kbx/changerequests?bug_id=531772</a></p>

<p>Driver and DB seems to be reasonably well implemented and documented otherwise. <br />
<img class="confluence-embedded-image confluence-external-resource" src="http://www.bitronix.be/images/shim.gif" data-image-src="http://www.bitronix.be/images/shim.gif"></p>