Help me understand and resolve MySQL deadlock - "lock mode AUTO-INC waiting"

Question

I am experiencing an issue with MySQL deadlocks and here is what I see in the MySQL error log:

2017-07-24 09:09:29 7fd5235db700InnoDB: transactions deadlock detected, dumping detailed information.
2017-07-24 09:09:29 7fd5235db700
*** (1) TRANSACTION:
TRANSACTION 177707078, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 20887150, OS thread handle 0x7fd522d7a700, query id 918997514 10.80.11.16 staging update
INSERT INTO `n0_table` (`post_id`, `meta_key`, `meta_value`) VALUES (411902, 'from_header', 'barberklingen <****************>')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 480 page no 44003 n bits 768 index `post_id` of table `staging`.`n0_table` trx id 177707078 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 177706917, ACTIVE 1 sec setting auto-inc lock
mysql tables in use 1, locked 1
121 lock struct(s), heap size 13864, 15196 row lock(s), undo log entries 93
MySQL thread id 20887160, OS thread handle 0x7fd5235db700, query id 918997545 10.80.11.15 staging update
INSERT INTO `n0_table` (`post_id`, `meta_key`, `meta_value`) VALUES (411901, '_wc_customer_order_csv_export_customer_is_exported', '0')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 480 page no 44003 n bits 760 index `post_id` of table `staging`.`n0_table` trx id 177706917 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `staging`.`n0_table` trx id 177706917 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)

Currently the MySQL InnoDB lock mode is:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 0     |
+--------------------------+-------+

The hosting setup for this website which generates those deadlocks is the following:

• Requests go to two load balancers and from there are forwarded to 4 application servers.
• The application servers then use one and the same MySQL master database.
• There is a slave which is configured in case the master fails.

I am trying to figure out why those deadlocks are occurring and also how to prevent them. In my understanding the auto increment should be working without deadlocks in this case but I am probably missing something. Thank you and let me know if case other information is required.

UPDATE:

Here is the show create table:

mysql> show create table n0_table\G;
*************************** 1. row ***************************
       Table: n0_table
Create Table: CREATE TABLE `n0_table` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=8014133 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Answer 1

The schema for n0_table looks like it is straight from Wordpress? It has multiple inefficiencies. Revamp it to make queries faster and either eliminate or decrease the frequency of the original problem.

Details: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta