Mysql – Help me understand and resolve MySQL deadlock – “lock mode AUTO-INC waiting”

auto-incrementdeadlocklockingMySQLWordpress

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)

Best Answer

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

Related Solutions

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Mysql – Clarification on thesql deadlock situation

Your assumption is correct. Nevertheless, you need to pay attention to what is being locked.

Both transactions are locking

space id 10787
page no 139
n bits 176
index PERMISSION_ID of table IDENTITY.ROLE

The DB Connections 12287 and 12279 ran one INSERT each

Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='...' to fill in DOMAIN_ID. This would cause additional locks because a query's result is needed to fill DOMAIN_ID while other indexes are made to wait for the materialization of ROLE's primary key. This deadlock lasts a little longer that normal because of this intermittent need to run a SELECT.

SUGGESTION

Instead of doing

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT

SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks

Best Answer

Related Solutions

Mysql – Drop auto increment hack w/o alter table

Mysql – Clarification on thesql deadlock situation

SUGGESTION

Related Question