I'm running into some deadlocks issues. I have this structure accounting |---movements
To know the balance of the account, I usualy do a sum(movements.amount) where accounting.id=someid The issue is that the sum is starting to run very slow due hardware constraints, and I can't trow more hardware :(, so I need to find a software solution My approach was to create a balance field inside accounting, but I'm running into deadlocks, because accounting has a tree structure based upon accounting.id and accounting.parentid. Btw, I can't use triggers or store procedure due <insert management reason here>. Out of discussion. So, to have all the accounts in balance, I do a drill down to get all paretns, grandparent from an account... So I think, I have race conditions and multiversioning issues. I read about innodb locking and decided to use lock in share mode... But I'm runing with some deadlocks: TRANSACTION 0 264994, ACTIVE 1 sec, process no 5031, OS thread id 140061201196816 starting index read mysql tables in use 1, locked 1 LOCK WAIT 151 lock struct(s), heap size 30704, 36974 row lock(s) MySQL thread id 62743, query id 35566790 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '-3961.30' WHERE `accountid` ='408' LIMIT 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264994 lock_mode X locks rec but not gap waiting Record lock, heap no 19 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 00000198; asc ;; 1: len 6; hex 00000001b314; asc ;; 2: len 7; hex 000000003b0b18; asc ; ;; 3: len 4; hex 00000194; asc ;; 4: len 1; hex 02; asc ;; 5: len 20; hex 414e4149535f5a41495a41525f414c434152415a; asc LOANS;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8fb56d; asc m;; 11: len 1; hex 00; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 7ffffffffffff086e1; asc ;; *** (2) TRANSACTION: TRANSACTION 0 264995, ACTIVE 1 sec, process no 5031, OS thread id 140061201999632 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 4360 lock struct(s), heap size 456688, 579126 row lock(s) MySQL thread id 60636, query id 35566800 localhost 127.0.0.1 vortex Updating UPDATE `account` SET `balance` = '1.31' WHERE `accountid` ='11009' LIMIT 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 154 page no 11 n bits 288 index `PRIMARY` of table `management`.`account` trx id 0 264995 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 4; hex 00000001; asc ;; 1: len 6; hex 000000040aba; asc ;; 2: len 7; hex 000000003c1b0a; asc < ;; 3: SQL NULL; 4: len 1; hex 01; asc ;; 5: len 6; hex 41435449564f; asc ACTIVO;; 6: len 1; hex 31; asc 1;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 1; hex 01; asc ;; 10: len 3; hex 8faa7d; asc };; 11: len 1; hex 01; asc ;; 12: len 1; hex 80; asc ;; 13: len 9; hex 7ffffffff8f72f3efa; asc /> ;; Any clues about how to avoid this?
