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)
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)