MySQL Forums
Forum List  »  InnoDB

Re: innodb deadlock on UPDATE
Posted by: Michael Leitch
Date: July 08, 2015 09:52PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2052
July 06, 2015 09:37AM
929
July 08, 2015 07:24PM
Re: innodb deadlock on UPDATE
1334
July 08, 2015 09:52PM
1010
July 09, 2015 11:52PM
946
July 15, 2015 12:00PM
879
July 17, 2015 03:43PM
844
July 17, 2015 04:42PM
841
July 24, 2015 03:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.