Deadlock Detection in InnoDB storage engine
Currently we are doing concurrent testing for our application which uses InnoDB storage engine.
We have nearly 5 threads running in parallel each performing same
set of operations in a fixed order. we have table called OB_SEQUENCE_INFO
which used for generating unique numbers. Every time we need to get unique no we
read the corresponding record with the lock, increment it and then update it.
The application throws the below error status after creating nearly 5000 records.
Please help us to understand why there is a deadlock and how to overcome it?
------------------------
LATEST DETECTED DEADLOCK
------------------------
120724 17:31:56
*** (1) TRANSACTION:
TRANSACTION 368CB13, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s)
MySQL thread id 13674, query id 60643444 192.168.4.138 obcore2 Sending data
select obsequence0_.ID as ID77_, obsequence0_.NAME as NAME77_, obsequence0_.VALUE as VALUE77_, obsequence0_.INCREMENT_SIZE as INCREMENT4_77_, obsequence0_.IS_POOLED as IS5_77_, obsequence0_.POOL_SIZE as POOL6_77_, obsequence0_.PARTY_ROLE_ID as PARTY7_77_ from OB_SEQUENCE_INFO obsequence0_ where obsequence0_.NAME='bill' and obsequence0_.PARTY_ROLE_ID='21ce44c2d23a11e1a3bf68b599c22bdc' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1217 n bits 80 index `PRIMARY` of table `obcore2`.`OB_SEQUENCE_INFO` trx id 368CB13 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 313537353530633664323361313165316133626636386235393963323262; asc 157550c6d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000368cb09; asc h ;;
2: len 7; hex 1a0012402d23a3; asc @-# ;;
3: len 5; hex 7061727479; asc party;;
4: len 8; hex 8000000000006b34; asc k4;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: SQL NULL;
*** (2) TRANSACTION:
TRANSACTION 368CB09, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
19 lock struct(s), heap size 3112, 15 row lock(s), undo log entries 13
MySQL thread id 13669, query id 60644069 192.168.4.138 obcore2 update
insert into OB_BILLING_PROFILE (ACC_ID, CREATED_DATE, LAST_MODIFIED_DATE, CREATED_BY, LAST_MODIFIED_BY, LAST_ACC_CYCLE, NEXT_ACC_CYCLE, STATUS, LAST_BILL, NEXT_BILL, BILL_WHEN, DOM, LAST_BILL_DATE, NEXT_BILL_DATE, ID) values ('ff80808138b37b650138b8dd706d0532', '2011-01-05 03:31:56', '2011-01-05 03:31:56', '22035e46d23a11e1a3bf68b599c22bdc', '22035e46d23a11e1a3bf68b599c22bdc', null, null, 'PendingActivation', null, null, null, null, null, null, 'ff80808138b37b650138b8dd70a40533')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1217 n bits 80 index `PRIMARY` of table `obcore2`.`OB_SEQUENCE_INFO` trx id 368CB09 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 313537353530633664323361313165316133626636386235393963323262; asc 157550c6d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000368cb09; asc h ;;
2: len 7; hex 1a0012402d23a3; asc @-# ;;
3: len 5; hex 7061727479; asc party;;
4: len 8; hex 8000000000006b34; asc k4;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: SQL NULL;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 323164656237383064323361313165316133626636386235393963323262; asc 21deb780d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000368cafb; asc h ;;
2: len 7; hex 0c00000d430503; asc C ;;
3: len 5; hex 6f72646572; asc order;;
4: len 8; hex 8000000000006b0e; asc k ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: len 30; hex 323163653434633264323361313165316133626636386235393963323262; asc 21ce44c2d23a11e1a3bf68b599c22b; (total 32 bytes);
Record lock, heap no 4 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 323164666131383664323361313165316133626636386235393963323262; asc 21dfa186d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 000003527880; asc Rx ;;
2: len 7; hex f4000002c40110; asc ;;
3: len 7; hex 696e766f696365; asc invoice;;
4: len 8; hex 80000000000003e8; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: len 30; hex 323163653434633264323361313165316133626636386235393963323262; asc 21ce44c2d23a11e1a3bf68b599c22b; (total 32 bytes);
Record lock, heap no 5 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 323165303861663664323361313165316133626636386235393963323262; asc 21e08af6d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000368cb09; asc h ;;
2: len 7; hex 1a0012402d23e6; asc @-# ;;
3: len 8; hex 637573746f6d6572; asc customer;;
4: len 8; hex 8000000000006b33; asc k3;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: len 30; hex 323163653434633264323361313165316133626636386235393963323262; asc 21ce44c2d23a11e1a3bf68b599c22b; (total 32 bytes);
Record lock, heap no 6 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 323165313734636164323361313165316133626636386235393963323262; asc 21e174cad23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000352b2cd; asc R ;;
2: len 7; hex 7d0009403e1de7; asc } @> ;;
3: len 7; hex 7061796d656e74; asc payment;;
4: len 8; hex 80000000000003ea; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: len 30; hex 323163653434633264323361313165316133626636386235393963323262; asc 21ce44c2d23a11e1a3bf68b599c22b; (total 32 bytes);
Record lock, heap no 7 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 323165323565623264323361313165316133626636386235393963323262; asc 21e25eb2d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000368cb07; asc h ;;
2: len 7; hex 18000b8033136c; asc 3 l;;
3: len 4; hex 62696c6c; asc bill;;
4: len 8; hex 80000000000068fd; asc h ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: len 30; hex 323163653434633264323361313165316133626636386235393963323262; asc 21ce44c2d23a11e1a3bf68b599c22b; (total 32 bytes);
Record lock, heap no 8 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 323165333438396164323361313165316133626636386235393963323262; asc 21e3489ad23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 000003527884; asc Rx ;;
2: len 7; hex f80012003c0110; asc < ;;
3: len 13; hex 71756f74657363686564756c65; asc quoteschedule;;
4: len 8; hex 80000001dcd65001; asc P ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: len 30; hex 323163653434633264323361313165316133626636386235393963323262; asc 21ce44c2d23a11e1a3bf68b599c22b; (total 32 bytes);
Record lock, heap no 9 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 323165343332363464323361313165316133626636386235393963323262; asc 21e43264d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 000003527885; asc Rx ;;
2: len 7; hex f900001a8a0110; asc ;;
3: len 8; hex 71756f74656d7361; asc quotemsa;;
4: len 8; hex 80000001b9130a01; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: len 30; hex 323163653434633264323361313165316133626636386235393963323262; asc 21ce44c2d23a11e1a3bf68b599c22b; (total 32 bytes);
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1215318 n bits 192 index `IDX_OB_BILL_PROF_ACC_ID` of table `obcore2`.`OB_BILLING_PROFILE` trx id 368CB09 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 368CD9B
Purge done for trx's n:o < 368CCFA undo n:o < 0
History list length 960
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 13673, query id 60663287 localhost obcore2
SHOW ENGINE INNODB STATUS
---TRANSACTION 368CCAF, not started
MySQL thread id 13643, query id 60656300 192.168.4.138 obcore2
---TRANSACTION 368B9C9, not started
MySQL thread id 13633, query id 60583529 192.168.4.20 obcore2
---TRANSACTION 0, not started
MySQL thread id 13549, query id 55555836 192.168.4.13 obcore2
---TRANSACTION 3664840, not started
MySQL thread id 13548, query id 55555833 192.168.4.13 obcore2
---TRANSACTION 368CD9A, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 13681, query id 60663280 192.168.4.138 obcore2 Sending data
select obsequence0_.ID as ID77_, obsequence0_.NAME as NAME77_, obsequence0_.VALUE as VALUE77_, obsequence0_.INCREMENT_SIZE as INCREMENT4_77_, obsequence0_.IS_POOLED as IS5_77_, obsequence0_.POOL_SIZE as POOL6_77_, obsequence0_.PARTY_ROLE_ID as PARTY7_77_ from OB_SEQUENCE_INFO obsequence0_ where obsequence0_.NAME='party' for update
Trx read view will not see trx with id >= 368CD9B, sees < 368CD7A
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1217 n bits 80 index `PRIMARY` of table `obcore2`.`OB_SEQUENCE_INFO` trx id 368CD9A lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 313537353530633664323361313165316133626636386235393963323262; asc 157550c6d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000368cd82; asc h ;;
2: len 7; hex 55000b802b1539; asc U + 9;;
3: len 5; hex 7061727479; asc party;;
4: len 8; hex 8000000000006b4a; asc kJ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: SQL NULL;
------------------
---TRANSACTION 368CD98, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 4 row lock(s)
MySQL thread id 13669, query id 60663276 192.168.4.138 obcore2 Sending data
select obsequence0_.ID as ID77_, obsequence0_.NAME as NAME77_, obsequence0_.VALUE as VALUE77_, obsequence0_.INCREMENT_SIZE as INCREMENT4_77_, obsequence0_.IS_POOLED as IS5_77_, obsequence0_.POOL_SIZE as POOL6_77_, obsequence0_.PARTY_ROLE_ID as PARTY7_77_ from OB_SEQUENCE_INFO obsequence0_ where obsequence0_.NAME='bill' and obsequence0_.PARTY_ROLE_ID='21ce44c2d23a11e1a3bf68b599c22bdc' for update
Trx read view will not see trx with id >= 368CD99, sees < 368CD7A
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1217 n bits 80 index `PRIMARY` of table `obcore2`.`OB_SEQUENCE_INFO` trx id 368CD98 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 313537353530633664323361313165316133626636386235393963323262; asc 157550c6d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000368cd82; asc h ;;
2: len 7; hex 55000b802b1539; asc U + 9;;
3: len 5; hex 7061727479; asc party;;
4: len 8; hex 8000000000006b4a; asc kJ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: SQL NULL;
------------------
---TRANSACTION 368CD8A, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 13682, query id 60662843 192.168.4.138 obcore2 Sending data
select obsequence0_.ID as ID77_, obsequence0_.NAME as NAME77_, obsequence0_.VALUE as VALUE77_, obsequence0_.INCREMENT_SIZE as INCREMENT4_77_, obsequence0_.IS_POOLED as IS5_77_, obsequence0_.POOL_SIZE as POOL6_77_, obsequence0_.PARTY_ROLE_ID as PARTY7_77_ from OB_SEQUENCE_INFO obsequence0_ where obsequence0_.NAME='party' for update
Trx read view will not see trx with id >= 368CD8B, sees < 368CD6E
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1217 n bits 80 index `PRIMARY` of table `obcore2`.`OB_SEQUENCE_INFO` trx id 368CD8A lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 30; hex 313537353530633664323361313165316133626636386235393963323262; asc 157550c6d23a11e1a3bf68b599c22b; (total 32 bytes);
1: len 6; hex 00000368cd82; asc h ;;
2: len 7; hex 55000b802b1539; asc U + 9;;
3: len 5; hex 7061727479; asc party;;
4: len 8; hex 8000000000006b4a; asc kJ;;
5: len 4; hex 80000001; asc ;;
6: len 1; hex 31; asc 1;;
7: len 4; hex 80000064; asc d;;
8: SQL NULL;
------------------
---TRANSACTION 368CD82, ACTIVE 1 sec
16 lock struct(s), heap size 3112, 13 row lock(s), undo log entries 12
MySQL thread id 13683, query id 60663286 192.168.4.138 obcore2
Trx read view will not see trx with id >= 368CD83, sees < 368CD6D
---TRANSACTION 368CD7A, ACTIVE 1 sec
mysql tables in use 7, locked 0
MySQL thread id 13679, query id 60662924 192.168.4.138 obcore2 Copying to tmp table
select this_.ID as ID15_6_, this_.CREATED_DATE as CREATED2_15_6_, this_.CREATED_BY as CREATED3_15_6_, this_.LAST_MODIFIED_DATE as LAST4_15_6_, this_.LAST_MODIFIED_BY as LAST5_15_6_, this_.ACC_NUMBER as ACC6_15_6_, this_.PARENT_ID as PARENT7_15_6_, this_.ACTIVATION_START_DATE as ACTIVATION8_15_6_, this_.STATUS as STATUS15_6_, this_.TAX_EXEMPTED as TAX10_15_6_, this_.NOTES as NOTES15_6_, this_.PAYMENT_PROFILE_ID as PAYMENT12_15_6_, this_.SPONSORED as SPONSORED15_6_, this_.BUYER_ID as BUYER14_15_6_, this_.SELLER_ID as SELLER15_15_6_, accountbal6_.ACC_ID as ACC2_15_8_, accountbal6_.ID as ID8_, acc
Trx read view will not see trx with id >= 368CD7B, sees < 368CD6C
--------