Search code examples
postgresqlmybatispessimistic-lockingselect-for-update

Why PostgreSQL throws concurrent update error while SELECT FOR UPDATE SKIP LOCKED?

My Java application interacts with PostgreSQL via MyBatis.

From multiple threads it executes this request

  select * 
  from v_packet_unread
  limit 1000
  for update skip locked

and sometimes gets ERROR: could not serialize access due to concurrent update. As I remember this error occurs in case of optimistic update, and here I use just SELECT, not even an UPDATE, and cannot explain what is going on.

v_packet_unread - is a simple view joining two small tables (2 columns per each) without any hidden effects (like triggers of function calls).

Could you help me to find out the reason of this behavior and how to avoid that?

Exception:

2021-07-16 06:31:39.278 [validator-exec-5     ] [ERROR] r.c.p.Operators - Operator called default onErrorDropped
reactor.core.Exceptions$ErrorCallbackNotImplemented: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update
### The error may exist in database/schemas/receiver/map/PacketMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select *     from v_packet_unread     limit ? for update skip locked
### Cause: org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update
### The error may exist in database/schemas/receiver/map/PacketMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select *     from v_packet_unread     limit ? for update skip locked
### Cause: org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
    at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
    at jdk.proxy2/jdk.proxy2.$Proxy65.selectUnread(Unknown Source)
    at ...
Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at org.apache.ibatis.executor.BatchExecutor.doQuery(BatchExecutor.java:92)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
    ... 25 common frames omitted

Versions:

PostgreSQL 12.5 on x86_64-redhat-linux-gnu, 
                compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


dependencies:
  org.mybatis:mybatis:3.5.7 
  org.postgresql:postgresql:42.2.20
Solution

  • That can happen if you are running in a transaction with isolation level REPEATABLE READ or above: if you try to lock a row that has been modified concurrently by a different transaction since your transaction started, you will get a serialization error.

    To avoid that, use the default READ COMMITTED isolation level.