Subject Re: [Firebird-Java] reuse of connection after failure.
Author Andrew Goedhart
Will pull the patch from head.

>> When we brought it back up, everytime we tried to start XA transactions, they timed out completely and then jboss hangs. It does not deal well with transaction time outs well. I can still access the data. I data pumped the data inserted after the last nbackup to another database whilst we did a restore. This makes me guess that normal transactioning is working, but XA transactioning has failed permanently.

>Do you have any errors in the log?!

The only errors in the log are where we run out of semaphores. We have had this before, increasing the semaphores in the database and the linux operating system(because the default for a linux process is 32), does not help, it only takes slightly longer to run out of them. Doing a restore stops the problem completely.... (for a while :-(
We also no longer run out of semaphores. Another symptom is that all the cores on the server go to a much higher average load.
Also there where no limbo or two phase commit transactions listed when we ran gfix. Weird and frustrating.

>> How do you set up the data source to use local transactions. I changed <xa-transaction> to <local-transaction>ion the firebird -ds.xml file that is loaded by the firebirdsql.rar (renamed jaybird 2.1.1 rar) file. Is this sufficient ? What really does <local-transaction> really mean on a JCA managed connection ?

Use Oracle's example to setup the data source.

>> I am in the process of splitting off the readings table from the rest of the database. It accounts for 80% of the size and 60% of the insert load, but loosing a few readings due to failure at this point is an option if it means the rest of the system remains stable. This means I need to XA Transactions from this Datasource.

> Ok. Then you will have two datasources.

Sorry the language is wrong, it should read then I do not need XA transactions for that datasource.
I.e. will be running only 40% of the previous XA load.

> Hibernate HAR files for JBoss are also JCA managed ( I think), We actually don't use any EJBs, only Hibernate. My understanding is that Hibernate is also configured as a JCA resource and uses the Jboss transaction manager automatically.

> It is a bit different - Hibernate support is a plugin for JBoss (SAR).
Also it depends what transaction_manager_lookup_class you use.

We have done a few SARs so far, message server, map server, scheduler etc. , maybe my confusion is with the name and the fact that I did not see it in the current service XML file.

>> Can I still use hibernate HARs with jaybird and Jboss if I turn off XA transactions on the data source or do I have to roll my own hibernate.cfg.xml file and load it manually so I can set a local transaction manager?

>We never used XA connections with Hiberanate and JBoss (though, it was
Oracle, but configured as local-tx-datasource).

>> My guess this is where I was getting lockups earlier when applying local transactions as the local connection was expecting the transaction to be committed on it but it was committed on the Jboss transaction manager instead.

The interesting thing is if I only change the configuration file and not the code, to use <XA transaction> to use <Local-transaction> the lock ups start to happen. This is the only change. The code works fine with the rest except for the XA transaction issues discussed earlier, and they only happen once in a while if not under heavy load.

>This does not depend on the datasource type, but on how you have
configured your Hibernate. In our experience most stable case was to use
autoflush mechanism of Hibernate and JBoss tx manager lookup (exactly as
Hibernate docu describes). But we were using SessionBeans (sometimes
with CMT, sometimes with BMT).

>In your case the question is where do you start your session and your
transaction. Do you use servlet filters to open a session and start
transaction on the beginning of the http request and commit at the end?
Or you manage everything yourself?

Depending on the server. We we use a filter and for MsgServer and the other SAR's we use a Jboss UserTransaction which we manage ourselves.

>> Eventually you start to get dead locks beecause nothing is commited as
far as Firebird is concerned.

>You can change the TPB for that particular datasource, so you get
deadlock error straightaway.