MySQL Bugs: #12140: Deadlock when two transactions executes INSERTs/DELETEs on same table

Bug #12140	Deadlock when two transactions executes INSERTs/DELETEs on same table
Submitted:	24 Jul 2005 16:52	Modified:	13 Sep 2005 10:32
Reporter:	Maxim M	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.9-beta, 4.1.9, 4.1.12-nt (probably any)	OS:	Linux (Linux RH7.3, RH8.0, RHAS3.0, XP)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
When two transactions INSERTs and DELETEs data to/from same table in parallel - deadlock  happens.

*) CREATE TABLE test.deadlock_test (
        id BIGINT  NOT NULL AUTO_INCREMENT,
        value BIGINT NOT NULL,
        PRIMARY KEY(id)
    ) TYPE = InnoDB; 
 
*) First transaction does loop, where X changes from 1 to 100:
set autocommit=0;
loop for (X from 1 to 100):
        insert into deadlock_test (value) values ( X );
        delete from deadlock_test where value = X;
end loop;
commit;

*) Second transaction does same loop, but x changes from 101 to 200

Notice that each transaction locks different rows.

MySQL error message:
        Deadlock found when trying to get lock; try restarting transaction

Later output from SHOW ENGINE INNODB STATUS statement:
=====================================
050724 19:36:17 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6, signal count 6
Mutex spin waits 4, rounds 80, OS waits 0
RW-shared spins 12, OS waits 6; RW-excl spins 0, OS waits 0
------------------------
LATEST DETECTED DEADLOCK
------------------------
050724 19:36:14
*** (1) TRANSACTION:
TRANSACTION 0 894507, ACTIVE 0 sec, process no 14374, OS thread id 45068 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 23, query id 739 localhost.localdomain 127.0.0.1 root updating
delete from deadlock_test where value = 101
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 50 n bits 320 index `PRIMARY` of table `test/deadlock_test` trx id 0 894507 lock_mode X locks rec but not gap waiting
Record lock, heap no 248 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000bf; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332406; asc     3$ ;; 3: len 8; hex 8000000000000000; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 0 894506, ACTIVE 0 sec, process no 14372, OS thread id 40971 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 159
MySQL thread id 22, query id 740 localhost.localdomain 127.0.0.1 root updating
delete from deadlock_test where value = 79
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 50 n bits 320 index `PRIMARY` of table `test/deadlock_test` trx id 0 894506 lock_mode X locks rec but not gap
Record lock, heap no 170 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 800000000000010d; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033312e; asc     31.;; 3: len 8; hex 800000000000004e; asc        N;;

Record lock, heap no 171 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 800000000000010c; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000333102; asc     31 ;; 3: len 8; hex 800000000000004d; asc        M;;

Record lock, heap no 172 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 800000000000010b; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003330d6; asc     30 ;; 3: len 8; hex 800000000000004c; asc        L;;

Record lock, heap no 173 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 800000000000010a; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003330aa; asc     30 ;; 3: len 8; hex 800000000000004b; asc        K;;

Record lock, heap no 174 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000109; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033307e; asc     30~;; 3: len 8; hex 800000000000004a; asc        J;;

Record lock, heap no 175 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000108; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000333052; asc     30R;; 3: len 8; hex 8000000000000049; asc        I;;

Record lock, heap no 176 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000107; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000333026; asc     30&;; 3: len 8; hex 8000000000000048; asc        H;;

Record lock, heap no 177 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000106; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332ffa; asc     3/ ;; 3: len 8; hex 8000000000000047; asc        G;;

Record lock, heap no 178 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000105; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332fce; asc     3/ ;; 3: len 8; hex 8000000000000046; asc        F;;

Record lock, heap no 179 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000104; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332fa2; asc     3/ ;; 3: len 8; hex 8000000000000045; asc        E;;

Record lock, heap no 180 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000103; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332f76; asc     3/v;; 3: len 8; hex 8000000000000044; asc        D;;

Record lock, heap no 181 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000102; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332f4a; asc     3/J;; 3: len 8; hex 8000000000000043; asc        C;;

Record lock, heap no 182 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000101; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332f1e; asc     3/ ;; 3: len 8; hex 8000000000000042; asc        B;;

Record lock, heap no 183 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 8000000000000100; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332ef2; asc     3. ;; 3: len 8; hex 8000000000000041; asc        A;;

Record lock, heap no 184 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000ff; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332ec6; asc     3. ;; 3: len 8; hex 8000000000000040; asc        @;;

Record lock, heap no 185 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000fe; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332e9b; asc     3. ;; 3: len 8; hex 800000000000003f; asc        ?;;

