Spring TransactionAwareDataSourceProxy.getConnection let connections leak after commit while DataSourceUtils.getConnection does not

Affects: 5.3.22

I have a DataSource of type TransactionAwareDataSourceProxy . My PlatformTransactionManager is JpaTransactionManager.

I have the following tests:

@SpringBootTest
class DataSourceTest {

  @Inject private PlatformTransactionManager transactionManager;
  @Inject private DataSource dataSource;

  @Test
  void test1() throws SQLException {
    TransactionStatus transaction = transactionManager.getTransaction(null);
    Connection conn = DataSourceUtils.getConnection(dataSource);
    transactionManager.commit(transaction);
    conn.createStatement().execute("select 1");
  }

  @Test
  void test2() throws SQLException {
    TransactionStatus transaction = transactionManager.getTransaction(null);
    Connection conn = dataSource.getConnection();
    transactionManager.commit(transaction);
    conn.createStatement().execute("select 1");
  }
}

As you can see, test1 takes the Connection via DataSourceUtils.getConnection while test2 takes it directly on the DataSource.

test1 fails on createStatement() because the connection is closed. test2 does not fail.

Note that I didn't find any documentation telling to avoid using DataSourceUtils on a TransactionAwareDataSourceProxy.

Therefore: - IMO, this is a bug because tests should have the same output - I don't know what should be the correct output? Is that normal that a connection "leaks" after closing a transaction like in test2 ?

Comment From: reda-alaoui

Here is test3 :

@Test
  void test3() throws SQLException {
    TransactionStatus transaction = transactionManager.getTransaction(null);
    DataSource unWrappedDataSource =
        ((TransactionAwareDataSourceProxy) dataSource).getTargetDataSource();
    Connection conn = DataSourceUtils.getConnection(unWrappedDataSource);
    transactionManager.commit(transaction);
    conn.createStatement().execute("select 1");
  }

It is similar to test1 but unwrap the DataSource from TransactionAwareDataSourceProxy before using DataSourceUtils. It behaves as test1, it fails.

Comment From: reda-alaoui

If I rewrite test2 like this:

@Test
  void test2() throws SQLException {
    TransactionStatus transaction = transactionManager.getTransaction(null);
    Connection conn = dataSource.getConnection();
    conn.createStatement().execute("select 1"); <-- added line
    transactionManager.commit(transaction);
    conn.createStatement().execute("select 1");
  }

test2 will then behave as test1 and test3 (i.e. the second statement creation will fail).

I think I found the reason of these differences: - JpaTransactionManager unwraps TransactionAwareDataSourceProxy to manipulate only the underlying DataSource - TransactionAwareDataSourceProxy connection's proxy delays underlying connection fetch as much as possible

What happens: - When JpaTransactionManager commits, it closes the underlying Connection. TransactionAwareDataSourceProxy is unaware of that. - Creating the statement on the TransactionAwareDataSourceProxy connection's proxy after the commits leads to the acquisition of a new connection, not bound to any transaction

IMO, the easiest fix would be to make TransactionAwareDataSourceProxy fetch its underlying connection eagerly if TransactionAwareDataSourceProxy#reobtainTransactionalConnections is false (default value). An alternative that looks more risky would be to make JpaTransactionManager works with TransactionAwareDataSourceProxy (without unwrapping it).

Comment From: reda-alaoui

Ping ?

Comment From: jhoeller

Superseded by PR #29423.

Comment From: jhoeller

For completeness, note that the general DataSource usage pattern is incomplete in the examples above: For every DataSource.getConnection() call, there needs to be a corresponding Connection.close() call within the same scope. It is generally not proper usage to open a Connection handle with the scope of a transaction and then continue using it afterwards.

The main reason for the current default behavior is an outdated usage pattern from Java EE land: opening a Connection first, then starting a transaction, then committing the transaction, then closing the Connection. Lazy target binding on first actual usage was our way to make that scenario work properly. As a positive side effect, this also avoids connection contention for transactions where no actual resource has been fetched, e.g. in JTA scenarios.

As of #29423, a configuration flag will allow to opt out of that lazy target binding behavior.