I have a webapp that uses session-per-request strategy and executes two operations that update the same db table. At higher loads one of two requests infrequently fails with StaleObjectStateException. This behavior seems correct, but I want to make sure the exception does NOT mean that two requests are inadvertently sharing the same Session, thereby breaking session-per-request strategy. So, is the following possible?:

Given:
Long Operation A:
-load from DB
-mod persistent object
-commit

Short Operation B:
-load from DB
-mod same persistent entity
-commit:


Can the following happen given that MySQL DB is in Repeatable Read mode:

1.Session(A) starts
2.Op A reads entity as Obj1

3.Session(B) starts
4.Op B reads same entity as Obj1
5.Op B mods entity
6.Session (B) commits Obj1(b)

7.Op A mods entity Obj1 as Obj1(a)
8.Session (A) tries to commit Obj1(a) and is denied because up-to-date entity is now Obj1(b)

Is the above scenario possible? I thought once Session(A) reads entity as Obj1 the table's locked until Session(A) terminates. If not, is there any way to make Session(B) block on select until Session(A) is done?


thanks,
-nikita

Hibernate version:
3.1.3
Name and version of the database you are using:
MySQL 5.0, InnoDB, repeatable read tx isolation

The table is not locked if you've only done a read. If you did the read with LockMode.UPGRADE, which turns "select xxx from ..." into "select xxx for update from ...", then you would lock the table, and that will cause session B to block, like you want.

Unfortunately, not every DBMS suppoorts select ... for update. I don't believe that MySQL does.

Thanks, tenwit!

So does that mean that if my app can have multiple threads touching the sam table row, I *will* see StaleObjectStateExceptions and therefore have to have logic to rollback/retry on those? in other words, there is no way to *guarantee* that that exception will never appear?

thanks
-nikita

seems that mysql 5.0 does support "select for update":
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html


Interestingly, the first user comment on that page seems to imply that even "select for update" will not guarantee thread safety. So seems like my app will still have to handle StaleObjectStateExceptions - except they should be appearing less frequently when "select for update" is used.

-nikita

Well, if you have write-often objects, it's best to refresh those before writing to them. Or do the opposite: write what your current thread sees, and ignore updates that happened current thread read whatever object it's about to save. Though the former option would usually be preferable to the latter.

Judicious use of transactions will have a greater impact on avoiding stale objects than "select for update". In the ref docs, the chapters on "Transactions and Concurrency" and "Improving Performance" (11 and 19, in the 3.1 ref docs) both have things to say on this matter.