JPA Writing from Multiple Process into Single DB

Question

So, I've got a design question: I have several processes running that are updating my database. As an example, imagine a banking system, where I'm recording transfers from one account to another. In this case, I have to subtract the transfer amount from the source account and credit the same amount into the destination account as a single transaction.

I'd like to make absolutely sure that there are no conditions where my transactions could be corrupted, so I was initially thinking of pessimistic locking of the source and destination accounts while I do the calculations and commit the changes. From what I've read on the web about JPA, it seems like pessimistic locking is not readily supported (at least not directly).

Can anyone give me an idea of other ways that I might be able to ensure the integrity of my transactions across multiple processes?

Any help/pointers greatly appreciated.

Thanks...

--Steve

Answer 1

The preferred method for locking in a system that uses detached persistence (e.g. JPA) is to use optimistic locking as it tends to reduce the amount of contention at the database level, allowing reads to continue while writes are occuring. The way to enable optimistic locking is to add a @Version annotation to a specific version field in your class.

@Entity
public class Account {

    @Id
    private long id;

    @Version
    private long version;

    @Basic
    private BigDecimal balance;
}

Then the JPA implementation will manage the version of you object for you. It is recommended that you leave this field private and don't specify getters or setters for it, as you want the JPA implementation to be singly responsible for this field. Your main consideration will then be what action to take when an OptimisticLockException is thrown (which is what happens if someone else has updated one of the accounts before you managed to save your changes.

Unfortunately there is no one solution to that problem, the simplest method is to reload the two accounts and retry the transaction, which will work fine if you do not have too many transactions accessing the same account at the same time. In this case you may run into issues where some requests spend the majority of their time retrying the transaction, however it's best to start with the simple solution then change the implementation as your application requires.