3 ms·

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 ne

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 3y ago
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 3y ago
  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 3y ago
    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.

karmakaze 3y ago

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 3y ago
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 3y ago
  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.