Discuss about the optimization of read-only transactions [DATAJPA-1210]

[spring-projects-issues]

Yang Lifan opened DATAJPA-1210 and commented

I know there already has an issue about this topic https://jira.spring.io/browse/DATAJPA-601. But I want to mention this again since after I studied Spring, Hibernate and JDBC sourcecode and MySQL document, then I think that add propagation.SUPPORTS on a read-only method is necessary, especially when working with MySQL.

We all know that the way JDBC begins a transaction is to set autocommit=0. And when use MySQL, there are two ways to use read-only transaction:

1. Call START TRANSACTION READ ONLY
2. Set autocommit=1 and execute select statement, then MySQL will do the optimization automatically.

So according to statements mentioned above, using @Transactional(readonly = true) will not begin a read-only transaction in MySQL. The only way to let MySQL to begin a read-only transaction is to set propagation = SUPPORTS. Since this will set autocommit=1 and let MySQL has a chance to do the optimization.

So if propagation = SUPPORTS does not have any negative affects to other non-MySQL DBs. I think Spring Data JPA should set @Transactional(propagation = SUPPORTS) by default to optimize read-only DB operations

---

Affects: 2.0 GA (Kay)

Issue Links:

• DATAJPA-601 Improve performance with Propagation.SUPPORTS for readOnly operation
  ("duplicates")

[spring-projects-issues]

Mark Paluch commented

That's a duplicate of an already closed ticket. You're describing an optimization for a specific database which can be handled best inside your own code by either annotating your services or your repositories with @Transactional and setting the corresponding attributes