Optimistic locking and stored procedures

[graeder]

Can someone give me some insight as to how I might be able to leverage Spring to obtain an optimistic locking concurrency strategy using stored procedures? I'm kind of a newbie when it comes to stored procedures.

[karldmoore]

Could you do this through the isolation level set on the transaction and a version id field.
http://www.hibernate.org/hib_docs/v3...ons-optimistic
http://peter.jteam.nl/?p=12

[graeder]

It seems like manual version checking would work. I'm curious if hibernate's automatic versioning feature would work with stored procs - any insight there?. We could also implement a trigger at the database level to do a compare at time of update.

[karldmoore]

I'm not sure about Hibernate and stored procedures. It doesn't really seem to have that much support. A trigger is an interesting idea, I guess you could just give it a go and see how it works out.

[gmatthews]

Remember to evict the Hibernate object out of your cache too, to avoid cache inconsistencies.

[karldmoore]

If you change values outside of Hibernate you could have real issues with a cache as it won't be aware of the changes. I think this has been raised a few times before on the forum.

[gmatthews]

I guess what we're all saying is, you'll be entering a world of pain if you use stored procedures.

If you do have to, you'll need to do a combination of:
- turning off 2nd level caching, and/or
- manually evicting objects from 1st + 2nd level cache after a stored proc call, and
- remembering to do a manual flush before calling a stored proc so that the stored proc can see changes made (if required).

[karldmoore]

In a word yes