Problems with LocalTx and XATx Databases
Hi,
We have recently upgraded to JBoss 3.2.1 from 2.4.7, and are getting quite a few problems. We are using two databases:
SQL Server: supports XATransactions
Tamino: only supports LocalTransactions.
I have included the main ways of how we access our databases here, and would appreciate it if anybody could let us know if we are going hideously wrong somewhere. I am not sure if the problems are due to any of this or if it could be due to our servers recently being under a higher load. It all works fine on our development server, and I have created a few tests trying out each access method that I am about to do a load test on.
Access methods:
---------------
BMT facades:
BMT-1) Writing to Tamino and/or SQL Server
Here we use separate a UserTransaction for writing to each Db, and is OK (apart from the normal stuff about accessing multiple non-2PC databases)
BMT-2) Reading from Tamino and/or SQL Server
Here we don't use UserTransaction, if an error occurs we throw an EJBException and don't go on trying to access any more data.
CMT facades (trans-atribute=Required):
CMT-1) Writing to SQL Server only
CMT-2) Writing to Tamino only
CMT-3) Reading from SQL Server only
CMT-4) Reading from SQL Server only
These should all be OK, and I have only included them for completeness
CMT-5) Writing to SQL Server and Tamino in same facade call
We never do this, as JBoss won't let us. We noticed at start of project when using 2.4.7, and are using BMT-1 to achieve this
CMT-6) Reading from Tamino and reading from SQL
No errors are thrown, the data seems valid, but I am wondering if this could cause any problems.
CMT-7) Reading from Tamino and writing to SQL
No errors are thrown, the data seems valid, but I am wondering if this could cause any problems.
CMT-8) Writing to Tamino and reading from SQL
As far as I can tell this never happens
Problems:
---------
The main problems we are experiencing are:
a) Locks in SQL Server that are never released, causing subsequent accesses to time out
b) We get a few of the 'prepare called on a local tx...' messages, in CMT-6 and CMT-7, but as far as I understand this is just a warning and we are just reading from Tamino here so I don't think it is a problem. Is it?
c) Get a series of the 'Transaction marked for rollback, possibly a timeout' errors on access to SQL Server before it bombs out with the
'No managed connections available'. I have increased the pool size, and made sure that all our DAO code closes all connections (it was doing this already), and all statements and result sets (I found a few cases where this did not happen)
d) Get messages for our Tamino connetions saying. Local transaction mode could not be released properly. Falling back to autocommit mode.
The access methods I am having doubts about are as follows, and if somebody has any definite answers about whether or not this is OK or not, that would be very much appreciated as there are a LOT of code changes involved.
BMT-2) Do we HAVE TO make sure that appropriate UserTransactions are used? What happens if we don't do this?
CMT-6 and CMT-7) What happens here? My guess is that the SQL Server uses the CMT, and that Tamino somehow gets it's own Tx? If this is the case does the order in which I access SQL Server and Tamino have any significance? If this is a problem, the obvious fix I think would be to wrap the read calls to Tamino to go via a local session facade that has trans-attribute=RequiresNew.
Apart from that we are using all the deafult configuration options.
Thanks,
Kab