[rowls66]

To avoid deadlock, you need to ensure that locking happens in the same order in all concurrent transactions. So account A must be locked before account B and never in the opposite order. So if account B is the account being debited and therefore the account that needs to have a sufficient balance, then account A must be locked first. If account A is the account to be debited, then explicitly locking account B is not necessary. It will be locked when its updated.

[karmakaze]

I think I agree but was hard to parse the message. My summary would be to always lock A and B in the same order (e.g. smallest account number first) regardless of which is being reduced or increased. In addition, an UPDATE statement counts as lock so doesn't need a SELECT ... FOR UPDATE if it's the 2nd one that you want to do.

[CraigJPerry]

If transaction 1 and 2 are running the example and the ordering is something like:

    txn1 = db.begin()

    # implicit exclusive row lock established here on A row, exclusive rather than shared read because we say for update
    currBalance1 = txn1.query('select balance from accounts where name = A for update’)

    … <snipped some of the example code> …

    txn1.execute('update accounts set balance = balance - $amount where name = A')

    # Concurrently tx2 begins, tx1 has locked row A so far
    txn2 = db.begin()

    # we’re exclusively locking row B in txn2
    currBalance2 = txn2.query('select balance from accounts where name = B for update’)

    # This is the first point in transaction 1 where we take an exclusive lock on B, however we are deadlocked now
    txn1.execute('update accounts set balance = balance + $amount where name = B')

The solution is what the parent suggested, take the exclusive locks on rows A&B at the same time because if transaction 2 is locking B&C you can’t afford to do 2 separate for update calls because b might get locked as you’ve just locked A and were about to lock B

    select balance from accounts where name = 'A' or name = ‘B’ for update

[karmakaze]

When it comes to row locks, there isn't a same time. Which row gets locked first if left unspecified can lead to deadlocks. All I was suggesting was:

  select balance from accounts where name = 'A' for update
  select balance from accounts where name = 'B' for update

where the two statements are run with smaller 'name' first, or

  select balance from accounts where name = 'A' or name = ‘B’ order by name for update

Of course, I'd use the primary key (likely an id rather than name).

By locking the accounts in smallest->largest name (or id), you can afford to do 2 separate for update calls. Even when done in a single statement specify the canonical ordering of locks to avoid deadlocks. For example, 3 transactions locking (A, B), (B, C), (C, A):

  tx1: lock A, lock B
  tx2: lock B, lock C
  tx3: lock A, lock C

This is the lock order regardless of which of A/B/C is being debited or credited. As a final detail, make sure the ORDER BY that you specify is on a unique index or else there could/will be range locks on the index and won't strictly be using row locks.