Percona pt-deadlock-logger strange results

Posted on

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:

  1. How come txn_id is 0? Does that mean that pt-deadlock-logger could not extract the transaction id?

  2. 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 with INSERT 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.

Leave a Reply

Your email address will not be published. Required fields are marked *