jdbcTemplate.update freezes

Question

I'm using a Spring JdbcTemplate without a "transactionManager" since I have mostly select to do.

When I try to call select queries from JUnit, it works, but when I try to call an "update", it freezes the test (no connection timeout, nothing, just waiting).

I've seen examples of jdbcTemplates insert/update without any transactionManager, but could it be the problem here ?

  public void insert(String param1, String param2) {

    String sql = "UPDATE MYTABLE SET name = :param1 where first_name = :param2";

    NamedParameterJdbcTemplate npJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("param1", param1).addValue("param2", param2);

    npJdbcTemplate.update(sql, namedParameters);
  }

Octangular

How big is the table? Does the query result in a full table scan making it take a long time? Can the query be executed by some other tool? Does the underlying data source provide connections with auto commit set to true? – Adversity

I would try the same update with JDBC (PreparedStatement.executeUpdate) to see if it hangs without Spring or not. – Schwartz

The same query is instant in any SQL client. You may be right about the "autocommit" settings and the commit not occuring. Let's say my datasource is in autocommit = false, what should I do to commit manually ? – Octangular

I get it I guess : If my datasource is not in "autocommit", I need a "transactionManager" to call the "commit" manually, right ? – Octangular

In this example, he's using hikariCP like me, autocommit is not set (default is false) and update is occuring though : mkyong.com/spring-boot/spring-boot-jdbc-mysql-hikaricp-example – Octangular

Maybe the problem is not in my code, but just that my test query in SQL client was not comitted : #6911911 – Octangular

Just because you don't specify a transactionmanager doesn't mean there's not a transaction (unless you're specifically using a non-transactional database engine). it just means you're not getting the benefit of using spring to demarcate transaction boundaries, set isolation levels, etc. – Divers

Yes, you're right, I edit my question : transaction -> transactionManager. – Octangular

Answer 1

The problem here was I had passed the same update query on the same line in a SQL client (Oracle SQL developer) but it had not been committed in this client.

My JUnit had been stalled for 12 hours and right after I commit the query in SQL developer, the update occurred in the JUnit.

It had nothing to do with transaction management in the app or autocommit status of the datasource.

Octangular

OMG, I'd upvote this more than once. Spent 2 hours searching the cause why a certain update call wasn't executed and I had still an open commit in my DataGrip application. – Spaceless

Exactly my case too! Thank you! – Cultivator

Been stuck with this issue for a day. Thankyou ! – Nonie

Thank you so much for your answer. I spent a day trying to understand what I was doing wrong. – Eslinger

Still relevant in 2023! Had to commit the query in Toad for Oracle in my case. – Vesical