Record lock, heap no 186 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000fd; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332e70; asc     3.p;; 3: len 8; hex 800000000000003e; asc        >;;

Record lock, heap no 187 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000fc; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332e45; asc     3.E;; 3: len 8; hex 800000000000003d; asc        =;;

Record lock, heap no 188 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000fb; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332e1a; asc     3. ;; 3: len 8; hex 800000000000003c; asc        <;;

Record lock, heap no 189 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000fa; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332def; asc     3- ;; 3: len 8; hex 800000000000003b; asc        ;;;

Record lock, heap no 190 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f9; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332dc4; asc     3- ;; 3: len 8; hex 800000000000003a; asc        :;;

Record lock, heap no 191 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f8; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332d99; asc     3- ;; 3: len 8; hex 8000000000000039; asc        9;;

Record lock, heap no 192 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f7; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332d6e; asc     3-n;; 3: len 8; hex 8000000000000038; asc        8;;

Record lock, heap no 193 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f6; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332d43; asc     3-C;; 3: len 8; hex 8000000000000037; asc        7;;

Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f5; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332d18; asc     3- ;; 3: len 8; hex 8000000000000036; asc        6;;

Record lock, heap no 195 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f4; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332ced; asc     3, ;; 3: len 8; hex 8000000000000035; asc        5;;

Record lock, heap no 196 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f3; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332cc2; asc     3, ;; 3: len 8; hex 8000000000000034; asc        4;;

Record lock, heap no 197 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f2; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332c97; asc     3, ;; 3: len 8; hex 8000000000000033; asc        3;;

Record lock, heap no 198 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f1; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332c6c; asc     3,l;; 3: len 8; hex 8000000000000032; asc        2;;

Record lock, heap no 199 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000f0; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332c41; asc     3,A;; 3: len 8; hex 8000000000000031; asc        1;;

Record lock, heap no 200 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000ef; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332c16; asc     3, ;; 3: len 8; hex 8000000000000030; asc        0;;

Record lock, heap no 201 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000ee; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332beb; asc     3+ ;; 3: len 8; hex 800000000000002f; asc        /;;

Record lock, heap no 202 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000ed; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332bc0; asc     3+ ;; 3: len 8; hex 800000000000002e; asc        .;;

Record lock, heap no 203 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000ec; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332b95; asc     3+ ;; 3: len 8; hex 800000000000002d; asc        -;;

Record lock, heap no 204 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000eb; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332b6a; asc     3+j;; 3: len 8; hex 800000000000002c; asc        ,;;

Record lock, heap no 205 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000ea; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332b3f; asc     3+?;; 3: len 8; hex 800000000000002b; asc        +;;

Record lock, heap no 206 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e9; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332b14; asc     3+ ;; 3: len 8; hex 800000000000002a; asc        *;;

Record lock, heap no 207 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e8; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332ae9; asc     3* ;; 3: len 8; hex 8000000000000029; asc        );;

Record lock, heap no 208 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e7; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332abe; asc     3* ;; 3: len 8; hex 8000000000000028; asc        (;;

Record lock, heap no 209 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e6; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332a93; asc     3* ;; 3: len 8; hex 8000000000000027; asc        ';;

Record lock, heap no 210 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e5; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332a68; asc     3*h;; 3: len 8; hex 8000000000000026; asc        &;;

Record lock, heap no 211 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e4; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332a3d; asc     3*=;; 3: len 8; hex 8000000000000025; asc        %;;

Record lock, heap no 212 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e3; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332a12; asc     3* ;; 3: len 8; hex 8000000000000024; asc        $;;

Record lock, heap no 213 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e2; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003329e7; asc     3) ;; 3: len 8; hex 8000000000000023; asc        #;;

Record lock, heap no 214 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e1; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003329bc; asc     3) ;; 3: len 8; hex 8000000000000022; asc        ";;

Record lock, heap no 215 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000e0; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332991; asc     3) ;; 3: len 8; hex 8000000000000021; asc        !;;

Record lock, heap no 216 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000df; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332966; asc     3)f;; 3: len 8; hex 8000000000000020; asc         ;;

Record lock, heap no 217 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000de; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033293b; asc     3);;; 3: len 8; hex 800000000000001f; asc         ;;

Record lock, heap no 218 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000dd; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332910; asc     3) ;; 3: len 8; hex 800000000000001e; asc         ;;

