Tag: Update

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.