Re: Deadlock Detection in InnoDB storage engine
Hi Aftab,
Thanks for all the help and assistance.
We tried this.
CREATE INDEX IDX_BILLING_ACCOUNT_SELLER_ID_STATUS ON OB_BILLING_ACCOUNT(SELLER_ID,STATUS);
Explain Plan:-
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: this_
type: ref
possible_keys: IDX_BILLING_ACCOUNT_STATUS,IDX_BILLING_ACCOUNT_SELLER_ID,IDX_BILLING_ACCOUNT_SELLER_ID_STATUS
key: IDX_BILLING_ACCOUNT_SELLER_ID
key_len: 99
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: accountbal6_
type: ref
possible_keys: IDX_OB_BALANCE_ACC_ID
key: IDX_OB_BALANCE_ACC_ID
key_len: 98
ref: obcore2.this_.ID
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: obbillinga5_
type: ref
possible_keys: IDX_OB_BLLNG_ACC_IMG_ACC_ID
key: IDX_OB_BLLNG_ACC_IMG_ACC_ID
key_len: 99
ref: obcore2.this_.ID
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: obpartyrol1_
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: obcore2.this_.BUYER_ID
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: obparty2_
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: obcore2.obpartyrol1_.PARTY_ID
rows: 1
Extra:
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: obpartyadd3_
type: ref
possible_keys: IDX_OB_PARTY_ADDRESS_ACC_ID
key: IDX_OB_PARTY_ADDRESS_ACC_ID
key_len: 98
ref: obcore2.obparty2_.ID
rows: 1
Extra:
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: obpartycon4_
type: ref
possible_keys: IDX_OB_PRTY_CNTCT_PRTY_ID
key: IDX_OB_PRTY_CNTCT_PRTY_ID
key_len: 98
ref: obcore2.obparty2_.ID
rows: 1
Extra:
7 rows in set (0.00 sec)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Slow query Log:-
# User@Host: obcore2[obcore2] @ [192.168.4.138]
# Query_time: 15.017330 Lock_time: 0.000230 Rows_sent: 30 Rows_examined: 435923
SET timestamp=1343625547;
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_, accountbal6_.ID as ID10_0_, accountbal6_.ACC_ID as ACC2_10_0_, accountbal6_.BILLING_PROFILE as BILLING3_10_0_, accountbal6_.CREATED_DATE as CREATED4_10_0_, accountbal6_.LAST_MODIFIED_DATE as LAST5_10_0_, accountbal6_.CREATED_BY as CREATED6_10_0_, accountbal6_.LAST_MODIFIED_BY as LAST7_10_0_, obbillinga5_.ACC_ID as ACC2_15_9_, obbillinga5_.ID as ID9_, obbillinga5_.ID as ID16_1_, obbillinga5_.ACC_ID as ACC2_16_1_, obbillinga5_.CREATED_DATE as CREATED3_16_1_, obbillinga5_.CREATED_BY as CREATED4_16_1_, obbillinga5_.LAST_MODIFIED_DATE as LAST5_16_1_, obbillinga5_.LAST_MODIFIED_BY as LAST6_16_1_, obbillinga5_.IMAGE as IMAGE16_1_, obbillinga5_.DESCRIPTION as DESCRIPT8_16_1_, obpartyrol1_.ID as ID97_2_, obpartyrol1_.PARTY_ID as PARTY2_97_2_, obpartyrol1_.CREATED_DATE as CREATED3_97_2_, obpartyrol1_.CREATED_BY as CREATED4_97_2_, obpartyrol1_.LAST_MODIFIED_DATE as LAST5_97_2_, obpartyrol1_.LAST_MODIFIED_BY as LAST6_97_2_, obpartyrol1_.TYPE as TYPE97_2_, obpartyrol1_.PRIMARY_CURRENCY_ID as PRIMARY8_97_2_, obpartyrol1_.SECONDARY_CURRENCY_ID as SECONDARY9_97_2_, obparty2_.ID as ID92_3_, obparty2_.CREATED_DATE as CREATED2_92_3_, obparty2_.CREATED_BY as CREATED3_92_3_, obparty2_.LAST_MODIFIED_DATE as LAST4_92_3_, obparty2_.LAST_MODIFIED_BY as LAST5_92_3_, obparty2_.NAME as NAME92_3_, obparty2_.PARTY_NUMBER as PARTY7_92_3_, obparty2_.TYPE as TYPE92_3_, obparty2_.VALID_FROM as VALID9_92_3_, obparty2_.VALID_TO as VALID10_92_3_, obpartyadd3_.PARTY_ID as PARTY2_92_10_, obpartyadd3_.ID as ID10_, obpartyadd3_.ID as ID93_4_, obpartyadd3_.PARTY_ID as PARTY2_93_4_, obpartyadd3_.CREATED_DATE as CREATED3_93_4_, obpartyadd3_.CREATED_BY as CREATED4_93_4_, obpartyadd3_.LAST_MODIFIED_DATE as LAST5_93_4_, obpartyadd3_.LAST_MODIFIED_BY as LAST6_93_4_, obpartyadd3_.ADD_LINE1 as ADD7_93_4_, obpartyadd3_.ADD_LINE2 as ADD8_93_4_, obpartyadd3_.ADD_LINE3 as ADD9_93_4_, obpartyadd3_.COUNTY as COUNTY93_4_, obpartyadd3_.CITY as CITY93_4_, obpartyadd3_.STATE as STATE93_4_, obpartyadd3_.COUNTRY as COUNTRY93_4_, obpartyadd3_.ZIP as ZIP93_4_, obpartyadd3_.ADD_TYPE as ADD15_93_4_, obpartycon4_.PARTY_ID as PARTY2_92_11_, obpartycon4_.ID as ID11_, obpartycon4_.ID as ID95_5_, obpartycon4_.PARTY_ID as PARTY2_95_5_, obpartycon4_.CREATED_DATE as CREATED3_95_5_, obpartycon4_.CREATED_BY as CREATED4_95_5_, obpartycon4_.LAST_MODIFIED_DATE as LAST5_95_5_, obpartycon4_.LAST_MODIFIED_BY as LAST6_95_5_, obpartycon4_.SALUTATION as SALUTATION95_5_, obpartycon4_.FIRST_NAME as FIRST8_95_5_, obpartycon4_.MIDDLE_NAME as MIDDLE9_95_5_, obpartycon4_.LAST_NAME as LAST10_95_5_, obpartycon4_.CONTACT_TYPE as CONTACT11_95_5_ from OB_BILLING_ACCOUNT this_ left outer join OB_BALANCE accountbal6_ on this_.ID=accountbal6_.ACC_ID left outer join OB_BILLING_ACC_IMAGE obbillinga5_ on this_.ID=obbillinga5_.ACC_ID left outer join OB_PARTY_ROLE obpartyrol1_ on this_.BUYER_ID=obpartyrol1_.ID left outer join OB_PARTY obparty2_ on obpartyrol1_.PARTY_ID=obparty2_.ID left outer join OB_PARTY_ADDRESS obpartyadd3_ on obparty2_.ID=obpartyadd3_.PARTY_ID left outer join OB_PARTY_CONTACT obpartycon4_ on obparty2_.ID=obpartycon4_.PARTY_ID where this_.STATUS=1001 and this_.SELLER_ID='bf2fc500d6f011e1860068b599c22bdc' order by obparty2_.NAME asc limit 30;
This query is generated using Hibernate(ORM). The same query will be used by all the 5 threads.
This is currently used for Account Listing information in the landing page.