Running the deadlock detector from Percona Toolkit, I get something like the following (MySQL 5.7 / AWS RDS, reformatted for clarity)
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query xxx.amazonaws.com 2016-10-18T18:20:08 241 0 105 root 172.31.10.209 mydb ccc GEN_CLUST_INDEX RECORD X w 0 DELETE FROM ccc WHERE y=25 AND z <= 15939 xxx.amazonaws.com 2016-10-18T18:20:08 296 0 42 root 172.31.10.209 mydb rrr rrr_index RECORD X w 1 INSERT INTO rrr(...) values (...),
The above DELETE and INSERT statements are executed within a single transaction which usually does just an
INSERT INTO rrr and
INSERT INTO ccc and occasionally executes a
DELETE FROM ccc.
However, given the different thread ids (241 vs 296) it seems like the first might belong to transaction A which still hasn't finished (and does include the
DELETE) when another iteration kicked in and generated transaction B.
txn_idis 0? Does that mean that pt-deadlock-logger could not extract the transaction id?
I can't see how can MySQL deadlock on two unrelated tables? Or is it just the logging incorrect and the
DELETE FROM cccis colliding with
INSERT INTO cccof the next transaction?
MySQL is using the default REPEATABLE-READ isolation and all tables are innoDB, of course.
Here's the output of
SHOW CREATE TABLE ccc:
CREATE TABLE `ccc` ( `parent` int(11) NOT NULL, `child` int(11) NOT NULL, `z` bigint(20) unsigned NOT NULL, `y` int(11) NOT NULL, KEY `y_and_z` (`y`,`z`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
SHOW CREATE TABLE rrr:
CREATE TABLE `rrr` ( `item_url` varchar(1024) COLLATE utf8_unicode_ci NOT NULL, `xxx` int(10) unsigned NOT NULL, `y` int(11) NOT NULL, `z` int(11) NOT NULL, KEY `y_and_z` (`y`,`z`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci