Analyzing the sql statements which have been performed during a general
select all statement on a transactional database (MySQL InnoDB) we
discovered that leaving the c3p0 property
forceIgnoreUnresolvedTransactions unchanged (that is to the default
value false) is a serious performance penalty. The test consisted of
executing a block of 10.000 select all queries without processing the
result set for 5 times in a row. The only thing we have changed during
test 1 and 2 is the property forceIgnoreUnresolvedTransactions.

Here are the results we’ve seen:

1. c3p0.forceIgnoreUnresolvedTransactions = true

10000 of queries took 40739 msec.
10000 of queries took 34729 msec.
10000 of queries took 33370 msec.
10000 of queries took 34192 msec.
10000 of queries took 33864 msec.

executed sql-statement per select-query:
select * from article this_ order by this_.code asc

On average that is 3.54 msec per query.

2. c3p0.forceIgnoreUnresolvedTransactions = false

10000 of queries took 88558 msec.
10000 of queries took 85622 msec.
10000 of queries took 85611 msec.
10000 of queries took 85203 msec.
10000 of queries took 82695 msec.

executed sql-statements per select-query:
SET autocommit=0
select * from article this_ order by this_.code asc
rollback
SET autocommit=1

On average that is 8.55 msec per query. (This is ~2.5 times as long)

(Note that the select-statement is executed by Hibernate, the set
autocommit and rollback statements are executed by c3p0)

So leaving the setting forceIgnoreUnresolvedTransactions unchanged is a
serious performance issue. In spite of the terrifying warnings
associated with setting this property you should still concern it. It is
a pity that the documentation does not shed some light on the exact
conditions on when setting this property can lead to subtle and bizarre
bugs.

Any comments on these results or more details about the possible failure
conditions would be welcome.

I never did see these autocommit and rollback statements executed by c3p0.
Can it be that in hibernate you use nontransactional query (= querying without beginning a transaction before explicitely) ?

It is indeed a non-transactional query.
Note that we have logged the sql statements on the sql-driver using the connection url
so we've added the following parameters for the connection url:
profileSQL=true&logger=com.mysql.jdbc.log.Log4JLogger&profilerEventHandler=<FQN profiler class>

Furthermore we figured out that the test is a bit flawed that is, we had query caching of MySQL
enabled which makes the performance of the test query to be fast, when disabling the query cache
the difference between setting the flag to true/false was minor that is ~500msecs slower on query
average.

We have also discovered a vague problem with this flag, that is when configured the connection release mode to
aggressive release mode.
We're performing a select all query on connection A, than a insert on connection A, than when
you get a second different connection B, it happens that when performing a select all query the
newly inserted record is not in the result set. Is seems like c3p0 is doing some kind of resultset/connection
caching which is related to this flag. Setting it to false we did not get this inconsistent query results.