Mysql – Understanding INSERT … ON DUPLICATE KEY UPDATE deadlock scenario

deadlockinnodbMySQL

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.

Best Answer

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)) ...;
```

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – Getting a Deadlock,

The system is starting to complain about the number of comments.

I'd be interested in seeing the statements as they are sent to the server - i.e. the fully generated SQL statements, i.e. with real values, not ? parameter place holders.

The statement that appears to be causing the issue is this one.

INSERT INTO `inventoryitems` VALUES (DEFAULT, 4, '595', null, 5390001, 5, 1, 1, '', -1, 0, -1, '')

Now, inventory items has a FOREIGN KEY constraint

CONSTRAINT FK_inventoryitems_1 FOREIGN KEY (characterid) REFERENCES characters (id) ON DELETE CASCADE

What is the result of the following query

SELECT COUNT(*) FROM characters WHERE characters.id = 595;

The values here are also of interest.

ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

[EDIT]

I'm starting to get some insight into what may be going on. You appear to be using some sort of framework for your database access (that's the reason why your SQL has '595' for an integer insert instead of 595.

It's possible that the transaction isolation level is being set by the framework to something other than read committed (see here). Try and ensure that your transactions use read committed - set the transaction level. Try adding this code to yours after the line

Connection con = DatabaseConnection.getConnection(); con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // Added line.

Also, check out this and the rest of the thread.

[EDIT] in response to "How do i check it? , can you guide me?"

What I meant was for you to add the line above to your code and rerun your system to the point where you got your original error. Do you still get an error? If you do, then is it different to the original error? If it is different, then post the error text here. HTH.

Could you take a dump of your database and put it online? You can anonymise anything sensitive. Also, zip up your Java code and I'll run that here against your schema. My Java's a bit rusty, but running it on a different system might give us some clues! :-)

[EDIT] in response to OP's comment "can you give me an example of indexes [code example] ?".

See here (you can also use the KEY keyword as a synonym for INDEX - see the docco here. You should add to your inventoryitems CREATE TABLE statement lines such as

INDEX `ii_type_ix` (type), 
INDEX `ii_account_id_ix` (accountid),

As for changing your Java code - I'm not 100% sure, but the problem lies with the database so your efforts should be concentrated there. Where you can change the code is in varying the order of your queries (but keeping the original functionality).

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Getting a Deadlock,

Related Question