Serializable Transactions vs SELECT FOR UPDATE

Question

I was reading on the different transaction isolation levels, and came up across the SERIALIZABLE isolation level. I also know that databases such as Postgres, Oracle and MySQL support the SELECT .. FOR UPDATE syntax.

I am however confused how these should be used when I would like to lock a row (or a range of rows) which I wish to perform updates on.

When using JPA in the past, I always used @Transactional coupled with a LockModeType.PESSIMISTIC_WRITE on the query. This translates to using a READ_COMMITTED isolation level with a SELECT .. FOR UPDATE in SQL.

But now, having read about SERIALIZABLE, I'm wondering what would be different if I used @Transactional(isolation=SERIALIZABLE) with a normal SELECT (e.g. em.findById to fetch a detached entity), followed by an UPDATE (merge of the entity).

Would the behavior be the same?

Say for example, I have a Bank system, and I wish to transfer money between two accounts. I require these accounts not to be meddled with, while the transfer is in progress. So, suppose I debit one account with -100 and credit it into the other account. What would be the best way to ensure that these accounts are available only to the transaction performing the update?

Assume that I'm manipulating JPA detached entities, so prior to updating, I will have to read them from the DB, e.g. findById().

• Use @Transactional(isolation=READ_COMMITTED), em.findById with LockModeType.PESSIMISTIC_WRITE (i.e. SELECT .. FOR UPDATE), and then em.merge (i.e. UPDATE) ?
• OR Use @Transactional(isolation=SERIALIZABLE), em.findById, and then em.merge (i.e. UPDATE)?

Answer 1

The main difference between SERIALIZABLE and using SELECT FOR UPDATE is that with SERIALIZABLE everything is always locked. Where as with SELECT FOR UPDATE you get to choose what and when you lock.

So if you only want to lock some data, i.e. BankAccount but not other, i.e. Branch, AccountTypes, then SELECT FOR UPDATE gives you much better control, where as SERIALIZABLE would block your entire system because every transaction selected from the ACCOUNT_TYPES table. Also, some transactions may just want to check the balance, so do not need to lock the ACCOUNT table.

See,

http://en.wikibooks.org/wiki/Java_Persistence/Locking

Answer 2

It seems to me that SERIALIZABLE can't work in this BUSINESS transaction, because you need to check some conditions after selecting an entity (for example, if an account has enough money). Concurrent transactions can get the wrong value with SERIALIZABLE level, because it holds shared (read) lock for SELECT.

But SELECT ... FOR UPDATE will work properly because it will hold an exclusive lock until the end of a transaction and will force other transactions to wait.