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.

Answer 3

select ... for update is for row locking. This will cause other transactions that tries to do select ... for update for the already-locked rows (and each's foreign key referenced columns) to wait for the locking transaction to finish before continuing. Locking the concurrently updated resources should work for the bank balance update scenario, lock it upon update, and other business logic transactions modifying the same resource[s] using select ... for update will have to wait and take turns.

Serializable on the other hand prevent lost updates by handling concurrent transactions commits validity as if they were commited in sequence, therefore validating conflicting changes. For the bank transfer deduction case, with PostgreSQL, the first commiting transaction will win, and the other commiting transactions with updates on the same balance will fail upon commit. Handling the failing commit will be the responsibility of the client code.

Personally i prefer using Serializable combined with idempotent auto-retry upon failing conflicting changes, and then check the update count to know whether it's a successful update or not, and then handle appropriately. It's simpler than having to remember to use for lock in several places, and also the fact that locking could produce a lot of waiting transactions that could lead to increased database load.

Answer 4

If you use for update, you specify which rows to lock. If you have multiple read operations, you can lock rows that belong to a specific read operation within a transaction. On the other hand, serializable always locks rows from being read concurrently