Serializable Transactions and SELECT FOR UPDATE

Question

I read different transaction isolation levels and switched to SERIALIZABLE isolation SERIALIZABLE . I also know that databases such as Postgres, Oracle, and MySQL support SELECT .. FOR UPDATE syntax.

However, I am confused how they should be used when I would like to block the row (or row of rows) that I want to execute.

When using JPA in the past, I always used @Transactional in combination with LockModeType.PESSIMISTIC_WRITE in the request. This means using the READ_COMMITTED isolation READ_COMMITTED with SELECT .. FOR UPDATE in SQL.

But now, after reading about SERIALIZABLE , I wonder what would change if I used @Transactional(isolation=SERIALIZABLE) with a regular SELECT (like em.findById to retrieve a single object), followed by UPDATE (object union).

Will the behavior be the same?

Say, for example, I have a banking system, and I want to transfer money between two accounts. I demand that these accounts do not interfere while the transfer is in progress. So, suppose I debit one account at -100 and transfer it to another account. What would be the best way to ensure that these accounts are only available for the transaction performing the upgrade?

Suppose I manage individual JPA objects, so before upgrading I will have to read them from the database, for example. 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 in the case of SELECT FOR UPDATE, you can choose what and when you block.

So, if you want to lock some data, i.e. BankAccount, but not others, i.e. Branch, AccountTypes, then SELECT FOR UPDATE gives you much better control, where SERIALIZABLE locks your entire system, because every transaction selected from the ACCOUNT_TYPES table, In addition, some transactions may just want to check the balance, so there is no need to lock the ACCOUNT table.

Cm,

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

Answer 2

It seems to me that SERIALIZABLE cannot work in this BUSINESS transaction, because you need to check some conditions after selecting an object (for example, if the account has enough money). Concurrent transactions may receive the wrong value with the SERIALIZABLE level because it contains a read lock for SELECT.

But SELECT ... FOR UPDATE will work correctly, because it will hold an exclusive lock until the end of the transaction and cause other transactions to wait.