Importing a Large CSV Into MySQL Without Locking the Table

The usual worry about a big LOAD DATA is that it will lock the table and stop the application. On InnoDB, that is not what happens, and it is worth knowing exactly what does, because the real problems with a large load are different: a long transaction that holds back purge, a table that is half old and half new, and a replica that falls behind. This article measures the first, tests the common fix of chunking, and shows the pattern that removes the visible partial state. The procedure and the settings that stop a load from starting are in the MySQL import guide. The statement's clauses are explained in using LOAD DATA INFILE, and the same load on PostgreSQL is in the PostgreSQL guide, where the parallel version is measured.

What other sessions experience

I started a 2,000,000 row LOAD DATA in one thread against a table that already held two rows. In another thread, every 200 ms, I ran a SELECT COUNT(*) and inserted one unrelated row with a negative key, so it could not collide with the incoming ids:

python mysql_concurrency.py

load took 6.5 s while a second session ran 21 counts and 21 single row inserts
counts seen during the load: [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22]  (rows in the table afterwards: 2,000,023)
SELECT COUNT(*) during the load: median 44 ms, slowest 124 ms
INSERT of an unrelated key during the load: median 2 ms, slowest 22 ms

in 20 chunks with a commit after each: median 5.79 s
swap with RENAME TABLE took 21 ms; orders now has 2,000,000 rows

The first four lines are the result of that test. The counts the second session saw rose from 2 to 22, and every increment is one of its own inserts: the 2,000,000 loaded rows were invisible to it until the commit, after which the table held 2,000,023 rows. That is InnoDB's multi-version reads at work. The manual's page on consistent nonlocking reads says a consistent read sets no locks and other sessions are free to modify the table meanwhile, so a plain SELECT reads a snapshot and does not wait for a transaction that is changing rows. The count itself took a median of 44 ms, slower than normal because the server was busy, and 124 ms at worst. The unrelated INSERT took a median of 2 ms and 22 ms at worst. The load did not lock the table against readers or writers.

What would have blocked: a writer touching the same keys, which waits on a row lock until the load commits or rolls back; a statement that needs a metadata lock, such as ALTER TABLE, which waits behind the load's metadata lock and then makes every new query queue behind itself; and anything that needs the table to be quiet, such as LOCK TABLES. If your application runs DDL on the same table during a load, expect it to wait.

Does chunking help

The common advice is to break the file into pieces and commit after each, so that no single transaction is large. I split the same file into 20 pieces of 100,000 lines and loaded them one after another with a commit between:

The result was 5.79 s against 5.86 s for the single statement on the earlier run. There was no gain in speed, which is what I expected, since the work is the same. What changes is the shape of the transaction, and that has costs and benefits.

A single 2,000,000 row transaction keeps its undo information until it commits, and a long transaction holds back the purge of old row versions for everyone else. On a busy table that updates frequently, the history list grows while the load runs. For a 130 MB file the effect is small; for a 40 GB one it is a real concern. Chunking releases the undo after each commit. It also bounds the cost of a failure: a chunk that fails rolls back 100,000 rows, not two million, and you restart from the failed chunk, if you recorded which chunks finished.

Chunking has one serious cost, which is visibility. After 7 of 20 chunks, other sessions see 700,000 of the new rows. For a table that is a stand-in for the file, that is a state that should never be visible. For an append only log table it is fine.

Two warnings about how to split. Cutting a file with split -l is only safe if no quoted field contains a line break; the quoting article shows a file damaged by it. And remove the header from every chunk, or drop IGNORE 1 LINES and strip the first line once; my chunks were cut from the body only, so I used no IGNORE clause.

Build beside and swap

The pattern that avoids both the long transaction's visibility and chunking's partial state is to build the new data in a separate table and replace the live one in one atomic step. Create orders_new with the same definition, load into it, check it, then swap:

RENAME TABLE orders TO orders_old, orders_new TO orders;

RENAME TABLE with several pairs is atomic: the manual says no other session can access any of the tables while the rename is in progress, and that if an error occurs, no changes are made. In my run it took 21 ms against a table of 2,000,000 rows, because the statement changes names and not data, and the lock it needs is a metadata lock held for that instant. Queries running against the old table finish against it, and new queries see the new one. The last line of the output above is the result:

swap with RENAME TABLE took 21 ms; orders now has 2,000,000 rows.

That swap is the last step of the checked load in the import guide, which refuses to swap when the load produced a warning or the count is wrong. Three practical points: the new table needs the same indexes, foreign keys and grants as the old one, and CREATE TABLE orders_new LIKE orders copies the columns and indexes but not the foreign keys or the grants, so add those before the swap; anything with a foreign key pointing at orders is updated to point at orders_old after the swap, because the manual says referencing keys follow the renamed table, which is a reason this pattern fits tables without incoming keys; and views and stored routines refer to the name, so they follow the swap, which is usually what you want.

The old table, now orders_old, is your rollback. Keep it until the new data has been used for a day, then drop it. Dropping a large table takes time and disk I/O, so do it at a quiet hour.

Settings that matter, and ones that do not

unique_checks = 0 and foreign_key_checks = 0 are the settings that blogs suggest. In the hub's benchmark they changed nothing for this table, 5.84 s against 5.86 s, because it has no secondary indexes or foreign keys for them to skip; a table with several secondary indexes and a foreign key would see a difference, and one with incoming keys is better left alone. The innodb_flush_log_at_trx_commit setting matters for many small commits, not for one large one, so chunking with = 2 could change the chunked time and would risk losing the last second of commits in a crash.

Binary logging is the cost I did not tune away: with log_bin on, as it is by default in 8.4, the load is written to the binary log too, and replicas have to apply it. A 2,000,000 row load makes a binary log event of a size comparable to the file, and a replica that is behind will fall further behind. Watch replication lag during a large load, and if the replica is slow, chunking gives it breathing space between commits.

How to tell it worked

After the swap, verify the three things the script checks: the row count equals the file's record count, SHOW WARNINGS was empty for the load, and a sum of a numeric column agrees with the file. Then confirm the application is using the new table, with a query for a row that only the new data has. If you kept orders_old, compare its count with the new one for an expected difference. And look at replica lag until it is back to normal.

When LOAD DATA does lock something

A LOAD DATA into a table that has a FULLTEXT index, or one with many secondary indexes, spends much of its time maintaining them, and with MyISAM the whole table is locked for the duration, which is the origin of the old advice. If you are on MyISAM, or on a storage engine with table level locks, the swap pattern is not optional. If the load must be into a table with unique keys that the application also writes, row locks on the same keys will make one side wait.

And if the target is a replica set where the primary's binary log is the limit, the better plan may be to load once and clone, or to load each node from the file separately, which depends on your topology. For a single server with an application that reads the table while you load it, loading into a new table and swapping is the approach I would use, and it is the one the measurements support.