Upgrading from Spring Boot 1.5 to 2.0 - cannot execute UPDATE in a read-only transaction

Question

Updating Spring Boot 1.5 to 2.1.5

When trying to do a operation repository.save(entity) it gives the following error:

Caused by: com.impossibl.postgres.jdbc.PGSQLSimpleException: cannot execute UPDATE in a read-only transaction

We use the org.springframework.data.repositoryCrudRepository interface to perform the operations.

1) @Transactional(readOnly = false), as i understood setting Read-Only mode to false only works as a hint to the sub-layers, how can i check and change the other layers?

@Service
public class ServiceImpl

    private final Repository repository;

    @Autowired
    public ServiceImpl(Repository repository) {
        this.repository = repository;
    }
@Transactional(readOnly = false)
public void operation(Entity entity){
    repository.save(entity);
}

And Repository is

public interface Repository extends CrudRepository<Entity, UUID>{

    @Query("select e from Entity e where lower(u.name) = lower(?1)")
    Entity findByName(String name);

}

build.gradle
------------

`dependencies {
    classpath("org.springframework.boot:spring-boot-gradle-plugin:2.1.5.RELEASE")
}
`

```runtime("org.springframework.boot:spring-boot-properties-migrator")
    compile("org.springframework.boot:spring-boot-starter-security")
    compile("org.springframework.boot:spring-boot-starter-jersey")
    compile("org.springframework.boot:spring-boot-starter-web")
    compile("org.springframework.boot:spring-boot-starter-thymeleaf")
    compile("org.springframework.boot:spring-boot-starter-data-jpa")
    compile("org.springframework.boot:spring-boot-starter-jetty")
    compile("org.springframework.boot:spring-boot-starter-mail")
    compile("org.springframework.boot:spring-boot-starter-actuator")
    compile("org.quartz-scheduler:quartz:2.3.1")
    compile("com.fasterxml.jackson.dataformat:jackson-dataformat-xml")
    compile("com.fasterxml.jackson.datatype:jackson-datatype-jsr310")
    compile("com.fasterxml.woodstox:woodstox-core:5.2.1")
    compile("org.glassfish.jersey.media:jersey-media-multipart:2.28")
    compile("net.java.dev.msv:msv-core:2013.6.1")
    compile("com.impossibl.pgjdbc-ng:pgjdbc-ng:0.8.2")
    compile('org.apache.commons:commons-lang3:3.9')
    compile('commons-io:commons-io:2.6')
    compile('org.apache.commons:commons-compress:1.18')
    compile('org.apache.poi:poi-ooxml:4.1.0')
    compile('org.apache.xmlbeans:xmlbeans:3.1.0')
    compile('org.mitre.dsmiley.httpproxy:smiley-http-proxy-servlet:1.10')
    compile('com.monitorjbl:xlsx-streamer:2.1.0')
    compile('com.zaxxer:HikariCP:3.3.1')

application.properties

spring.datasource.driverClassName=com.impossibl.postgres.jdbc.PGDriver

spring.datasource.url=
spring.datasource.username=
spring.datasource.password=


spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.idle-timeout=10000

 # Set auto-commit = false, otherwise - Caused by: java.sql.SQLException: 
  Clobs require connection to be in manual-commit mode... 


spring.datasource.hikari.auto-commit=false

logging.level.ROOT=INFO
logging.level.org.springframework.orm.jpa=DEBUG
logging.level.org.springframework.transaction=DEBUG

One important thing is that i added the Auto-Commit to false in Hikari, otherwise it would fail with an exception as it can bee seen in the comment.

Note: In some threads it was suggested to check postgres connection

    show default_transaction_read_only;
     default_transaction_read_only 
    -------------------------------
     off

    SELECT pg_is_in_recovery();
     pg_is_in_recovery 
    -------------------
     f

Thanks in advance.

Answer 1

1. Property readOnly is false by default, so you should never use @Transactional(readOnly = false), use @Transactional instead.
2. When you mark some methods or class with @Trasnacional Spring creates a proxy of that class to inject the logic of Transaction Manager. It uses a bean that implements interface org.springframework.transaction.PlatformTransactionManager
3. In your specific case a bean of org.springframework.orm.jpa.JpaTransactionManager will be created.
4. Spring Boot is using Hibernate as a default JPA provider, so eventually all transaction logic will affect Hibernate. E.g. readOnly = true is used in order to disable "dirty checking" mechanism that performs all UPDATE operations in Hibernate.
5. By default, Spring Transaction Manager creates a new Hibernate Session (new transition) when it calls a method marker with @Transactional and no Session is attached to the current thread. So all the following calls in the current thread will use the same Session (and same transaction). Unless you change the propagation property.
6. It all means that configs for the transaction are set when Spring calls @Transactional method the first time and those configs are used for all methods calls in the same thread. See the code example:

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionSynchronizationManager;

@Service
public class ServiceA {

    @Transactional(readOnly = true)
    public void a() {
        boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        System.out.println(isReadOnly);
    }
}

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ServiceB {
    private final ServiceA serviceA;

    public ServiceB(ServiceA serviceA) {
        this.serviceA = serviceA;
    }

    @Transactional
    public void b() {
        serviceA.a();
    }
}

• serviceA.a() will print true
• serviceB.b() will print false

Answer 2

See Spring Boot 2.0 Migration Guide about Gradle and add dependency management plugin:

Spring Boot’s Gradle plugin no longer automatically applies the dependency management plugin. Instead, Spring Boot’s plugin now reacts to the dependency management plugin being applied by importing the correct version of the spring-boot-dependencies BOM. This gives you more control over how and when dependency management is configured.

For most applications applying the dependency management plugin will be sufficient:

apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management' // <-- add this to your build.gradle

Also you can remove spring.datasource.type

If you used spring.datasource.type to force the use of Hikari in a Tomcat-based application, you can now remove that override.

Notice also that minimum Hibernate version is 5.2

Also I see you added spring-boot-properties-migrator, note that it should be removed after you finish migration tweaks

Once you’re done with the migration, please make sure to remove this module from your project’s dependencies.