Recently I encountered a StaleObjectStateException which said “Row was updated or deleted by another transaction.” We were just updating multiple objects of the same domain inside a loop. So we used executeUpdate statement instead of myClass.save() method to update the records, which worked.

ReserveAccount.executeUpdate("update ReserveAccount set currentBalance=(:balance) where id=(:id)", [balance: currBalance, id: reserveAccount.id])

Later we also needed to update multiple objects of the same domain with same values. But grails executeUpdate statement doesn’t support list as a parameter. So here on the mailing list I found the solution

AccountTransactionStatus.withSession {session ->
      session.createQuery("""update AccountTransactionStatus t set t.thru=(:today) where
                            t.statusFor in (:selectedList)""")
			     .setParameterList('selectedList', transactions)
			     .setParameter('today', new Date()).executeUpdate()
    }

Now the concern was to retain my service as transactional since with executeUpdate statement in there, it couldn’t be transactional anymore. So then we used withTransaction on the parent domain as a block to sorround the executeUpdate statement and along with other statements, which worked.

 Servicer.withTransaction {
	myMap.each {servicerId, amount ->
	      def servicer = Servicer.read(servicerId)
	      BigDecimal currBalance = servicer.reserveAccount.currentBalance
	      currBalance -= amount
	      ReserveAccount.executeUpdate("update ReserveAccount set currentBalance=(:balance) where id=(:id)", [balance: currBalance, id:
		 servicer.reserveAccount.id])
    }
 }

And other solution to handle StaleObjectStateException exception is given here in the mailing list

Hope it helped!

~~Amit Jain~~
amit@intelligrape.com

http://www.intelligrape.com