Re: innodb deadlock on UPDATE
Here's what you requested, plus some additoinal informatoin. Note that I have not included all queries as there are thousands witin each transaction. I have assumed what statements are relevant.
CREATE TABLE `UNIT_COUNTS` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`M_ID` tinyint(3) unsigned NOT NULL,
`SS_ID` smallint(5) unsigned NOT NULL,
`FFID` tinyint(3) unsigned NOT NULL,
`S_ID` smallint(5) unsigned NOT NULL,
`IS_BACKUP` tinyint(1) NOT NULL,
`PERIOD` enum('day','week','year','all') NOT NULL,
`D_START` date NOT NULL,
`UC_TYPE` enum('indiv','group','subgroup','elem','aton','setA','setB','setC','setD') NOT NULL,
`E_ID` smallint(5) unsigned DEFAULT NULL,
`RN` tinyint(4) NOT NULL DEFAULT '0',
`T_EARLIEST` datetime DEFAULT NULL,
`T_LATEST` datetime DEFAULT NULL,
`N_PTS` int(10) unsigned DEFAULT '0',
`AVG_DUR` float NOT NULL,
PRIMARY KEY (`ID`),
KEY `UNIT_COUNTS_FK_S_SSD` (`SS_ID`),
KEY `UNIT_COUNTS_FK_E` (`E_ID`),
KEY `UNIT_COUNTS_FK_S_ID` (`S_ID`),
KEY `M_ID_UNIT_COUNTS` (`M_ID`),
CONSTRAINT `M_ID_UNIT_COUNTS` FOREIGN KEY (`M_ID`) REFERENCES `M` (`ID`) ON UPDATE NO ACTION,
CONSTRAINT `UNIT_COUNTS_FK_E` FOREIGN KEY (`E_ID`) REFERENCES `E` (`ID`) ON UPDATE NO ACTION,
CONSTRAINT `UNIT_COUNTS_FK_S_ID` FOREIGN KEY (`S_ID`) REFERENCES `S` (`ID`) ON UPDATE NO ACTION,
CONSTRAINT `UNIT_COUNTS_FK_S_SSD` FOREIGN KEY (`SS_ID`) REFERENCES `SSD` (`ID`) ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=91042 DEFAULT CHARSET=utf8
13 Query SAVEPOINT SAVEPT13415
...
13 Query SELECT ID FROM UNIT_COUNTS WHERE M_ID = 4 AND S_ID = 5 AND IS_BACKUP = 0 AND PERIOD = 3 AND UC_TYPE = 8 AND RN = 0
13 Query SELECT ID FROM UNIT_COUNTS WHERE M_ID = 4 AND SS_ID = 3 AND FFID = 2 AND S_ID = 10 AND IS_BACKUP = 0 AND PERIOD = 2 AND D_START = '2004-02-09' AND UC_TYPE = 2 AND E_ID IS NULL AND RN = 0
13 Query SELECT ID FROM UNIT_COUNTS WHERE M_ID = 4 AND SS_ID = 3 AND FFID = 2 AND S_ID = 6 AND IS_BACKUP = 0 AND PERIOD = 3 AND UC_TYPE = 1 AND E_ID IS NULL AND RN = 0
13 Query SELECT ID FROM UNIT_COUNTS WHERE M_ID = 4 AND SS_ID = 3 AND FFID = 2 AND S_ID = 6 AND IS_BACKUP = 0 AND PERIOD = 1 AND D_START = '2004-02-13' AND UC_TYPE = 1 AND E_ID IS NULL AND
...
13 Query UPDATE UNIT_COUNTS SET T_EARLIEST = '2004-02-13 04:10:48', T_LATEST = '2004-02-13 19:08:12', N_PTS = 1, AVG_DUR = 31.130318 WHERE ID = 91042
...
13 Query INSERT INTO UNIT_COUNTS VALUES (NULL,4,3,2,5,0,3,'1997-01-01',9,21,0,NULL,NULL,NULL, 0.0)
13 Query SELECT last_insert_id()
13 Query UPDATE UNIT_COUNTS SET T_EARLIEST = '2004-02-13 04:32:23', T_LATEST = '2004-02-13 18:47:24', N_PTS = 1, AVG_DUR = 31.437500 WHERE ID = 91043
...
150708 20:19:13 13 Query DELETE FROM UNIT_COUNTS WHERE UC_TYPE=4 AND PERIOD=3 AND M_ID=4 AND N_PTS=0
13 Query DELETE FROM UNIT_COUNTS WHERE UC_TYPE=6 AND PERIOD=3 AND M_ID=4 AND N_PTS=0
...
13 Query commit
Additional Information
-----------------------
There are two connections to the database running at the same time. Both are from the same utility, so both send the same statements and queries. They differ on the rows of UNIT_COUNTS they use. One connection only reads and writes to rows with M_ID = 4, and the other rows with M_ID = 3.
The deadlock is only seen when both connections are up and running.
Each connection uses transactions.