mysqlpostgresqltransactionsisolation-leveltransaction-isolation

Transaction isolation level for financial transactions

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 FROM 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 an exclusive 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.

Solution

  • Your conclusion is correct, you need to explicitly take a lock to prevent problems. In other words, pessimistic locking.

    The statement you show with FOR UPDATE actually acquires an exclusive lock, not a shared lock. MySQL syntax uses FOR SHARE to acquire a shared lock (this used to be known as LOCK IN SHARE MODE in MySQL 5.x).

    In fact, you should also lock the destination row for B at the same time, to prevent deadlocks in case a transaction is transferring money from B to A concurrently.

    Locking is more important for this task than choosing between transaction isolation levels. This is why there's no clear answer when you ask "which transaction isolation level should I use?"