MySQL Forums
Forum List  »  InnoDB

two updates => TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH
Posted by: mehdi xxxxx
Date: January 29, 2009 04:53AM

hi,
I am having some deadlocks and I was wondering if someone could help me understand it.

this is one table of the application :

CREATE TABLE CAMPAGNE (
CPG_ID int(10) unsigned NOT NULL auto_increment,
CPG_ODP varchar(8) default NULL,
ANNC_ID int(10) unsigned NOT NULL default '0',
CPG_NOM varchar(64) NOT NULL default '',
CPG_STATUS int(10) unsigned NOT NULL default '1',
CPG_DATE_DEBUT int(10) unsigned NOT NULL default '0',
CPG_DATE_FIN int(10) unsigned NOT NULL default '0',
CPG_PRIORITE int(10) unsigned NOT NULL default '0',
CPG_PONDERATION tinyint(3) unsigned NOT NULL default '1',
CPG_MAXAFFICHAGE int(10) unsigned NOT NULL default '0',
CPG_MAXCLICK int(10) unsigned NOT NULL default '0',
CPG_MAXAFFICHAGEJOURNEE int(10) unsigned NOT NULL default '0',
CPG_AFFICHAGE int(10) unsigned NOT NULL default '0',
CPG_CLICK int(10) unsigned NOT NULL default '0',
CPG_AFFICHAGEJOURNEE int(10) unsigned NOT NULL default '0',
PRIMARY KEY (CPG_ID),
KEY CAMPAGNE_FKIndex1 (ANNC_ID)
) ENGINE=Innodb DEFAULT CHARSET=latin1;

we do many increments on 3 fields of the table :
update CAMPAGNE set CPG_AFFICHAGE=CPG_AFFICHAGE+1, CPG_CLICK=CPG_CLICK+1, CPG_AFFICHAGEJOURNEE=CPG_AFFICHAGEJOURNEE+1 where CPG_ID='170'

the table contains 30 records.

under high load, I get some deadlocks.


this is the innodb status :
------------------------
LATEST DETECTED DEADLOCK
------------------------
090129 1:05:49
*** (1) TRANSACTION:
TRANSACTION 0 2246272, ACTIVE 1 sec, process no 27784, OS thread id 1190259040 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 100163, query id 202515 web2 94.143.118.39 root Updating
update `DB`.CAMPAGNE set CPG_AFFICHAGE=CPG_AFFICHAGE+1, CPG_CLICK=CPG_CLICK+1, CPG_AFFICHAGEJOURNEE=CPG_AFFICHAGEJOURNEE+1 where CPG_ID='170'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 183 page no 3 n bits 104 index `PRIMARY` of table `DB/CAMPAGNE` trx id 0 2246272 lock_mode X locks rec but not gap waiting
Record lock, heap no 31
*** (2) TRANSACTION:
TRANSACTION 0 2246278, ACTIVE 1 sec, process no 27784, OS thread id 1202506080 updating or deleting, thread declared inside InnoDB 0
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368
MySQL thread id 100170, query id 202521 web2 94.143.118.39 root Updating
update `DB`.CAMPAGNE set CPG_AFFICHAGE=CPG_AFFICHAGE+1, CPG_CLICK=CPG_CLICK+1, CPG_AFFICHAGEJOURNEE=CPG_AFFICHAGEJOURNEE+1 where CPG_ID='170'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 183 page no 3 n bits 104 index `PRIMARY` of table `DB/CAMPAGNE` trx id 0 2246278 lock_mode X locks rec but not gap
Record lock, heap no 31
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 183 page no 3 n bits 104 index `PRIMARY` of table `DB/CAMPAGNE` trx id 0 2246673 lock_mode X locks rec but not gap waiting
Record lock, heap no 31
TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH
*** WE ROLL BACK TRANSACTION (2)


the transaction 2246278 is waiting 2246673 gets the lock it is holding??? :

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 183 page no 3 n bits 104 index `PRIMARY` of table `DB/CAMPAGNE` trx id 0 2246278 lock_mode X locks rec but not gap
Record lock, heap no 31
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 183 page no 3 n bits 104 index `PRIMARY` of table `DB/CAMPAGNE` trx id 0 2246673 lock_mode X locks rec but not gap waiting
Record lock, heap no 31

and what does "TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH" mean?

Options: ReplyQuote


Subject
Views
Written By
Posted
two updates => TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH
3469
January 29, 2009 04:53AM


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.