Description:
The deadlock happened when there's an insert to the InnoDB table (tblresume_industry) and at the same time there's a select from it.
Below is the DEADLOCK detail from SHOW ENGINE INNODB STATUS:
------------------------
LATEST DETECTED DEADLOCK
------------------------
071108 23:09:04
*** (1) TRANSACTION:
TRANSACTION 0 74187752, ACTIVE 0 sec, process no 22526, OS thread id 1167776064 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1216, undo log entries 2
MySQL thread id 13732228, query id 288527898 web1 192.168.100.201 testuser25 update
INSERT INTO tblresume_industry(resumeid,industryid) VALUES ('411053','33'),('411053','17'),('411053','3')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 128539 n bits 984 index `industryid` of table `mydb/tblresume_industry` trx id 0 74187752 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 2; hex 8012; asc ;; 1: len 4; hex 80000101; asc ;; 2: len 4; hex 801a95f0; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 74187659, ACTIVE 3 sec, process no 22526, OS thread id 1172035904 fetching rows, thread declared inside InnoDB 43
mysql tables in use 2, locked 2
28166 lock struct(s), heap size 2570224
MySQL thread id 13732201, query id 288527222 web1 192.168.100.201 testuser25 Sending data
UPDATE tblref_industrycode o SET industryresumecount =
( SELECT COUNT(ri.resumeid) FROM tblresume_industry ri
JOIN tblresume r ON r.resumeid=ri.resumeid AND r.iscompleted = 1
AND r.isapproved = 1
AND r.issearchable = 1
AND r.isdeleted = 0
AND r.isdenied = 0
WHERE ri.industryid=o.industryid
)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 128539 n bits 984 index `industryid` of table `mydb/tblresume_industry` trx id 0 74187659 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 2; hex 8012; asc ;; 1: len 4; hex 80000101; asc ;; 2: len 4; hex 801a95f0; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 129869 n bits 808 index `industryid` of table `mydb/tblresume_industry` trx id 0 74187659 lock mode S waiting
Record lock, heap no 733 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 2; hex 8021; asc !;; 1: len 4; hex 800645ad; asc E ;; 2: len 4; hex 801e907c; asc |;;
*** WE ROLL BACK TRANSACTION (2)
AND the DEADLOCK report from innotop:
___________________________________________________________________ Deadlock Transactions ___________________________________________________________________
CXN ID Timestring User Host Victim Time Undo LStrcts Query Text
VNW 13732201 2007-11-08 23:09:04 testuser25 web1 Yes 00:03 0 28166 UPDATE tblref_industrycode o SET industryresumecount = ( SELECT COUNT(ri.r......
VNW 13732228 2007-11-08 23:09:04 testuser25 web1 No 00:00 2 4 INSERT INTO tblresume_industry(resumeid,industryid) VALUES ('411053','33')
___________________________________________ Deadlock Locks ___________________________________________
CXN ID Txn Status Mode DB Table Index Special Ins Intent
VNW 13732201 waits_for S mydb tblresume_industry industryid 0
VNW 13732201 holds S mydb tblresume_industry industryid gap before rec 0
VNW 13732228 waits_for X mydb tblresume_industry industryid gap before rec 1
How to repeat:
The tables definition:
mysql> show create table tblref_industrycode\G
*************************** 1. row ***************************
Table: tblref_industrycode
Create Table: CREATE TABLE `tblref_industrycode` (
`industryid` smallint(6) NOT NULL auto_increment,
`industrycode` varchar(50) default NULL,
`industryjobcount` int(11) default NULL,
`industryresumecount` int(11) default NULL,
PRIMARY KEY (`industryid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table tblresume_industry\G
*************************** 1. row ***************************
Table: tblresume_industry
Create Table: CREATE TABLE `tblresume_industry` (
`entryid` int(11) NOT NULL auto_increment,
`resumeid` int(11) default NULL,
`industryid` smallint(6) default NULL,
`jobfunctionid` smallint(6) default NULL,
PRIMARY KEY (`entryid`),
KEY `resumeid` (`resumeid`),
KEY `industryid` (`industryid`,`resumeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)