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"
> To: "Mailing-List mysql"
> 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 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 0198; asc ;; 1: len 6; hex 0001b314; asc
>;;
> 2: len 7; hex 3b0b18; asc ; ;; 3: len 4; hex 0194;
> 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 7086e1; 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 0001; asc ;; 1: len 6; hex 00040aba; asc
>;;
> 2: len 7; hex 3c1b0a; 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 78f72f3efa; 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
