grails中的并发事务导致数据库陈旧状态异常 [英] concurrent transaction in grails resulting in database stale state exception

问题描述

以下是我在api调用中运行的代码。下面的代码是在默认情况下是事务性的grails服务。但即使在锁定行之后，我也收到以下错误： Message：行被另一个事务更新或删除（或未保存的值映射不正确）。数据库中只有一个计划，因此for循环只运行一次。但是存在对api的并发调用， error.Please帮我解决这个问题

  def updateAllPlans（def user，def percentComplete，def chapterId）{
 def plan = Plan.findAllWhere（user：user）
 def chapter = Chapter.findById（chapterId）
（def plan：plans）{
 def foundChapters = plan.psubject.ptopics.psubtopics.pchapters .chapter.flatten（）。contains（chapter）
 if（foundChapters）{
 plan.lock（）
 if（percentComplete =='0'）{
 plan.durationViewComplete = plan.duration ViewComplete.plusMillis（chapter.duration.getMillisOfSecond（））
 plan.durationViewComplete = plan.durationViewComplete.plusSeconds（chapter.duration.getSecondOfMinute（））
 plan.durationViewComplete = plan.durationViewComplete.plusMinutes（chapter。 duration.getMinuteOfHour（））
 plan.durationViewComplete = plan.durationViewComplete.plusHours（chapter.duration.getHourOfDay（））
} else {
 plan.durationComplete = plan.durationComplete.plusMillis（chapter .duration.getMillisOfSecond（））
 plan.durationComplete = plan.durationComplete.plusSeconds（chapter.duration.getSecondOfMinute（））
 plan.durationComplete = plan.durationComplete.plusMinutes（chapter.duration.getMinuteOfHour（） ）
 plan.durationComplete = plan.durationComplete.plusHours（chapter.duration.getHourOfDay（））
} 
 
 plan.save（flush：true，failOnError：true）
} 
} 
} 
  

解决方案

您只在计划被读取后锁定计划。所以多个线程可以同时读取计划。然后一个线程锁定并更新它，另一个线程锁定它并更新它。但它们都同时读取它们，所以它们都读取相同的数据，其中包含相同的版本：

 线程1：读取计划，版本= 3 
线程2：读取计划，版本= 3 
线程1：锁定计划
线程1：更新和保存计划。版本变成4 
线程2：锁定计划
线程2：更新和保存计划。内存中的版本是3，但数据库中的版本是4，因此引发异常
  

您需要阅读时锁定（这是悲观锁定），记录在案：

  def airport = Airport.findByName（Heathrow，[lock：true]）
  
 
 
 
然后第二个线程必须等到第一个线程在读取相同的计划之前保存并提交它的事务。
 
 
 
这虽然具有悲观锁定的所有缺点：吞吐量可能会降低，因为一次只有一个事务可以使用计划，这正是乐观锁定试图避免的原因。成本是你可以得到例外，并且必须处理它们（通过重试，显示错误信息，或根据情况得到的最佳答案）
following is my code that is run on an api call.The below code is in a grails service which are transactional by default.But even after locking the row,I am getting this error : Message: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect).There is just one plan in database so for loop is run just once.but there are concurrent calls to api which are causing error.Please help me fix this
def updateAllPlans(def user,def percentComplete,def chapterId){
        def plans = Plan.findAllWhere(user:user)
        def chapter = Chapter.findById(chapterId)
        for(def plan:plans){
                def foundChapters = plan.psubject.ptopics.psubtopics.pchapters.chapter.flatten().contains(chapter)
                if(foundChapters){
                    plan.lock()
                    if(percentComplete=='0'){
                        plan.durationViewComplete = plan.durationViewComplete.plusMillis(chapter.duration.getMillisOfSecond())
                        plan.durationViewComplete = plan.durationViewComplete.plusSeconds(chapter.duration.getSecondOfMinute())
                        plan.durationViewComplete = plan.durationViewComplete.plusMinutes(chapter.duration.getMinuteOfHour())
                        plan.durationViewComplete = plan.durationViewComplete.plusHours(chapter.duration.getHourOfDay())
                    }else{
                        plan.durationComplete = plan.durationComplete.plusMillis(chapter.duration.getMillisOfSecond())
                        plan.durationComplete = plan.durationComplete.plusSeconds(chapter.duration.getSecondOfMinute())
                        plan.durationComplete = plan.durationComplete.plusMinutes(chapter.duration.getMinuteOfHour())
                        plan.durationComplete = plan.durationComplete.plusHours(chapter.duration.getHourOfDay())
                    }

                    plan.save(flush:true, failOnError:true)
                }
        }
    }

解决方案
You only lock the plan after it has been read. So multiple threads can read the plan concurrently. Then one thread locks it and updates it, and the other thread locks it and updates it as well. But they both read it concurrently, so both read the same data, with the same version inside:
thread 1: read plan, with version = 3
thread 2: read plan, with version = 3
thread 1: lock plan
thread 1 : update and save plan. version becomes 4
thread 2 : lock plan
thread 2 : update and save plan. version in memory is 3, but version in database is 4, so an exception is thrown
You need to lock when reading (which is pessimistic locking), as documented:
def airport = Airport.findByName("Heathrow", [lock: true])
Then the second thread will have to wait for the first one to have saved and committed its transaction before reading the same plan.

This has all the disadvantages of pessimistic locking though: the throughput might be reduced because only one transaction at a time can use the plan, which is precisely what optimistic locking tries to avoid. The cost is that you can get exceptions and have to deal with them (by retrying, displaying an error message, or whatever is the best answer depending on the situation)

                        
这篇关于grails中的并发事务导致数据库陈旧状态异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！