------------------------
LATEST DETECTED DEADLOCK
------------------------
100722  9:11:30
*** (1) TRANSACTION:
TRANSACTION 0 79863245, ACTIVE 5 sec, process no 30967, OS thread id 1180342592 starting index read
mysql tables in use 4, locked 2
LOCK WAIT 6 lock struct(s), heap size 3024, 718 row lock(s)
MySQL thread id 582442, query id 30899169 172.30.5.38 abc preparing
SELECT
               m.id,
               m.default_alloy,
               m.description,
               m.screen_message,
               d.name as department_name,
               d.id as department_id,
               m.requires,
               m.optional,
               m.force_single_unit,
               m.turn_around_days,
               m.sort_rank
           FROM
               abc_lab_800020.macros as m
           LEFT JOIN
               abc_lab_800020.departments as d
           ON
               m.department_id = d.id WHERE
                           m.id IN (
                               SELECT DISTINCT
                                   p.macro_id
                               FROM
                                   abc_lab_800020.macros_practices as p
                               WHERE
                                   p.practice_id = '2353'
                           )
 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14132 page no 232 n bits 1120 index `IDX_macros_practices_1` of table `abc_lab_800020`.`macros_practices` trx id 0 79863245 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 3; hex 414142; asc AAB;; 1: len 6; hex 000002b0a22c; asc      ,;;

*** (2) TRANSACTION:
TRANSACTION 0 79863205, ACTIVE 23 sec, process no 30967, OS thread id 1179810112 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
875 lock struct(s), heap size 161776, 191899 row lock(s), undo log entries 127795
MySQL thread id 582439, query id 30915183 172.30.5.38 abc updating
DELETE FROM
                           abc_lab_800020.macros_practices
                       WHERE
                           macro_id = 'A'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 14132 page no 232 n bits 1152 index `IDX_macros_practices_1` of table `abc_lab_800020`.`macros_practices` trx id 0 79863205 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

....

CREATE TABLE parent (
       id INTEGER PRIMARY KEY,
       name varchar(10)
) ENGINE=InnoDB;

CREATE TABLE child (
       id INTEGER PRIMARY KEY,
       parent_id INTEGER NOT NULL,
       FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=InnoDB;

insert into parent (id, name) values (1, 'charlie');

-- in thread 1:
SET autocommit=0;
INSERT INTO child VALUES (1,1);
-- in thread 2:
SET autocommit=0;
UPDATE parent SET name ='alice' WHERE id = 1;

UPDATE parent SET name ='bob' WHERE id = 1;

Paramétrage de mysql-server-5.1 (5.1.37-1ubuntu5.1) ...
 * Stopping MySQL database server mysqld                                 [ OK ]
100309 18:37:33 [Note] Plugin 'FEDERATED' is disabled.
100309 18:37:33  InnoDB: Started; log sequence number 0 44233
100309 18:37:33  InnoDB: Starting shutdown...
100309 18:37:35  InnoDB: Shutdown completed; log sequence number 0 44233
100309 18:37:35 [Warning] Forcing shutdown of 1 plugins
 * Starting MySQL database server mysqld