conflicts in database connections in JTA

[tbee]

I've got a setup where business model and Activiti are mixed into one engine. In order to facilitate transactional consistency, Bitronix JTA-XA is used to enabled distributed transactions. Configuration wise this works, however during the execution of a Activiti listener the connections have a conflict: the execution freezes and after a while a time out occurs. This looks very similar to a locking conflict.

In order to examine this, I've logged all changes that Activiti makes to the database:

 169: [EL Finer]: 2012-09-20 14:08:17.962–ClientSession(5578877)–Thread(Thread[main,5,main])–TX beginTransaction, status=STATUS_ACTIVE
 306: 2012-09-20 14:08:20,448 DEBUG #5958562[C22645161] activiti: execute: update ACT_GE_PROPERTY     …
 334: 2012-09-20 14:08:20,495 DEBUG #10522721[C5094311] activiti: execute: insert into ACT_RU_EXECUTION …
 360: 2012-09-20 14:08:20,495 DEBUG #25022548[C5094311] activiti: execute: insert into ACT_HI_PROCINST …
 384: 2012-09-20 14:08:20,511 DEBUG #13502571[C5094311] activiti: execute: insert into ACT_HI_ACTINST …
 408: 2012-09-20 14:08:20,526 DEBUG #25489800[C5094311] activiti: execute: insert into ACT_RU_VARIABLE …
 432: 2012-09-20 14:08:20,542 DEBUG #28968689[C5094311] activiti: execute: insert into ACT_RU_VARIABLE …
 456: 2012-09-20 14:08:20,542 DEBUG #9486511[C5094311] activiti: execute: insert into ACT_HI_ACTINST …
 486: 2012-09-20 14:08:20,557 DEBUG #4047039[C5094311] activiti: execute: insert into ACT_RU_TASK …
 520: 2012-09-20 14:08:20,573 DEBUG #2157445[C5094311] activiti: execute: insert into ACT_HI_TASKINST …
 534: 2012-09-20 14:08:20,589 DEBUG #22864626[C5094311] activiti: execute: insert into ACT_RU_IDENTITYLINK …
 674: [EL Finer]: 2012-09-20 14:08:20.714–UnitOfWork(30281720)–Thread(Thread[main,5,main])–TX afterCompletion callback, status=COMMITTED
 676: —————————
 677: [EL Finer]: 2012-09-20 14:08:20.729–ClientSession(5578877)–Thread(Thread[main,5,main])–TX beginTransaction, status=STATUS_ACTIVE
 741: 2012-09-20 14:08:20,808 DEBUG #17985579[C5094311] activiti: execute: update ACT_RU_TASK …
 769: 2012-09-20 14:08:20,823 DEBUG #32139235[C5094311] activiti: execute: update ACT_HI_TASKINST …
 781: 2012-09-20 14:08:20,823 DEBUG #28336320[C5094311] activiti: execute: update ACT_HI_ACTINST …
 877: 2012-09-20 14:08:20,901 DEBUG #28860947[C22645161] activiti: execute: insert into ACT_RU_VARIABLE …
 913: 2012-09-20 14:08:20,917 DEBUG #9528986[C5094311] activiti: execute: insert into ACT_RU_EXECUTION …
 937: 2012-09-20 14:08:20,933 DEBUG #33054865[C5094311] activiti: execute: insert into ACT_HI_ACTINST …
 961: 2012-09-20 14:08:20,948 DEBUG #21289936[C5094311] activiti: execute: insert into ACT_HI_ACTINST …
 991: 2012-09-20 14:08:20,980 DEBUG #23935686[C5094311] activiti: execute: insert into ACT_RU_TASK …
1025: 2012-09-20 14:08:20,995 DEBUG #31851612[C5094311] activiti: execute: insert into ACT_HI_TASKINST …
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Important is the number in square brackets which denotes the JDBC connection used. What I notice is that almost all interaction is done through [C5094311], except three which use [C22645161]. Now, prefix 408 and 432 and 877 use the same table through different connections. 877 is the result of this line:

      ProcessEngines.getDefaultProcessEngine().getRuntimeService().setVariable( getWorkflowProcessInstance().getProcessInstanceId(), "validateOfferStage", Long.valueOf(System.currentTimeMillis()));
