The smoothest way to avoid deadlocks, is to ensure that all your sessions lock their tables in exactly the same order. From your explanation, that might not be as easy as one would expect, though.
If you can't create triggers, is it acceptable to have delayed updates on the totals? Your idea was good, but the classic way to go about that is materialized views - in this case also known as aggregate tables. You simply run a cron job every hour or whatever that drops and recreates a (temporary) table holding all the summaries you'll ever need. Sure, the build job is heavy - but it only runs once every so often; and building an aggregate of ten subset is not as heavy as running the aggregate query for every subset separately, so there's a definite win. If you can live with 24h old data, just run the aggregate build somewhere during the slow hours. ----- Original Message ----- > From: "Andrés Tello" <mr.crip...@gmail.com> > To: "Mailing-List mysql" <mysql@lists.mysql.com> > Sent: Wednesday, 20 April, 2011 9:15:09 PM > Subject: How to avoid deadlocks.. advice needed also insight, illumination > and a bit of hallucination... > > I'm running into some deadlocks issues. > > I have this structure > accounting > > 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? > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
