0

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)
1
  • Look at your code. MySQL does not record the rest of the transaction.
    – Rick James
    Jul 25, 2017 at 17:30

1 Answer 1

2

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

Your Answer

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

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