首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >为什么PostgreSQL在选择update跳转锁定时抛出并发更新错误?

为什么PostgreSQL在选择update跳转锁定时抛出并发更新错误?
EN

Stack Overflow用户
提问于 2021-07-16 07:09:04
回答 1查看 1.2K关注 0票数 1

我的PostgreSQL应用程序通过MyBatis与MyBatis进行交互。

从多个线程执行此请求。

代码语言:javascript
运行
复制
  select * 
  from v_packet_unread
  limit 1000
  for update skip locked

有时还会得到ERROR: could not serialize access due to concurrent update。正如我所记得的,这个错误发生在乐观更新的情况下,这里我只使用SELECT,甚至不是更新,无法解释发生了什么。

v_packet_unread -是一个简单的视图,它连接两个小表(每个表2列),没有任何隐藏的效果(比如函数调用的触发器)。

你能帮我找出这种行为的原因以及如何避免吗?

异常:

代码语言:javascript
运行
复制
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

版本:

代码语言:javascript
运行
复制
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
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-07-16 08:16:17

如果在具有隔离级别REPEATABLE READ或更高级别的事务中运行,则可能发生这种情况:如果您试图锁定自事务启动以来由不同事务并发修改的行,则会得到序列化错误。

要避免这种情况,请使用默认的READ COMMITTED隔离级别。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/68404870

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档