Help with detected deadlock

[Danny Lade]

We are running a big delete statement with IN-clause to cleanup much older data. But the insert-statement shall insert only data with much newer Id's therefore the rows are not in the same ID-space.

Lets say:
ID 1 ... 20 - will be removed
ID 30 - is inserted
at the same time but at different rows.

We're locking on a row/rec basis, therefore (IMHO) it shouldn't be running in a deadlock - but it does.

We've read a lot of internet articles but non of this describes how the locking really works at innodDB nor how this special deadlock can happen.
(maybe we're reading the wrong docs and there is one we missed ...)

Help please,
Danny

------------------------
LATEST DETECTED DEADLOCK
------------------------
130418 10:39:00
*** (1) TRANSACTION:
TRANSACTION 3130F19, ACTIVE 0 sec, process no 22672, OS thread id 1080166736 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 3112, 1859 row lock(s), undo log entries 1850
MySQL thread id 554692, query id 47035826 10.188.14.235 test_user updating
DELETE FROM BIGOBJECT WHERE id IN (11811161272,11811161273,11811161274,11811161275,11811161276,11811161277,11811161278,11811161279,11811161271,11811161310,11811161311,11811161308,11811161309,11811161306,11811161307,11811161304,11811161305,11811161302,11811161303,11811161300,11811161301,11811161298,11811161299,11811161296,11811161297,11811161295,11811161294,11811161293,11811161292,11811161291,11811161290,11811161289,11811161288,11811161287,11811161286,11811161285,11811161284,11811161283,11811161282,11811161281,11811161280,11811161320,11811161317,11811161316,11811161319,11811161318,11811161313,11811161312,11811161315,11811161314,11811161107,11811161106,11811161105,11811161104,11811161110,11811161109,11811161108,11811161090,11811161091,11811161088,11811161089,11811161094,11811161095,11811161092,11811161093,11811161098,11811161099,11811161096,11811161097,11811161102,11811161103,11811161100,11811161101,11811161175,11811161174,1181
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 31468 n bits 424 index `PRIMARY` of table `testdb`.`BIGOBJECT` trx id 3130F19 lock_mode X waiting
Record lock, heap no 64 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 8; hex 80000002c000109e; asc ;;
1: len 6; hex 000003130f13; asc ;;
2: len 7; hex 800000016001e8; asc ` ;;
3: len 8; hex 80000002c0001099; asc ;;
4: len 8; hex 80000002c0000061; asc a;;
5: SQL NULL;
6: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 3130F13, ACTIVE 1 sec, process no 22672, OS thread id 1106278736 inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 28
MySQL thread id 554650, query id 47035835 10.188.14.235 test_user update
INSERT INTO BIGOBJECT (ID, CHILDID, PARENTID, SOURCEID, TARGETID) VALUES (11811164317, 11811164314, 11811160161, null, null)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 31468 n bits 424 index `PRIMARY` of table `testdb`.`BIGOBJECT` trx id 3130F13 lock_mode X locks rec but not gap
Record lock, heap no 64 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 8; hex 80000002c000109e; asc ;;
1: len 6; hex 000003130f13; asc ;;
2: len 7; hex 800000016001e8; asc ` ;;
3: len 8; hex 80000002c0001099; asc ;;
4: len 8; hex 80000002c0000061; asc a;;
5: SQL NULL;
6: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 31468 n bits 424 index `PRIMARY` of table `testdb`.`BIGOBJECT` trx id 3130F13 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 64 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 8; hex 80000002c000109e; asc ;;
1: len 6; hex 000003130f13; asc ;;
2: len 7; hex 800000016001e8; asc ` ;;
3: len 8; hex 80000002c0001099; asc ;;
4: len 8; hex 80000002c0000061; asc a;;
5: SQL NULL;
6: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 3130F97
Purge done for trx's n:o < 3130F2C undo n:o < 0
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 22672, OS thread id 1088567632
MySQL thread id 555789, query id 47040041 localhost root
show innodb status
---TRANSACTION 3130F96, not started, process no 22672, OS thread id 1093847376
MySQL thread id 555580, query id 47039724 localhost root
---TRANSACTION 3130F86, not started, process no 22672, OS thread id 1075808592
MySQL thread id 555311, query id 47039184 localhost root
---TRANSACTION 0, not started, process no 22672, OS thread id 1076336976
MySQL thread id 553985, query id 47036033 localhost root
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)



Edited 1 time(s). Last edit at 04/19/2013 08:37AM by Danny Lade.