From: Johan De Meersman Date: April 20 2011 8:20pm Subject: Re: How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination... List-Archive: http://lists.mysql.com/mysql/224834 Message-Id: <346ff6f7-735d-4606-a7a2-a677d24e5829@zimbra> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable The smoothest way to avoid deadlocks, is to ensure that all your sessions l= ock their tables in exactly the same order. From your explanation, that mig= ht not be as easy as one would expect, though. If you can't create triggers, is it acceptable to have delayed updates on t= he totals? Your idea was good, but the classic way to go about that is mate= rialized views - in this case also known as aggregate tables. You simply ru= n 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 bu= ilding 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 li= ve with 24h old data, just run the aggregate build somewhere during the slo= w hours. ----- Original Message ----- > From: "Andr=C3=A9s Tello" > To: "Mailing-List mysql" > Sent: Wednesday, 20 April, 2011 9:15:09 PM > Subject: How to avoid deadlocks.. advice needed also insight, illuminatio= n 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=3Dsomeid > > 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 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` =3D '-3961.30' WHERE `accountid` =3D'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` =3D '1.31' WHERE `accountid` =3D'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