Record lock, heap no 219 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000dc; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003328e5; asc     3( ;; 3: len 8; hex 800000000000001d; asc         ;;

Record lock, heap no 220 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000db; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003328ba; asc     3( ;; 3: len 8; hex 800000000000001c; asc         ;;

Record lock, heap no 221 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000da; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033288f; asc     3( ;; 3: len 8; hex 800000000000001b; asc         ;;

Record lock, heap no 222 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d9; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332864; asc     3(d;; 3: len 8; hex 800000000000001a; asc         ;;

Record lock, heap no 223 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d8; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332839; asc     3(9;; 3: len 8; hex 8000000000000019; asc         ;;

Record lock, heap no 224 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d7; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033280e; asc     3( ;; 3: len 8; hex 8000000000000018; asc         ;;

Record lock, heap no 225 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d6; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003327e3; asc     3' ;; 3: len 8; hex 8000000000000017; asc         ;;

Record lock, heap no 226 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d5; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003327b8; asc     3' ;; 3: len 8; hex 8000000000000016; asc         ;;

Record lock, heap no 227 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d4; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033278d; asc     3' ;; 3: len 8; hex 8000000000000015; asc         ;;

Record lock, heap no 228 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d3; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332762; asc     3'b;; 3: len 8; hex 8000000000000014; asc         ;;

Record lock, heap no 229 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d2; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332737; asc     3'7;; 3: len 8; hex 8000000000000013; asc         ;;

Record lock, heap no 230 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d1; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033270c; asc     3' ;; 3: len 8; hex 8000000000000012; asc         ;;

Record lock, heap no 231 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000d0; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003326e1; asc     3& ;; 3: len 8; hex 8000000000000011; asc         ;;

Record lock, heap no 232 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000cf; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003326b6; asc     3& ;; 3: len 8; hex 8000000000000010; asc         ;;

Record lock, heap no 233 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000ce; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033268b; asc     3& ;; 3: len 8; hex 800000000000000f; asc         ;;

Record lock, heap no 234 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000cd; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332660; asc     3&`;; 3: len 8; hex 800000000000000e; asc         ;;

Record lock, heap no 235 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000cc; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332635; asc     3&5;; 3: len 8; hex 800000000000000d; asc         ;;

Record lock, heap no 236 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000cb; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033260a; asc     3& ;; 3: len 8; hex 800000000000000c; asc         ;;

Record lock, heap no 237 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000ca; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003325df; asc     3% ;; 3: len 8; hex 800000000000000b; asc         ;;

Record lock, heap no 238 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c9; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003325b4; asc     3% ;; 3: len 8; hex 800000000000000a; asc         ;;

Record lock, heap no 239 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c8; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332589; asc     3% ;; 3: len 8; hex 8000000000000009; asc         ;;

Record lock, heap no 240 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c7; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033255e; asc     3%^;; 3: len 8; hex 8000000000000008; asc         ;;

Record lock, heap no 241 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c6; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332533; asc     3%3;; 3: len 8; hex 8000000000000007; asc         ;;

Record lock, heap no 242 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c5; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332508; asc     3% ;; 3: len 8; hex 8000000000000006; asc         ;;

Record lock, heap no 243 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c4; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003324dd; asc     3$ ;; 3: len 8; hex 8000000000000005; asc         ;;

Record lock, heap no 244 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c3; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 000000003324b2; asc     3$ ;; 3: len 8; hex 8000000000000004; asc         ;;

Record lock, heap no 245 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c2; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332487; asc     3$ ;; 3: len 8; hex 8000000000000003; asc         ;;

Record lock, heap no 246 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c1; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 0000000033245c; asc     3$\;; 3: len 8; hex 8000000000000002; asc         ;;

Record lock, heap no 247 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000c0; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332431; asc     3$1;; 3: len 8; hex 8000000000000001; asc         ;;

Record lock, heap no 248 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32
 0: len 8; hex 80000000000000bf; asc         ;; 1: len 6; hex 0000000da62a; asc      *;; 2: len 7; hex 00000000332406; asc     3$ ;; 3: len 8; hex 8000000000000000; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 50 n bits 320 index `PRIMARY` of table `test/deadlock_test` trx id 0 894506 lock_mode X locks rec but not gap waiting
Record lock, heap no 169 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0
 0: len 8; hex 800000000000010e; asc         ;; 1: len 6; hex 0000000da62b; asc      +;; 2: len 7; hex 80000000340084; asc     4  ;; 3: len 8; hex 8000000000000065; asc        e;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 894509
Purge done for trx's n:o < 0 894506 undo n:o < 0 0
History list length 21
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 894465, not started, process no 14343, OS thread id 36874
MySQL thread id 1, query id 787 localhost root
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
31 OS file reads, 48 OS file writes, 42 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.89 writes/s, 0.76 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 5, seg size 7, is empty
Ibuf for space 0: size 1, free list len 5, seg size 7,
0 inserts, 0 merged recs, 0 merges
Hash table size 276707, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 10.73 non-hash searches/s
---
LOG
---
Log sequence number 0 350983987
Log flushed up to   0 350983987
Last checkpoint at  0 350971221
0 pending log writes, 0 pending chkp writes
37 log i/o's done, 0.58 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 92812006; in additional pool allocated 1399168
Buffer pool size   4096
Free buffers       4026
Database pages     69
Modified db pages  7
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 69, created 0, written 13
0.00 reads/s, 0.00 creates/s, 0.27 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 14335, id 28680, state: sleeping
Number of rows inserted 291, updated 0, deleted 290, read 290
6.47 inserts/s, 0.00 updates/s, 6.44 deletes/s, 6.44 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

How to repeat:
*) CREATE TABLE test.deadlock_test (
        id BIGINT  NOT NULL AUTO_INCREMENT,
        value BIGINT NOT NULL,
        PRIMARY KEY(id)
    ) TYPE = InnoDB; 
 
*) Java source code to recreate:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author Maxim
 */
public class MysqlDeadlockTest implements Runnable {

    /* 
     * CREATE TABLE test.deadlock_test (
     *      id BIGINT  NOT NULL AUTO_INCREMENT,
     *      value BIGINT NOT NULL,
     *      PRIMARY KEY(id)
     *      ) TYPE = InnoDB; 
     * 
     */

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private String url = "jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull&useServerPrepStmts=false";
    private String user = "";
    private String password = "";
    private Thread thread;

    //connection for each worker thread
    protected Connection connection;
    //unique values for each worker thread
    protected int start, end;

    public MysqlDeadlockTest(int start, int end) throws SQLException {
        connection = DriverManager.getConnection(url, user, password);
        this.start = start;
        this.end = end;
        thread = new Thread(this);
        thread.start();
    }

    public void startTransaction() throws SQLException {
        System.out.println(this + " Before start transaction");
        connection.setAutoCommit(false);
    }

    public void endTransaction() throws SQLException {
        connection.commit();
        System.out.println(this + " After end transaction");
    }

    public void run() {
        try {
            startTransaction();
            try {
                for (int i = start; i < end; i++) {
                    Statement stmt = connection.createStatement();
                    try {
                        String insert = "insert into deadlock_test (value) "
                                + "values (" + i + ");";
                        stmt.executeUpdate(insert);

                        String delete = "delete from deadlock_test where value = "
                                + i;
                        stmt.executeUpdate(delete);

                    } finally {
                        stmt.close();
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();

            } finally {
                endTransaction();
            }

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            try {
                connection.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    /**
     * main
     */
    public static void main(String[] args) {
        try {
            new MysqlDeadlockTest(0, 1000);
            new MysqlDeadlockTest(1001, 2000);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Java test

Attachment: MysqlDeadlockTest.java (text/x-java), 2.81 KiB.

INNODB monitor output after deadlock

Attachment: deadlock.out (application/octet-stream, text), 24.92 KiB.

Thank you for a bug report. It looks like a variation of well-known InnoDB problem http://bugs.mysql.com/bug.php?id=3300

I was able to reproduce it on 4.1.12-nt using a much simpler test scenario:

mysql> CREATE TABLE test.deadlock_test (
    ->         id BIGINT  NOT NULL AUTO_INCREMENT,
    ->         value BIGINT NOT NULL,
    ->         PRIMARY KEY(id)
    ->     ) TYPE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.81 sec)

Then, in session A:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into deadlock_test(value) values(1);
Query OK, 1 row affected (0.00 sec)

In session B:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into deadlock_test(value) values(10);
Query OK, 1 row affected (0.00 sec)

In session A:

mysql> delete from deadlock_test where value=1;

At this moment session A hangs...

In session B:

mysql> delete from deadlock_test where value=10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

At this moment in session A the following printed:
Query OK, 1 row affected (7.76 sec)

That's all. 

If you'll add index on deadlock_table(value), you'll get in 4.1.12 a wait in session B on the insert until the transaction in session A ended. And no deadlock, just locked session B...

This bug is similar to bug #3300. First delete has to wait for a lock because it does a full table access to the table deadlock_test. Thus it requests a X-lock to row (10) but can't have it because it is already locked. Similarly, second delete does a full table access and requires a X-lock to row (1) but can't have it, causing a deadlock. This might look little strange, because it means that both transaction's request a lock to a row which has been created by a uncommited insert. I was able to repeat this using 5.0.11. However, at the moment this is how InnoDB is designed to work and therefore this is a feature. I left this bug report open in a feature request state, to remind us to test also delete when we implement a new 'dirty' read version of update when innobase_locks_unsafe_for_binlog option is used.

This is duplicate of bug #3300.