Spring Transactions not working - JDBCTemplate is reading uncommitted data

Question

The following Method inserts two records (but doesn't commits them at this point) and it then tries to read one of the uncommitted records from the previous statements. I wrapped the code with Transaction, and set the isolationLevel to "READ_COMMITTED" but this doesn't seems to be working. The read/"SELECT" statement is reading the uncommitted records.

How is this possible? Where am I going wrong? Please see the code below and help me out. I would be really thankful ~

Note :
I am using BoneCP to get the DataSource. dbConnectionPool.initConnectionPool(dbName) , will fetch a BoneCPDataSource.

 @Override public void testDBCalls() {
  dBConnectionPool.initConnectionPool("titans");      
  DataSource dataSource = dBConnectionPool.getDataSource("titans");   
  DefaultTransactionDefinition definition = new DefaultTransactionDefinition();

  definition.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
  definition.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);
  definition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

  DataSourceTransactionManager txManager = new DataSourceTransactionManager(dataSource);      TransactionStatus
  transactionStatus = txManager.getTransaction(definition);

  try {           
        try {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "INSERT INTO groundwater(external_id,source_type) VALUES (12, 13);";
          jdbcTemplate.update(sql);
          System.out.println("Successfully inserted - 1");

          String sql2 = "INSERT INTO groundwater(external_id, source_type,) VALUES(123,45);";
          jdbcTemplate.update(sql2);
          System.out.println("Successfully inserted - 2");

          String sql3 = "select gw_id from groundwater where external_id= 123;";
          System.out.println("Result : "+jdbcTemplate.queryForInt(sql3));

          txManager.commit(transactionStatus);
          System.out.println("Commiting the trasaction...");

      } catch (Exception e) {
          e.printStackTrace();
          txManager.rollback(transactionStatus);
          System.out.println("Rolling back the transaction");

      }
  } finally {
      try {
          dataSource.getConnection().close();
          System.out.println("Closing the connection ...");       
        } catch (SQLException e) {
            e.printStackTrace();            
        }
    }
 }

And why shouldn't it work. You are indeed not committing any thing but everything is run from a single transaction, a transaction can always see the changes it made. — M. Deinum, Jul 24 '16 at 18:30
Possible duplicate of [How Do SQL Transactions Work?](http://stackoverflow.com/questions/1668166/how-do-sql-transactions-work) — Software Engineer, Jul 25 '16 at 00:38

score 0 · Accepted Answer · answered Aug 10 '16 at 09:09

0

As @M.Denium explained in the comment, I was trying to do everything from a single transaction. Isolation Levels are meant for maintaining consistency across different transactions. I was still learning the concepts, so I took it in a wrong way.

answered Aug 10 '16 at 09:09

Muhammed Zubairuddin

147
2
10

Spring Transactions not working - JDBCTemplate is reading uncommitted data

1 Answers1