For anyone who's ever done something like the following...



The above can result in your application updating stale data, because refresh() retrieves the account before the customer, i.e.



So at the point where the account is retrieved your application hasn't obtained an exclusive lock and the data may therefore be stale. Locking the parent entity to update a child isn't something you're likely to do on a daily basis but occasionally it's useful (e.g. if you've already obtained a lock on the parent earlier in the call stack). Anyway, the above is definitely not the way to do it!

Can I just ask what is the best method of doing this?

Database transactions just aren't enough [with some databases] when dealing with concurrency especially when a limit needs to be checked (such as 'number of items left'). There needs to be some sort of synchronisation/thread locking.

How have people solved this?

The only way I found to safely implement pessimistic locking with hibernate was to lock the entity, then refresh it, e.g.

session.lock(customer, LockMode.UPGRADE)
session.refresh(customer)


Even then you're likely to get deadlocks, since with lock statements splattered throughout your code it's hard to guarantee your entities will be locked in a consistent order. i.e.

ThreadA -> lock(customer1)
ThreadB -> lock(customer2)
ThreadA -> lock(customer2)
ThreadB -> lock(customer1) !!! DEADLOCK !!!

To get around this problem I encapsulated the lock / refresh lines in a lock manager and made all my entities implement comparable. The lock manager tracked which entities had been locked within the current session (using a spring Custom Scope) and threw an exception in dev / logged a warning in prod if it was ever asked to lock an entity out of sequence. By doing this we were able to detect all "explicit" deadlock scenarios, but we still had problems with "implict" deadlocks - when Postgres secretly aquires a share lock before inserting into a table with foreign key constraints. The only way of detecting these was through concurrency testing.