CODEWITHSUNDEEP
javaspringhibernatejpatransactions
ThinkFloyd
ThinkFloyd

Reputation: 5021

How to avoid StaleObjectStateException when transaction updates thousands of entities?

We are using Hibernate 3.6.0.Final with JPA 2 and Spring 3.0.5 for a large scale enterprise application running on tomcat 7 and MySQL 5.5. Most of the transactions in application, lives for less than a second and update 5-10 entities but in some use cases we need to update more than 10-20K entities in single transaction, which takes few minutes and hence more than 70% of times such transaction fails with StaleObjectStateException because some of those entities got updated by some other transaction.

We generally maintain version column in all tables and in case of StaleObjectStateException we generally retry but since these longs transactions are anyways very long so if we keep on retrying then also I am not very sure that we'll be able to escape StaleObjectStateException.

Also lot of activities keep updating these entities in busy hours so we cannot go with pessimistic approach because it can potentially halt many activities in system.

Please suggest how to fix such long transaction issue because we cannot spawn thousands of independent and small transactions because we cannot afford messed up data in case of some failed & some successful transactions.

Upvotes: 1

Views: 1236

Answers (1)

Johanna
Johanna

Reputation: 5293

Modifying 20,000 entities in one transaction is really a lot, much more than normal.

I can't give you a general solution, but here are some ideas how to solve the problem.

1) Use LockMode.UPGRADE (see pessimistic locking). There you explicitly generate a "SELECT FOR UPDATE", which stops other users to modify the rows while they are locked. This should avoid your problem, but if you have too many large transactions it can produce deadlocks (depending of your programming) or timeouts.

2) Change your data model to avoid these large transactions. Why do you have to update 10,000 rows? Perhaps it is possible to put this information, which is updated in so many rows, into a new table and let it be referenced only, so you have to update only a few rows in the new table.

3) Use StatelessSession instead of Session. In this case you are not forced to rollback after an exception, instead you can correct the problem and continue (in your case reload the entity which was modified in meantime and do the modifcation for the large transaction on the reloaded entity). This perhaps give you the possibility to handle the critical event (row modified in meantime) on a row to row basis instead for the complete large transaction.

Upvotes: 1

Related Questions