Question :
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.
Questions:
-
How come
txn_id
is 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 ccc
is colliding withINSERT INTO ccc
of the next transaction?
MySQL is using the default REPEATABLE-READ isolation and all tables are innoDB, of course.
Edit
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
and 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
Answer :
Every InnoDB table really ought to have an explicit PRIMARY KEY
. If there is a “natural” PK of one (or more) unique columns, use that; else add a surrogate id INT UNSIGNED AUTO_INCREMENT
.
That is, if (y, z)
is unique, change it from being merely a KEY
to being PRIMARY KEY
. Else add an id
.