0

I have spring-data-jpa application with postgreSQL database. I get some entity from the database, change it according to the business logic, save the entity back to the database. But periodically, when several requests for calling this functionality come in at the same time, I get this error:

org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    ...
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at jdk.proxy2/jdk.proxy2.$Proxy255.findForUpdateByUserId(Unknown Source)
    at com.life.adapter.out.jpa.JpaStatusPortImpl.findForUpdateByUserId(JpaStatusPortImpl.kt:25)

As I understand it, this error is related to the presence of @Lock(LockModeType.PESSIMISTIC_WRITE) over the repository method, but I don’t really understand why it happens. Shouldn’t the transaction with AA try to wait for the previous transaction to complete and try to execute again? How can I avoid this error?

My repository:

import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Lock
import org.springframework.stereotype.Repository
import javax.persistence.LockModeType

@Repository
interface StatusRepository : JpaRepository<StatusEntity, UUID> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    fun findForUpdateByUserId(userId: String): StatusEntity?
}

A logic of my method:

@Transactional(isolation = Isolation.REPEATABLE_READ)
fun upsert(userId: String) {
    val found = statusRepository.findForUpdateByUserId(userId) // get some entity from the database
    // some business logic
    val saved = statusRepository.saveAndFlush(found) // save the entity back to the database
    return saved
}
NeverSleeps
  • 1,439
  • 1
  • 11
  • 39
  • Please see this: [Postgres could not serialize access due to concurrent update](https://stackoverflow.com/questions/50797097/postgres-could-not-serialize-access-due-to-concurrent-update). – Denis Kiryanov Aug 08 '23 at 08:16
  • Thank you, I saw this question. But it seems that there the solution is to either use `READ COMMITTED`, or wrap everything in `try-catch`. Maybe there is another way? – NeverSleeps Aug 08 '23 at 08:35

0 Answers0