Transaction and Physical Connection in Spring

Question

When using Spring jdbcTemplate without @Transactional annotation and dataSource autocommit property set to true.

Each SQL statement will be in it's own transaction, but will these separate transaction be in the same physical connection?

---

In below code snippet(with @Transactional), all three SQL statements will be in one same connection/transaction.

@Transactional
public void test() {

    jdbcTemplate.batchUpdate("INSERT INTO TB_MYTEST(MYKEY, MYVALUE) VALUES ('start', 'start')");
    jdbcTemplate.batchUpdate("INSERT INTO TB_MYTEST(MYKEY, MYVALUE) VALUES ('hi', 'hi')");
    jdbcTemplate.batchUpdate("INSERT INTO TB_MYTEST(MYKEY, MYVALUE) VALUES ('end', 'end')");

}

---

But in below code snippet(without @Transactional), each SQL statement will be in separate transaction.

How about connection? Will all these three separate transactions be in the same physical connection or will Spring get three different connections from dataSource for each transaction?

public void test() {

    jdbcTemplate.batchUpdate("INSERT INTO TB_MYTEST(MYKEY, MYVALUE) VALUES ('start', 'start')");
    jdbcTemplate.batchUpdate("INSERT INTO TB_MYTEST(MYKEY, MYVALUE) VALUES ('hi', 'hi')");
    jdbcTemplate.batchUpdate("INSERT INTO TB_MYTEST(MYKEY, MYVALUE) VALUES ('end', 'end')");

}

---

Is there any way to check if two transactions are in the same physical connection?

Thanks for the reply!

Answer 1

It may use the same connection Each DB connection is bound to a thread or a transaction in spring. If no connections are bound to a thread/transaction then it will get a new connection from the connection pool.

TransactionSynchronizationManager class manages database connections with the help of the DataSource.

When you use Transaction then it's guaranteed that it will use the same database connection while if you're running SQL queries without transaction then it may or may not use the same database connection that depends on the connection availability in the connection pool.