Understanding INSERT ... ON DUPLICATE KEY UPDATE deadlock scenario

I'm trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency. The two tables in question: hosts: "CREATE TABLE hosts (" + "id int(10) unsigned NOT NULL AUTO_INCREMENT," + "osquery_host_id varchar(255) NOT NULL," + "created_at timestamp DEFAULT CURRENT_TIMESTAMP," + "updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," + "deleted_at timestamp NULL DEFAULT NULL," + "deleted tinyint(1) NOT NULL DEFAULT FALSE," + "detail_update_time timestamp NULL DEFAULT NULL," + "node_key varchar(255) DEFAULT NULL," + "host_name varchar(255) NOT NULL DEFAULT ''," + "uuid varchar(255) NOT NULL DEFAULT ''," + "platform varchar(255) NOT NULL DEFAULT ''," + "osquery_version varchar(255) NOT NULL DEFAULT ''," + "os_version varchar(255) NOT NULL DEFAULT ''," + "build varchar(255) NOT NULL DEFAULT ''," + "platform_like varchar(255) NOT NULL DEFAULT ''," + "code_name varchar(255) NOT NULL DEFAULT ''," + "uptime bigint(20) NOT NULL DEFAULT 0," + "physical_memory bigint(20) NOT NULL DEFAULT 0," + "cpu_type varchar(255) NOT NULL DEFAULT ''," + "cpu_subtype varchar(255) NOT NULL DEFAULT ''," + "cpu_brand varchar(255) NOT NULL DEFAULT ''," + "cpu_physical_cores int NOT NULL DEFAULT 0," + "cpu_logical_cores int NOT NULL DEFAULT 0," + "hardware_vendor varchar(255) NOT NULL DEFAULT ''," + "hardware_model varchar(255) NOT NULL DEFAULT ''," + "hardware_version varchar(255) NOT NULL DEFAULT ''," + "hardware_serial varchar(255) NOT NULL DEFAULT ''," + "computer_name varchar(255) NOT NULL DEFAULT ''," + "primary_ip_id INT(10) UNSIGNED DEFAULT NULL, " + "PRIMARY KEY (id)," + "UNIQUE KEY idx_host_unique_nodekey (node_key)," + "UNIQUE KEY idx_osquery_host_id (osquery_host_id)," + "FULLTEXT KEY hosts_search (host_name)" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;", networks_interfaces: "CREATE TABLE network_interfaces (" + "id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT," + "host_id INT(10) UNSIGNED NOT NULL," + "mac varchar(255) NOT NULL DEFAULT ''," + "ip_address varchar(255) NOT NULL DEFAULT ''," + "broadcast varchar(255) NOT NULL DEFAULT ''," + "ibytes BIGINT NOT NULL DEFAULT 0," + "interface VARCHAR(255) NOT NULL DEFAULT ''," + "ipackets BIGINT NOT NULL DEFAULT 0," + "last_change BIGINT NOT NULL DEFAULT 0," + "mask varchar(255) NOT NULL DEFAULT ''," + "metric INT NOT NULL DEFAULT 0," + "mtu INT NOT NULL DEFAULT 0," + "obytes BIGINT NOT NULL DEFAULT 0," + "ierrors BIGINT NOT NULL DEFAULT 0," + "oerrors BIGINT NOT NULL DEFAULT 0," + "opackets BIGINT NOT NULL DEFAULT 0," + "point_to_point varchar(255) NOT NULL DEFAULT ''," + "type INT NOT NULL DEFAULT 0," + "PRIMARY KEY (id), " + "FOREIGN KEY idx_network_interfaces_hosts_fk (host_id) " + "REFERENCES hosts(id) " + "ON DELETE CASCADE, " + "FULLTEXT KEY ip_address_search (ip_address)," + "UNIQUE KEY idx_network_interfaces_unique_ip_host_intf (ip_address, host_id, interface)" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;", Lastest deadlock info: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-01-20 00:09:06 0x2b033abd2700 *** (1) TRANSACTION: TRANSACTION 78516922, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 286926, OS thread handle 47297573750528, query id 1045761878 10.107.51.236 username update INSERT INTO network_interfaces ( host_id, mac, ip_address, broadcast, ibytes, interface, ipackets, last_change, mask, metric, mtu, obytes, ierrors, oerrors, opackets, point_to_point, type ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), mac = VALUES(mac), broadcast = VALUES(broadcast), ibytes = VALUES(ibytes), ipackets = VALUES(ipackets), last_change = VALUES(last_change), mask = VALUES(mask), metric = VALUES(metric), mtu = VALUES(mtu), obytes = VALUES(obytes), ierrors = VALUES(ierrors), oerrors = VALUES(oerrors), opackets = VALUES(opackets), point_to_point = VALUES(point_to_point), type = VALUES(type) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table kolide.network_interfaces trx id 78516922 lock_mode Xinsert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION 78516915, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 18 lock struct(s), heap size 1136, 33 row lock(s), undo log entries 12 MySQL thread id 281276, OS thread handle 47292870371072, query id 1045761879 10.107.78.241 username update INSERT INTO network_interfaces ( host_id, mac, ip_address, broadcast, ibytes, interface, ipackets, last_change, mask, metric, mtu, obytes, ierrors, oerrors, opackets, point_to_point, type ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), mac = VALUES(mac), broadcast = VALUES(broadcast), ibytes = VALUES(ibytes), ipackets = VALUES(ipackets), last_change = VALUES(last_change), mask = VALUES(mask), metric = VALUES(metric), mtu = VALUES(mtu), obytes = VALUES(obytes), ierrors = VALUES(ierrors), oerrors = VALUES(oerrors), opackets = VALUES(opackets), point_to_point = VALUES(point_to_point), type = VALUES(type) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table kolide.network_interfaces trx id 78516915 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table kolide.network_interfaces trx id 78516915 lock_mode Xinsert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1) The program starts a transaction, updates a host row, and uses that same transaction in a loop through all the host's interfaces and issues a INSERT...ON DUPLICATE statement for each interface. As I understand it, because the transaction starts with a UPDATE (exclusive) lock on the host table, another transaction cannot be updating the same host. So I don't think this is a scenario where two connections are trying to update the same set of host interfaces (this could easily result in a deadlock). I think this might be due to different host updates competing over the AUTO_INCREMENT index of network_interfaces? I just don't understand how, even after staring at the MySQL docs about locks. I understand that transaction 1 is waiting on an exclusive insert lock, transaction 2 has an exclusive lock and is also waiting on an exclusive insert lock. What I'm specifically not understanding is why TRANSACTION 2 has the exclusive lock lock_mode X to begin with.