Issue with LazyConnectionDataSourceProxy after upgrade from Spring 5.3.39 to Spring 6.1.14

[mattwms1998]

In my XML, I am creating the below beans with 'defaultAutoCommit' set to false.
However, I am getting errors "java.sql.SQLException: Can't call commit when autocommit=true". It is like the 'defaultAutoCommit' is being ignored. If set to "true", the error goes away.
I do have declarative transaction around my save method.

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
		<property name="targetDataSource">
			<ref bean="dataSourceTarget" />
		</property>
		<property name="defaultAutoCommit">
			<value>false</value>
		</property>
		<property name="defaultTransactionIsolationName">
			<value>TRANSACTION_READ_COMMITTED</value>
		</property>
	</bean>

	<bean id="dataSourceTarget"class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		..etc
         </bean>

[jhoeller]

Note that "defaultAutoCommit" on LazyConnectionDataSourceProxy has a special meaning: It instructs the lazy proxy to assume a specific auto-commit setting on target Connections that it will obtain later. For that reason, it needs to match the actual auto-commit setting on the target DataSource.

Your scenario seems to indicate a mismatch with the setting on the target DataSource which seems to be "true", so it needs to be "true" on the LazyConnectionDataSourceProxy as well. Alternatively, you may leave it unspecified in which case it will be runtime-determined on first access of a Connection.

[mattwms1998]

Is that a change that is now being enforced in version 6? From Spring 2 to 5, I've had defaultAutoCommit=false in the LazyConnectionDataSourceProxy and if I didn't wrap a declarative transaction around a method, it would not commit.

So, with your explanation, would the recommended approach be to create a Datasource like org.apache.commons.dbcp2.BasicDataSource where I can specify the "defaultAutoCommit" instead of using the org.springframework.jdbc.datasource.DriverManagerDataSource?

[jhoeller]

Indeed, as of #29931, the semantics of "defaultAutoCommit" are stronger - still adhering to the original javadoc but not hard-applying the given value to the underlying Connection anymore. We are avoiding unnecessary interaction with the database that way since even Connection.getAutoCommit() (which we had to call there before) can be expensive.

So yes, as of 6.1, "defaultAutoCommit" needs to be determined at the target DataSource level. The setting at LazyConnectionDataSourceProxy is just an optimization for more efficient Connection interaction against the target DataSource, telling it the target-configured auto-commit flag so that we don't have to determine it at runtime.

I would generally recommend an actual connection pool there, or just accepting that auto-commit will be on by default and needs to be switched off for every transaction at runtime. Note that setting the default auto-commit flag to "false" is only safe if all your DataSource interaction happens within a transaction since otherwise some dangling state can be left in the Connection (and this can break assumptions when a pooled Connection is picked up for some other operation later on).

[mattwms1998]

Thank you for the information and explanation! I updated my configuration to useorg.apache.commons.dbcp2.BasicDataSource and with it setting 'defaultAutoCommit=false'. This fixes the errors and unexpected transaction behavior.