MySQL Forums
Forum List  »  InnoDB

Re: Deadlock Detection in InnoDB storage engine
Posted by: Manjunath C
Date: August 01, 2012 12:03AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Deadlock Detection in InnoDB storage engine
4497
August 01, 2012 12:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.