Unexpected occasional DEADLOCK when re-recreating database from split MySQL dump

Question

To replace the content of a test database from a production database mysqldump output the following command was used:

cat mysqldump-db-thisdate.sql | mysql -p ... mydb

There has never been any issue with this command.

However the DB grew a lot and this command takes several minutes.

In order to reduce this time, a Perl script was written that

• takes the mysqldump output as input
• creates a single file having all DROP TABLE ... CREATE TABLE for each table
• run this drop-creation file on a single thread, before doing the tables feeding below
• creates as many files (see below) as there are tables (about 100 tables)
• makes a fork() for each table file that is injected into the DB (all tables are dropped and created + fed in "parallel". table1..100

The DROP-CREATION file is something like

DROP TABLE IF EXISTS `mytable1`;
CREATE TABLE `mytable1` (
 `someid1` int NOT NULL,
 ...
 PRIMARY KEY (`someid1`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
DROP TABLE IF EXISTS `mytable2`;
CREATE TABLE `mytable2` (
 `someid2` int NOT NULL,
 ...
 PRIMARY KEY (`someid2`)
) ENGINE=InnoDB AUTO_INCREMENT=222 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
...

Each table file mytableI.sql is like this, for instance for mytable1.sql

/*!40103 SET TIME_ZONE='+00:00' */;
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;
START TRANSACTION;
LOCK TABLES `mytable1` WRITE;
 INSERT INTO `mytable1` VALUES (...),(...),...;
UNLOCK TABLES;
COMMIT;

It's like doing, in parallel (pseudo code)

for each table 1 to 100 do
 cat mytableI.sql | mysql -p ... mydb /* I is 1 ... 100 */
end for

This method works very well, and saves from 50% to 75% of the time compared to the simple cat whole-dump | mysql usual method.

However, from time to time (maybe 1 / 10), doing this parallel method, mysql throws an error

Deadlock found when trying to get lock; try restarting transaction

It happens rarely, so just restarting the command is not a big deal.

But why? Each table is processed at once, foreign keys are not checked... Doesn't MySQL, thanks to "LOCK TABLES" (and other mechanisms) protect itself against deadlocks in this case?

Addendum: The mydb test database is not being accesses otherwise.

---

edit testing other methods

Trying to perform the DROP / CREATE operations in parallel, (each DROP / CREATE in the same thread, for each table), not even filling the tables with data, plenty of Deadlocks occur...

Could it be that MySQL does not handle very well DROP/CREATE operations performed simultaneously? (should be done by a single DB admin?)

Note:

"simultaneously" and "in parallel" meaning each thread has its own MySQL connection.

• Luuk: Did you check SHOW ENGINE INNODB STATUS \G ? (see: How do I find which transaction is causing a "Waiting for table metadata lock" state?) with 100 processes, trying to create a table, chances are ... I would split the complete dump not in 100 files, but in 4, or maybe 8, and check if time is acceptable when doing so. A too high number of concurrent loads will not improve speed.
• Déjà vu: Actually I didn't give all details, but I tried many values and came up with an ideal 12 threads simultaneously on 6 cores (each time a thread dies a new one is created, up to 12 at any moment). In this case, deadlocks are pretty seldom, but still happen 1 out of 10 times. (please read my edit for DROP/CREATE in //)
• Luuk: Try to increase innodb_lock_wait_timeout, or lower the number of threads. (Because 12 might not be the ideal value, because of this incidental issue) (see: stackoverflow.com/questions/5836623/… )
• Déjà vu: Your comment makes sense (after reading @Rolando's answer)

Answer 1

Please keep in mind that performing DDL such as DROP TABLE and CREATE TABLE will make changes in the InnoDB system tablespace. It manages the assignment of tablespace IDs to newly created tables.

Tablespace IDs in the system tablespace are auto-incremented and used with the creation of a new table.

No matter how much parallelism you apply, the assignment of tablespace IDs are always serialized.

If you create two tables in two separate threads, there is a likelihood that one CREATE TABLE has to wait for the other CREATE TABLE while a tablespace ID gets assigned to the first table.

If you create one table in one thread and drop a table in another thread, there is a likelihood that there will be some tablespace ID management taking place.

Someone actually encountered running out of tablespace ids

Please create all the tables in a single thread. That is the only way to eliminate this issue.

• Déjà vu: It's what I'm doing actually (all tables are dropped/created in sequence, first), yet rarely a deadlock happens. Tbh, that didn't happen for a long time and - maybe - the couple times it happened was due to another process (website) using the DB(?). Note: the deadlock always happened during the // INSERTs phase. Anyway this had me worrying about how MySQL handles semaphores (/critical sections), and wondered if - maybe - there could be a race condition hiding somewhere in the MySQL code...

Answer 2

The SHOW ENGINE INNODB STATUS as suggested by @Luuk in the comment (Unexpected occasional DEADLOCK when re-recreating database from split MySQL dump) will show the latest deadlock and its cause.

Alternatively, you can log all deadlocks to error.log by enabling innodb_print_all_deadlocks (https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks)

The deadlocks might occur when a table and its referencing/referenced table are in the same time being locked and the lock takes more than innodb_lock_wait_timeout seconds.

When the commit being executing, the disabled checks are re-enabled. The foreign key constraints will be validated against the referenced table. The validation can be done if the referenced table is not locked.

If the referenced table is locked, the validation step will wait for innodb_lock_wait_timeout seconds. If the lock released before timeout, the validation can continue. Otherwise, deadlock event triggered and transaction rolled back.

Since you already separate the table creations to DROP-CREATION file, I would suggest you to separate the foreign key constraints creation to, say, CONSTRAINTS file that will be executed after all the table inserts are finished.

Your workflow will like this:

• takes the mysqldump output as input
• creates a single file having all DROP TABLE ... CREATE TABLE for each table, excluding foreign key constraints
• creates a single file CONSTRAINTS containing all constraint creations for each table, containing something like ALTER TABLE x ADD FOREIGN KEY ...
• run this drop-creation file on a single thread, before doing the tables feeding below
• creates as many files (see below) as there are tables (about 100 tables)
• makes a fork() for each table file that is injected into the DB (all tables are dropped and created + fed in "parallel". table1..100
• run the CONSTRAINTS file on a single thread, after the tables feeding finished

HTH