3

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.

Improve this question
4
  • Kindly have a look at this question dba.stackexchange.com/a/87004/199622 It is similar to your question.
    – Phemelo Khetho
    Jan 24, 2020 at 22:48
  • is there a good cause why you don't use a trigge after insert and there you make your insert on duplicate. I think yoi are making the transaction because of the LAST_INSERT_ID(id), there is no cause for that
    – nbk
    Jan 25, 2020 at 23:08
  • Can you add explain plan of query ?
    – JYOTI RAJAI
    Jan 27, 2020 at 1:06
  • Consider creating tbl named network_interfaces_dkupd_detected similar to network_interfaces without AUTO_INCREMENT and rather than A) on duplicate key update - B) on duplicate key insert into network_interfaces_dkupd_detected and add the two columns missing which are host_id = VALUES(host_id), and ip_address = VALUES(ip_address), Please let me know if it works.
    – Wilson Hauck
    Jan 30, 2020 at 3:01

1 Answer 1

Reset to default
0

It may not be possible to eliminate all deadlocks; so write code to check for deadlocks and replay the transaction that gets such an error.

Many deadlocks can be avoided by speeding up the queries in question. Let's look at what is slowing down the IODKU you have presented.

Having multiple unique indexes aggravates the situation. This is because it must check each of them before committing the INSERT.

Do you really need all three?

    "PRIMARY KEY (`id`)," +
    "UNIQUE KEY `idx_host_unique_nodekey` (`node_key`)," +
    "UNIQUE KEY `idx_osquery_host_id` (`osquery_host_id`)," +

FOREIGN KEYs are handy in some situations, and I see you have a CASCADE DELETE. I suspect this something else that must be handled before committing, thereby slowing down the transaction.

Having both of ip_address, host_id in the same unique index seems "wrong". It seems like the ip_address would uniquely identify the host_id. That is, remove host_id from idx_network_interfaces_unique_ip_host_intf??

Don't blindly use (255) for all strings. (Perhaps that does not matter for this Question, but it does matter is several minor ways.)

Is each IODKU a transaction unto itself? That is, are you running with autocommit=ON and not using BEGIN..COMMIT? There is overhead for each "transaction". There are some ways you might cut back on this overhead:

  • innodb_flush_log_at_trx_commit = 2 -- This gives you speed, but risks loss in a crash.
  • BEGIN; do multiple IODKUs; COMMIT -- to amortize the overhead.

  • Upsert multiple rows at one time (also amortizing). (Caveat: the auto_increment values are harder to get.)

    INSERT INTO .. ( ... )
    ON DUPLICATE UPDATE ... x=VALUES(x) ...
    SELECT ... ((multiple rows)) ...;
Improve this answer
2
  • You mention the three keys slowing things down, but those keys are in the hosts table. The IODKU is happening on the network_interfaces table. Every time a host is updated, the queries are all wrapped in single BEGIN..COMMIT transaction. Which is an update on the host table and then ~2-10 IODKUs on it's associated network interfaces.
    – billcobbler
    Jan 27, 2020 at 18:54
  • @billcobbler - OK, I stand corrected.
    – Rick James
    Jan 27, 2020 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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