How to avoid deadlocks.. advice needed also insight, illumination anda bit of hallucination...

--00151747849e7b988f04a15e7176
Content-Type: text/plain; charset=ISO-8859-1

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 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?

--00151747849e7b988f04a15e7176--

Re: How to avoid deadlocks.. advice needed also insight, illumination and a bit of hallucination...

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és 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg