Mysql insert into deadlock with duplicate key

Question

In the logs, I have deadlocks associated with this request:

INSERT INTO `driver_state` (id, state)
  VALUES('83799','waiting')
ON DUPLICATE KEY UPDATE    
  state = IF(state = 'active', state, VALUES(state));

Exact error:

ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction 

I tried to figure out and understand how this query can even cause a dead end, but not very far.

Table structure

 CREATE TABLE IF NOT EXISTS `driver_state` (
   `id` int(11) NOT NULL,
   `state` enum('inactive','waiting_orders','has_order','busy') DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

In this case, we use the MEMORY table, but with InnoDB it has the same locking problems.

The table receives a lot of these insertion elements on repeated key queries and selects the queries (all selected queries are very fast and optimized). Transactions are not used anywhere except for manual blocking, etc.

Could you suggest any ideas that could trigger this?

Answer 1

Why is there an IF statement in your ON ON DUPLICATE KEY UPDATE?

, . , , , , .

ON DUPLICATE KEY UPDATE state = 'waiting'?

, , , , IF.

, .