Re: How to get transaction started always in WRITE mode.

On Tue, 2017-07-25 at 11:32 +0200, Adam Šlachta wrote:
> Hello,
>  
> In short: Is there any way how to setup PostgreSql 9.6 to always
> start a transaction in WRITE mode?
>  
> Our related configuration:
> "default_transaction_isolation" --> "read committed"
> "default_transaction_read_only" --> "off"
>  
>  
> Longer description (for those who are interested, since it is not
> only PostgreSQL related):
>  
> We are facing problems with "cannot execute <UPDATE/INSERT/DELETE> in
> a read-only transaction" exception
> (org.postgresql.util.PSQLException).
> It is very likely the problem is caused by our code, however at the
> moment the fastest solution before we solve the things properly would
> be to setup WRITE mode for all started transactions on a database-
> setup-level.
>  
> SW we use:
> -> Java 8
> -> Hibernate 5.1.2
> -> spring-data-jpa 1.10.4.RELEASE
> -> spring-beans, spring-core, other spring stuff of version
> 4.2.8.RELEASE
>  
> Related configuration (I don't want to spam here with long list of
> configuration files so I pick-up what I consider important):
> Hibernate -> first & second level cache switched OFF
> SessionFactory ->
> org.springframework.orm.hibernate5.LocalSessionFactoryBean
> transactionManager ->
> org.springframework.orm.jpa.JpaTransactionManager
> Spring @Transactional(read-only) hint -> where we could we set it to
> "false"
> Our typical @Repository extends
> org.springframework.data.jpa.repository.JpaRepository, which uses
> implementation from
> org.springframework.data.jpa.repository.support.SimpleJpaRepository.
>  
> Thank you very much for any hints.
> Adam Slachta
>
>

Hello Adam,

There is a Hibernate parameter that overrides the database's default
isolation level:-

hibernate.connection.isolation

Are you certain that second level caching is off?
You can make a class in a cache immutable by:-

<cache usage="read-only"/>

Don't know if that helps but your problem seems to be a configuration
issue in Hibernate.

Cheers,
Rob