Let’s assume we have an application that must transfer funds from account A to account B.
Let’s assume that database is MySQL (though I’d appreciant an answer for Postgres too).

Account A balance: 20
Account B balance: 0

Transaction 1 (TX1): transfer 10 from A to B
Transaction 2 (TX2): transfer 15 from A to B

TX1 and TX2 happen simultaneously.

Now, this is program logic steps:

1. Read A and B balance
2. Validate it’s more than transfer amount
3. Save updates. For example in transaction 1 updates are:

    update BALANCES set balance = balance - 10 where id = A
    update BALANCES set balance = balance + 10 where id = B

Questions:

1. What transaction isolation level should I use for this transaction?

2. Is transaction isolation level enough or I must explicitly use pessimistic locking?

Let me elaborate. Taking for example SERIALIZABLE level in MySQL, when you read a record within a transaction, record gets locked with shared lock.
A shared lock does not prevent other transactions from placing a shared lock and reading the data.

Effectively, both transactions get a shared lock on A and B, then TX1 tries to make an update and receive an exclusive lock. It can’t do it as TX2 already holds a shared lock.
So we get a deadlock: one of the transactions will get rolled back, and we will have to hang for a while (default is 30 or more seconds).

From where I see, a money transfer like this is not implementable with SERIALIZABLE isolation alone. Instead, it looks like we can go with READ_COMMITTED but explicitly request a shared lock right at the moment of data reading: “select * from BALANCES where id = A FOR UPDATE NOWAIT;”. Hence, no other transaction can obtain shared locks on records hence preventing a deadlock.

Please help me understand this.