Strange deadlock with multi-column index with innodb on MySQL

I'm seeing some odd (to me) behavior in MySQL. Let's start with the table that I'll be talking about.

CREATE TABLE `active_foo` (
  `active_foo_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c_id` bigint(20) NOT NULL,
  `aa_id` bigint(20) NOT NULL,
  `bar` varchar(60) DEFAULT NULL,
  `foo_string` varchar(60) NOT NULL,
  `handle_id` bigint(20) NOT NULL DEFAULT '-1',
  `hostname` varchar(64) DEFAULT NULL,
  `usage` int(11) DEFAULT NULL,
  `foo_id` int(11) NOT NULL,
  `bucket_id` int(11) NOT NULL,
  PRIMARY KEY (`active_foo_id`),
  KEY `idx_active_foo_foo_config` (`foo_id`),
  KEY `idx_active_foo_aa` (`aa_id`),
  KEY `foo_index` (`c_id`,`foo_id`,`foo_string`),
  KEY `idx_active_foo_buckets1` (`bucket_id`),
  KEY `handle_idx` (`handle_id`),
  KEY `host_idx` (`hostname`),
  CONSTRAINT `fk_active_foo_aa_idx` FOREIGN KEY (`aa_id`) REFERENCES `aa` (`access_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_active_foo_buckets1` FOREIGN KEY (`bucket_id`) REFERENCES `foo_buckets` (`bucket_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_active_foo_foo_config` FOREIGN KEY (`foo_id`) REFERENCES `foo_config` (`foo_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I have two inserts that look something like this:

insert into active_foo(c_id, foo_string, `usage`, foo_id, bucket_id, aa_id) 
values(6, '#TAG', 1, 1, 2, 3268192)

and

insert into active_foo(c_id, foo_string, `usage`, foo_id, bucket_id, aa_id) 
values(7, '#TAG', 1, 1, 1, 3268193)

Somehow they appear to be deadlocking on the same index, but I'm puzzled because I'd expect a deadlock to happen only if there is a second index involved that one holds and the other waits for. The latest deadlock report seems to indicate that they are in fact waiting on the same index, which I would think would just cause a small delay, not a deadlock.

```
180904 12:32:02
*** (1) TRANSACTION:
TRANSACTION 34018365, ACTIVE 1 sec inserting
mysql tables in use 1, locked

lock_mode X locks gap before rec insert

The key words to pull from there is "locks gap". MySQL has Gap Locks which locks the gaps between index records within the page. Since the second record inserted in adjacent to the first record, they are both waiting on the same gap lock.

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/


A gap lock is a lock on the gap between index records. Thanks to this gap lock, when you run the same query twice, you get the same result, regardless other session modifications on that table. This makes reads consistent and therefore makes the replication between servers consistent. If you execute SELECT * FROM id > 1000 FOR UPDATE twice, you expect to get the same value twice. To accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps between them with a shared gap lock.

You're not the only one experiencing these:
MySql Gap Lock Deadlock on Inserts. No real resolution on that one.

StackExchange Database Administrators Q#218667, answer score: 3