List:General Discussion« Previous MessageNext Message »
From:Andrés Tello Date:April 20 2011 7:15pm
Subject:How to avoid deadlocks.. advice needed also insight, illumination and
a bit of hallucination...
View as plain text  
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 <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?

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