topic Re: Serious issues with MyBatis connection management in Alfresco Archive

Serious issues with MyBatis connection management

franck102 — Thu, 19 Mar 2015 17:48:07 GMT

I have been troubleshooting DB connection issues with Activiti 5.15.1 and after quite a bit of debugging I believe that I have identified at two significant problems. Problem #1: the default configuration of Activiti causes the job executor to keep all MyBatis connections checked out from the pool f

Re: Serious issues with MyBatis connection management

franck102 — Thu, 19 Mar 2015 17:59:44 GMT

Thread dump attached.

I have also included (pool-active-conns.txt) the thread dump of the 10 active connections that are not being returned - the dump is taken at the time the connection is acquire from the pool.

Re: Serious issues with MyBatis connection management

franck102 — Mon, 23 Mar 2015 15:29:07 GMT

So I just stumbled upon http://jira.codehaus.org/browse/ACT-789 - basically that deadlock was reported in the past, and the recommended "fix" seems to be to use the StrongUuidGenerator.

Is this still the recommended approach? Why isn't that generator the default configured generator if it solves what seems to be a critical defect?

Thanks!
Franck

Re: Serious issues with MyBatis connection management

jbarrez — Tue, 24 Mar 2015 13:09:57 GMT

Yes, this is still the recommended approach. See http://activiti.org/userguide/index.html#advanced.uuid.generator

The reason why it is not a default, is because it is less performant. Secondly, not many people use the default connection pool, but their own preference (c3p0, Hikari), or jndi managed. None of our customers use it like that in production, but it is the easiest to get started and running out of the box, with a minimum number of dependencies.

Note that many performance issues are already solved by switching the connection pooling framework. The UUID approach takes care of another (sometimes related) problem: that the load is simply to high for the connection pooling to be able to follow when needing a new batch of ids.

Re: Serious issues with MyBatis connection management

franck102 — Tue, 24 Mar 2015 13:48:25 GMT

I had missed the StrongUuidGenerator being mentioned in the docs, thanks! Unfortunately:

1. Replacing the connection pool implementation will *not* make any difference with respect to connections not being returned to the pool. The pool only option in that situation are to forcibly claim back the connection, which will cause random errors if the connection is subsequently used. If that is confirmed to happen it has to be fixed in Activiti.

2. The default implementation of the ID generator is subject to deadlock because it tries to acquire a connection after locking the object, while other threads do the exact opposite. It will only work if the connection pool always has at least a free idle connection - as soon as all connections are out and trying to generate IDs the deadlock will happen.

I don't see that a different connection pool implementation would make any difference on these two fronts, unless I am missing something? With any pool any DB or network hiccup that causes transactions to be held back for a few seconds will cause the pool to quickly run out of idle connections.

Franck

Re: Serious issues with MyBatis connection management

trademak — Thu, 26 Mar 2015 07:53:00 GMT

Hi Franck,

Switching the connection pool sometimes help to optimise the number of connections and the idle time checks etc. I don't think the job executor keeps the connections checkout out of the pool. But it will poll the database every couple of seconds and execute outstanding jobs, so it might need a connection "all" the time. You can configure the number of threads that are used in the job executor and determine the correct level of connections needed in the connection pool based on that.

Best regards,

Re: Serious issues with MyBatis connection management

franck102 — Thu, 26 Mar 2015 08:22:52 GMT

Thanks Tijs, using the StrongUuidGenerator solved the deadlock problem for us.

I haven't been able to determine conclusively if or when connections fail to be returned, I will run with a high poolMaximumCheckoutTime for a while and I'll report back if I find a problem.

The polling is unlikely to be an issue, it is true that the MyBatis pool uses notifyAll and thus attributes available connection to waiting clients at random, but it would be very unlucky for a waiting client to lose every time and never get his connection…

Franck