Bug #32210 Deadlock when inserting into an InnoDB table and select from it at the same time
Submitted: 9 Nov 2007 3:30 Modified: 9 Nov 2007 5:35
Reporter: Safari - Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.0.22 OS:Linux (CentOS 5.0 x86_64)
Assigned to: CPU Architecture:Any

[9 Nov 2007 3:30] Safari -
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)
[9 Nov 2007 5:35] Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php