Bug #68083 Inserts that cannot progress that wait for a LOCK, innodb implicity kill them
Submitted: 14 Jan 2013 14:41 Modified: 14 Jan 2013 18:18
Reporter: Aftab Khan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.61 OS:Any
Assigned to: Matthew Lord CPU Architecture:Any

[14 Jan 2013 14:41] Aftab Khan 
Description:
I get a situation where inserts cannot progress and after long wait e.g. > 30 min. Innodb implicitly kill/rollback transactions:

[I have attached full output of show engine innodb status to the bug report]

---TRANSACTION 85207BD, ACTIVE 1536 sec, process no 5191, OS thread id 1331210560 updating or deleting, thread declared inside InnoDB 225
mysql tables in use 1, locked 1
162 lock struct(s), heap size 47544, 60190 row lock(s), undo log entries 9156
MySQL thread id 1700001, query id 382017386 localhost dba updating
DELETE FROM tblAlertSearchValue WHERE PurgeFlag = 1 LIMIT 10000
---TRANSACTION 85205ED, ACTIVE 1807 sec, process no 5191, OS thread id 1331611968 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4717 lock struct(s), heap size 1079736, 23 row lock(s), undo log entries 7
MySQL thread id 1627189, query id 382017574 bass.hosts.adeptra.com 10.11.5.22 dwhloader update

INSERT INTO tblAlertSearchValue
        (
            fkAlert_ID
            ,  fkAlertSearchName_ID
            ,  EncryptedValue
        )
        VALUES
        (
                NAME_CONST('vAlert_ID',524468793)
            ,   NAME_CONST('vAlertSearchName_ID',1)
            ,   NAME_CONST('vAlertSearchValue_Value',_binary'?? ?F??<Ii????^X?@1WO?T??W????8;' COLLATE 'binary')
        )
Trx read view will not see trx with id >= 85205EE, sees < 85202FC
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 23791889 n bits 1192 index `IX_AlertSearchValue_PurgeFlag` of table `global`.`tblAlertSearchValue` trx id 85205ED lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 699
------------------
---TRANSACTION 8520575, ACTIVE 1809 sec, process no 5191, OS thread id 1334020416 inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
6420 lock struct(s), heap size 1472952, 9 row lock(s), undo log entries 3
MySQL thread id 1627544, query id 382017429 bass.hosts.adeptra.com 10.11.5.22 dwhloader update
INSERT INTO tblAlertSearchValue
       (
           fkAlert_ID
           ,  fkAlertSearchName_ID
           ,  Value
        )
        VALUES
        (
            NAME_CONST('vAlert_ID',524468792)
           ,   NAME_CONST('vAlertSearchName_ID',292)
           ,  LTRIM(  NAME_CONST('pAlertSearchValue_Value',_utf8'CBP' COLLATE 'utf8_general_ci') )
        )
Trx read view will not see trx with id >= 8520576, sees < 85202FC
---TRANSACTION 8520542, ACTIVE 1809 sec, process no 5191, OS thread id 1085741376 inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
6773 lock struct(s), heap size 1538488, 18 row lock(s), undo log entries 6
MySQL thread id 1636721, query id 382017707 bass.hosts.adeptra.com 10.11.5.22 dwhloader update
INSERT INTO tblAlertSearchValue
       (
           fkAlert_ID
           ,  fkAlertSearchName_ID
           ,  Value
        )
        VALUES
        (
            NAME_CONST('vAlert_ID',524468791)
           ,   NAME_CONST('vAlertSearchName_ID',633)
           ,  LTRIM(  NAME_CONST('pAlertSearchValue_Value',_utf8'FI_BASE_TENANT' COLLATE 'utf8_general_ci') )
        )
Trx read view will not see trx with id >= 8520543, sees < 85202FC
---TRANSACTION 8520534, ACTIVE 1809 sec, process no 5191, OS thread id 1326795072 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5873 lock struct(s), heap size 1341880, 19 row lock(s), undo log entries 6
MySQL thread id 1627546, query id 382017489 bass.hosts.adeptra.com 10.11.5.22 dwhloader update
INSERT INTO tblAlertSearchValue
       (
           fkAlert_ID
           ,  fkAlertSearchName_ID
           ,  Value
        )
        VALUES
        (
            NAME_CONST('vAlert_ID',524468790)
           ,   NAME_CONST('vAlertSearchName_ID',8)
           ,  LTRIM(  NAME_CONST('pAlertSearchValue_Value',_utf8'45236' COLLATE 'utf8_general_ci') )
        )
Trx read view will not see trx with id >= 8520535, sees < 85202FC
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 23791889 n bits 1192 index `IX_AlertSearchValue_PurgeFlag` of table `global`.`tblAlertSearchValue` trx id 8520534 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 699
------------------
---TRANSACTION 852051C, ACTIVE 1809 sec, process no 5191, OS thread id 1332816192 inserting
mysql tables in use 1, locked 1
LOCK WAIT 6058 lock struct(s), heap size 1391032, 20 row lock(s), undo log entries 6
MySQL thread id 1627547, query id 382017695 bass.hosts.adeptra.com 10.11.5.22 dwhloader update
INSERT INTO tblAlertSearchValue
       (
           fkAlert_ID
           ,  fkAlertSearchName_ID
           ,  Value
        )
        VALUES
        (
            NAME_CONST('vAlert_ID',524468789)
           ,   NAME_CONST('vAlertSearchName_ID',8)
           ,  LTRIM(  NAME_CONST('pAlertSearchValue_Value',_utf8'275259496' COLLATE 'utf8_general_ci') )
        )
Trx read view will not see trx with id >= 852051D, sees < 85202FC
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 23791889 n bits 1192 index `IX_AlertSearchValue_PurgeFlag` of table `global`.`tblAlertSearchValue` trx id 852051C lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 699

How to repeat:
Uncertain. Presumably, run concurrent inserts until the bug occurs.
[14 Jan 2013 14:53] Aftab Khan 
I don't understand how the following 'DELETE' query can block inserts, the 'delete' query was started after single-row insert query 

[code]
---TRANSACTION 85207BD, ACTIVE 1536 sec, process no 5191, OS thread id 1331210560 updating or deleting, thread declared inside InnoDB 225
mysql tables in use 1, locked 1
162 lock struct(s), heap size 47544, 60190 row lock(s), undo log entries 9156
MySQL thread id 1700001, query id 382017386 localhost dba updating
DELETE FROM tblAlertSearchValue WHERE PurgeFlag = 1 LIMIT 10000
[/code]
[14 Jan 2013 18:18] Matthew Lord 
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not obviously the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.