3

We have a Spring Boot application, that heavily relies on SimpleJpaRepository implementations for performing database CRUD operations.

Problem: The class is annotated with @Transactional, causing all methods called to be wrapped in an SQL transaction.

We would like our repositories to generate SQL transactions only for insert/update/delete operations, but not for select operations (unless they are called from another method annotated with @Transactional)

How can we setup our repositories to achieve this, and what what would be the less obvious effects? (I assume the class was annotated like this for a reason)

Code and Logs

Calling @Transactional method, expected 1 transaction, found 1 (OK)

@Transactional
public User readUser(int id){
    userRepository.findOne(id);
    roleRepository.findByUser(id);
}

SQL Profiler:

set implicit_transactions on 
exec sp_executesql N'select userentity0_.id as id1_45_, .....'
exec sp_executesql N'select roleassign0_.id as id1_36_, ....'
IF @@TRANCOUNT > 0 COMMIT TRAN

Without @Transactional, expected no transactions, found 2, created by the repositories (BAD):

public User readUser(int id){
    userRepository.findOne(id);
    roleRepository.findByUser(id);
}

SQL Profiler:

set implicit_transactions on 
exec sp_executesql N'select userentity0_.id as id1_45_, .....'
IF @@TRANCOUNT > 0 COMMIT TRAN

set implicit_transactions on 
exec sp_executesql N'select roleassign0_.id as id1_36_, ....'
IF @@TRANCOUNT > 0 COMMIT TRAN
Alexandru Severin
  • 6,021
  • 11
  • 48
  • 71
  • Do you call those methods from class or method, annotated with transactional? You can test it with annotate one of those methods with transactional and set the propagation level to `NEVER `. Then it will throw an exception if you call it from transaction. – zlaval Jul 03 '20 at 18:01
  • I don't want it to throw an exception. I want it to simply not create an implicit transaction for every query we make. Added detailed logs – Alexandru Severin Jul 15 '20 at 15:24
  • How is your repository methods implemented? Do they use spring data repos? – Kavithakaran Kanapathippillai Jul 15 '20 at 22:46
  • Do you simply want no transactions at all for non-annotated methods? – Xaero Degreaz Jul 16 '20 at 01:11
  • @KavithakaranKanapathippillai all repositories implement `SimpleJpaRepository` from spring-data-jpa – Alexandru Severin Jul 16 '20 at 07:19
  • @KavithakaranKanapathippillai, your comment made me realize all my repositories extend `SimpleJpaRepository`, which is annotated with `Transactional` and is causing all the unwanted transactions for every method such as findAll, findOne, etc. I will update my question accordingly – Alexandru Severin Jul 16 '20 at 07:57
  • Yes. That is why I wanted to clarify. Implemented methods in `SimpleJpaRepository` have Transactional annotation. – Kavithakaran Kanapathippillai Jul 16 '20 at 08:04
  • But `findBy` methods in `SimpleJpaRepository` does not have `@Transactional` annotation. My suspicion is even if spring data does not wrap it, hibernate will wrap it in transaction. I will experiment a bit more when I get time – Kavithakaran Kanapathippillai Jul 16 '20 at 08:15
  • @KavithakaranKanapathippillai findBy methods indeed do not have `@Transactional`, but if the class has `@Transactional`, all methods in the class also have it. The transaction disapears if I override the method and annotate the concrete class with `@Transactional(propagation=SUPPORTS` – Alexandru Severin Jul 16 '20 at 08:40
  • I missed the class level annotation. It is the only way you get around with by redefining all the methods but that is a lot of work – Kavithakaran Kanapathippillai Jul 16 '20 at 08:42
  • @KavithakaranKanapathippillai, redefining all the methods is not a problem. Took me a few minutes thanks to IntelliJ's refactoring capabilities. Now I'm more interested in why the annotation exists on the class and what am I breaking by overriding the intended behavior – Alexandru Severin Jul 16 '20 at 09:56

2 Answers2

2

  1. By default, CRUD methods on repository instances are transactional. For read operations, the transaction configuration readOnly flag is set to true.

  2. So your option is to redefine findBy methods with @Transactional(propagation=SUPPORTS)

  3. You are not breaking anything by doing so but disabling some optimisations. See the answer by Oliver Gierke - the Spring Data author

Reading methods like findAll() and findOne(…) are using @Transactional(readOnly = true) which is not strictly necessary but triggers a few optimizations in the transaction infrastructure (setting the FlushMode to MANUAL to let persistence providers potentially skip dirty checks when closing the EntityManager). Beyond that the flag is set on the JDBC Connection as well which causes further optimizations on that level.

Kavithakaran Kanapathippillai
  • 7,883
  • 1
  • 11
  • 29
  • I assume if you have to do multiple `findBy` operations, to get repeatable read, you will wrap them in `@Transactional(readOnly=true)` in your service layer – Kavithakaran Kanapathippillai Jul 16 '20 at 10:48
  • A tad simpler solution is to use `@EnableJpaRepositories(enableDefaultTransactions = false)`. It disables begin/commit SQL statements, but still bind an instance of `TransactionInfo` to the current thread (see last statement of `TransactionAspectSupport.prepareTransactionInfo()`) so no _javax.persistence.TransactionRequiredException: no transaction is in progress_ exception is thrown when calling a method without wrapping transaction. – Siggen Mar 16 '23 at 10:42
0

Have you tried annotating the desired methods with

@Transactional(propagation = Propagation.NOT_SUPPORTED)

Execute non-transactionally, suspend the current transaction if one exists. Analogous to EJB transaction attribute of the same name.

Docs here

Xaero Degreaz
  • 1,045
  • 11
  • 18
  • This would suspend an already existing explicit transaction created by `@Transactional` and I don't want that. I only want to stop implicit transactions – Alexandru Severin Jul 16 '20 at 07:23