‍‍‍

And indeed, if i comment this out, the executions continues. Why are there different database connections being used to write to the database?

[tbee]

One more example, this is done within one JTA transaction:


2012-09-20 16:04:13,047 DEBUG #22433263[C18131420] activiti: [1] setString=27101 / java.lang.String / insert into ACT_RU_EXECUTION (ID_, …
…
2012-09-20 16:04:13,329 DEBUG #925089[C16330489] activiti: [1] setString=27101 / java.lang.String / select E.*         from ACT_RU_EXECUTION …
That last line returns null since it uses a different connection than the insert. The last line is the result of:


  List<ProcessInstance> lProcessInstances = lProcessEngine.getRuntimeService().createProcessInstanceQuery().processInstanceId(processInstanceId).list();

Can I configure Activiti (Hibernate) to use the same connection for read and write?

[tbee]

Confirmed! In non JTA mode Activiti uses one JDBC connection for all three statements (408, 432, 877). I'm filing a bug.

[meyerd]

Hi tbee,

when you do


ProcessEngines.getDefaultProcessEngine().getRuntimeService().setVariable( getWorkflowProcessInstance().getProcessInstanceId(), "validateOfferStage", Long.valueOf(System.currentTimeMillis()));

a new command is executed which opens a new mybatis session. In this session, a connection to the database is obtained. You write that you setup the environment with Bitronix yourself. Are you using a JTA aware datasource? When you are using an Java EE application server, the JCA database connector make sure that whenever you obtain a JDBC connection and already obtained one in the same JTA transaction, you get the same connection.

I would invite you to run you test in a Java EE application server like Jboss 7 or Glassfish 3 since there all of this is already setup correctly.

Read: http://jbossts.blogspot.de/2012/01/connecting-dots.html

[tbee]

In the Jira issue

https://jira.codehaus.org/browse/ACT-1385

Frederick Heremans says The default activiti behavior uses thread-local/datasource transaction management or a similar mechanism, which results in reusing the open connection from the 1st context. When using JTA for handling transactions however, a new transaction is created with a new connection.

So he confirms that there is a different connection management in normal vs JTA mode. Naturally my opinion is that there should not be; if code runs in normal mode, it should also in JTA. So the question is: why is there a different handling? Is there a good reason to do so? Why can't the connection in JTA mode be reused as well?

I'm using Postgres 9.1, the driver is JTA-XA compatible. I've made a work around for my current code (no idea what I will run into in the future) by attaching the ExeuctionDelegate and TaskDelegate to the current thread in the listeners, and use that further down in a custom setVariable.

[meyerd]

Hi tbee,

You need a JTA-transaction-aware datasource proxy. This has nothing to do with the database driver. You need a datasource that integrates with your transaction manager, such that you always use the same connection in the same transaciton.

Have you used this:
http://docs.codehaus.org/display/BTM/JdbcConfiguration2x
?

Make sure to set
shareTransactionConnections=true

[meyerd]

Frederick Heremans says The default activiti behavior uses thread-local/datasource transaction management or a similar mechanism, which results in reusing the open connection from the 1st context. When using JTA for handling transactions however, a new transaction is created with a new connection.

This is wrong BTW. You only get a new connection, if you open a new JTA transaction. the default behavior is that the 2nd context participates in the transaction opened by the first context. If you would open a new transaction for the second command, you would get a new connection.

Compare this to REQUIRED vs. REQUIRES_NEW in EJB.

[tbee]

That could very well be wrong, I do not know. The only point I think is relevant is that IMHO Activiti should behave identically in normal and JTA mode.

[meyerd]

The only point I think is relevant is that IMHO Activiti should behave identically in normal and JTA mode.

It does, if you setup JTA correctly! Just try it in Jboss or Glassfish.

[tbee]

I do not see how I could have made an error in setting my unit test environment up.
- I create a single DataSource in Bitronix
- I assign this DataSource to a JNDI name (using Glassfishes JNDI implementation)
- I tell Activiti to use JTA and specify the JNDI name

Where could I have made a mistake?

Tom