-1

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
  • Do you have the query that was running? Does ccc have this composite index: INDEX(y, z)? Show us SHOW CREATE TABLE for both tables. – Rick James Oct 19 '16 at 1:21
  • Added the output you've asked for. I understand that the indices help reduce locking but still see no reason why the two independent tables should somehow affect one another. (No idea what you mean by "the query that was running". You can see both queries in the pt-deadlock-logger output). Any ideas or points to check will be much appreciated. Thanks! – nimrodm Oct 19 '16 at 3:39
0

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.

  • (y, z) is not unique and if I understand correctly InnoDB will generate a primary key based on some internal counter. What's the advantage of adding a primary key (autoincrement I guess) explicitly? Besides the visibility? – nimrodm Oct 19 '16 at 17:48
  • This question and another currently-open question have these in common: (1) unexplained deadlock, and (2) hidden PK. – Rick James Oct 19 '16 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.