List:General Discussion« Previous MessageNext Message »
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...
View as plain text  
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.criptos@stripped>
> To: "Mailing-List mysql" <mysql@stripped>
> 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
Thread
How to avoid deadlocks.. advice needed also insight, illumination anda bit of hallucination...AndrĂ©s Tello20 Apr
  • Re: How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...Johan De Meersman20 Apr